ModelAutocompleteFilter doesn't work correctly with multiple properties and custom createQuery

[tdumalin]

Environment

Sonata packages

$ composer show --latest 'sonata-project/*'
sonata-project/admin-bundle              3.68.0 3.79.0 The missing Symfony Admin Generator
sonata-project/block-bundle              3.19.0 3.21.0 Symfony SonataBlockBundle
sonata-project/cache                     2.0.1  2.0.1  Cache library
sonata-project/core-bundle               3.19.2 3.20.0 Symfony SonataCoreBundle (abandoned)
Package sonata-project/core-bundle is abandoned, you should avoid using it. No replacement was suggested.
sonata-project/doctrine-extensions       1.9.1  1.10.1 Doctrine2 behavioral extensions
sonata-project/doctrine-orm-admin-bundle 3.18.0 3.24.0 Symfony Sonata / Integrate Doctrine ORM into the SonataAdminBundle
sonata-project/exporter                  2.3.0  2.4.1  Lightweight Exporter library
sonata-project/intl-bundle               2.9.0  2.9.0  Symfony SonataIntlBundle

Symfony packages

$ composer show --latest 'symfony/*'
Restricting packages listed in "symfony/symfony" to "4.4.*"
symfony/asset                      v4.4.15 v4.4.16 Symfony Asset Component
symfony/browser-kit                v4.4.15 v4.4.16 Symfony BrowserKit Component
symfony/cache                      v4.4.15 v4.4.16 Symfony Cache component with PSR-6, PSR-16, and tags
symfony/cache-contracts            v2.2.0  v2.2.0  Generic abstractions related to caching
symfony/config                     v4.4.15 v4.4.16 Symfony Config Component
symfony/console                    v4.4.15 v4.4.16 Symfony Console Component
symfony/css-selector               v4.4.15 v4.4.16 Symfony CssSelector Component
symfony/debug                      v4.4.15 v4.4.16 Symfony Debug Component
symfony/debug-bundle               v4.4.15 v4.4.16 Symfony DebugBundle
symfony/debug-pack                 v1.0.8  v1.0.9  A debug pack for Symfony projects
symfony/dependency-injection       v4.4.15 v4.4.16 Symfony DependencyInjection Component
symfony/deprecation-contracts      v2.2.0  v2.2.0  A generic function and convention to trigger deprecation notices
symfony/doctrine-bridge            v4.4.15 v4.4.16 Symfony Doctrine Bridge
symfony/dom-crawler                v4.4.15 v4.4.16 Symfony DomCrawler Component
symfony/dotenv                     v4.4.15 v4.4.16 Registers environment variables from a .env file
symfony/error-handler              v4.4.15 v4.4.16 Symfony ErrorHandler Component
symfony/event-dispatcher           v4.4.15 v4.4.16 Symfony EventDispatcher Component
symfony/event-dispatcher-contracts v1.1.9  v2.2.0  Generic abstractions related to dispatching event
symfony/expression-language        v4.4.15 v4.4.16 Symfony ExpressionLanguage Component
symfony/filesystem                 v4.4.15 v4.4.16 Symfony Filesystem Component
symfony/finder                     v4.4.15 v4.4.16 Symfony Finder Component
symfony/flex                       v1.9.6  v1.10.0 Composer plugin for Symfony
symfony/form                       v4.4.15 v4.4.16 Symfony Form Component
symfony/framework-bundle           v4.4.15 v4.4.16 Symfony FrameworkBundle
symfony/http-client                v4.4.15 v4.4.16 Symfony HttpClient component
symfony/http-client-contracts      v2.2.0  v2.3.1  Generic abstractions related to HTTP clients
symfony/http-foundation            v4.4.15 v4.4.16 Symfony HttpFoundation Component
symfony/http-kernel                v4.4.15 v4.4.16 Symfony HttpKernel Component
symfony/inflector                  v4.4.15 v4.4.16 Symfony Inflector Component
symfony/intl                       v4.4.15 v4.4.16 A PHP replacement layer for the C intl extension that includes additional data from the ICU library.
symfony/maker-bundle               v1.21.1 v1.23.0 Symfony Maker helps you create empty commands, controllers, form classes, tests and more so you can forget about writing boilerp...
symfony/mime                       v4.4.15 v4.4.16 A library to manipulate MIME messages
symfony/monolog-bridge             v4.4.15 v4.4.16 Symfony Monolog Bridge
symfony/monolog-bundle             v3.6.0  v3.6.0  Symfony MonologBundle
symfony/options-resolver           v4.4.15 v4.4.16 Symfony OptionsResolver Component
symfony/orm-pack                   v2.0.0  v2.0.0  A pack for the Doctrine ORM
symfony/phpunit-bridge             v5.1.7  v5.1.8  Symfony PHPUnit Bridge
symfony/polyfill-intl-grapheme     v1.18.1 v1.20.0 Symfony polyfill for intl's grapheme_* functions
symfony/polyfill-intl-icu          v1.18.1 v1.20.0 Symfony polyfill for intl's ICU-related data and classes
symfony/polyfill-intl-idn          v1.18.1 v1.20.0 Symfony polyfill for intl's idn_to_ascii and idn_to_utf8 functions
symfony/polyfill-intl-normalizer   v1.18.1 v1.20.0 Symfony polyfill for intl's Normalizer class and related functions
symfony/polyfill-mbstring          v1.18.1 v1.20.0 Symfony polyfill for the Mbstring extension
symfony/polyfill-php72             v1.18.1 v1.20.0 Symfony polyfill backporting some PHP 7.2+ features to lower PHP versions
symfony/polyfill-php73             v1.18.1 v1.20.0 Symfony polyfill backporting some PHP 7.3+ features to lower PHP versions
symfony/polyfill-php80             v1.18.1 v1.20.0 Symfony polyfill backporting some PHP 8.0+ features to lower PHP versions
symfony/process                    v4.4.15 v4.4.16 Symfony Process Component
symfony/profiler-pack              v1.0.5  v1.0.5  A pack for the Symfony web profiler
symfony/property-access            v4.4.15 v4.4.16 Symfony PropertyAccess Component
symfony/property-info              v4.4.15 v4.4.16 Symfony Property Info Component
symfony/routing                    v4.4.15 v4.4.16 Symfony Routing Component
symfony/security                   v4.4.15 v4.4.16 Symfony Security Component
symfony/security-acl               v3.1.0  v3.1.0  Symfony Security Component - ACL (Access Control List)
symfony/security-bundle            v4.4.15 v4.4.16 Symfony SecurityBundle
symfony/serializer                 v4.4.15 v4.4.16 Symfony Serializer Component
symfony/serializer-pack            v1.0.3  v1.0.4  A pack for the Symfony serializer
symfony/service-contracts          v2.2.0  v2.2.0  Generic abstractions related to writing services
symfony/stopwatch                  v4.4.15 v4.4.16 Symfony Stopwatch Component
symfony/string                     v5.1.7  v5.1.8  Symfony String component
symfony/swiftmailer-bundle         v3.5.1  v3.5.1  Symfony SwiftmailerBundle
symfony/templating                 v4.4.15 v4.4.16 Symfony Templating Component
symfony/test-pack                  v1.0.6  v1.0.7  A pack for functional and end-to-end testing within a Symfony app
symfony/translation                v4.4.15 v4.4.16 Symfony Translation Component
symfony/translation-contracts      v2.3.0  v2.3.0  Generic abstractions related to translation
symfony/twig-bridge                v4.4.15 v4.4.16 Symfony Twig Bridge
symfony/twig-bundle                v4.4.15 v4.4.16 Symfony TwigBundle
symfony/validator                  v4.4.15 v4.4.16 Symfony Validator Component
symfony/var-dumper                 v4.4.15 v4.4.16 Symfony mechanism for exploring and dumping PHP variables
symfony/var-exporter               v4.4.15 v4.4.16 A blend of var_export() + serialize() to turn any serializable data structure to plain PHP code
symfony/web-link                   v4.4.15 v4.4.16 Symfony WebLink Component
symfony/web-profiler-bundle        v4.4.15 v4.4.16 Symfony WebProfilerBundle
symfony/web-server-bundle          v4.4.15 v4.4.16 Symfony WebServerBundle
symfony/webpack-encore-bundle      v1.7.3  v1.8.0  Integration with your Symfony app & Webpack Encore!
symfony/yaml                       v4.4.15 v4.4.16 Symfony Yaml Component

PHP version

$ php -v
PHP 7.2.34-4+ubuntu18.04.1+deb.sury.org+1 (cli) (built: Oct 18 2020 21:19:33) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies
    with Zend OPcache v7.2.34-4+ubuntu18.04.1+deb.sury.org+1, Copyright (c) 1999-2018, by Zend Technologies

Subject

ModelAutocompleteFilter doesn't work correctly with multiple properties and custom createQuery

Steps to reproduce

let's imagine a marketplace with stores who sell products, a product is linked to a store and it can have many variants. When a user log in, we store the id of it's store in session in 'store_id' key. After word he can only see's products and variants from the store setted in session Here's some sample of the entities and related admins

<?php 

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity()
 * @ORM\Table(name="store")
 */
class Store
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;
    //..
}

<?php 

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity()
 * @ORM\Table(name="product")
 */
class Product
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="code", type="string",length=10,unique=true)
     */
    private $code;

    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string",length=255)
     */
    private $name;

    /**
     * @ORM\ManyToOne(targetEntity="Store")
     * @ORM\JoinColumn(name="store_id", referencedColumnName="id")
     */
    private $store;

}

<?php 

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity()
 * @ORM\Table(name="product_variant")
 */
class ProductVariant
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="code", type="string",length=10,unique=true)
     */
    private $code;

    /**
     * @ORM\ManyToOne(targetEntity="Product")
     * @ORM\JoinColumn(name="product_id", referencedColumnName="id")
     */
    private $product;
}

<?php 

namespace App\Admin;

use Sonata\AdminBundle\Admin\AbstractAdmin

class ProductAdmin extends AbstractAdmin
{
    public function createQuery($context = 'list')
    {
        $query = parent::createQuery($context);
        $qb=$query->getQueryBuilder();
        $alias = $qb->getRootAliases()[0];
        $storeId = $this->getRequest()->getSession()->get('store_id');
        $qb->andWhere("$alias.store = :store")
              ->setParameter('store',$storeId);
        return $query;
    }

}

<?php 

namespace App\Admin;

use Sonata\AdminBundle\Admin\AbstractAdmin
use Sonata\DoctrineORMAdminBundle\Filter\ModelAutocompleteFilter;

class ProductVariantAdmin extends AbstractAdmin
{
    public function createQuery($context = 'list')
    {
        $query = parent::createQuery($context);
        $qb=$query->getQueryBuilder();
        $alias = $qb->getRootAliases()[0];
        $storeId = $this->getRequest()->getSession()->get('store_id');
        $qb
              ->leftJoin("$alias.product",'p')
              ->andWhere("p.store = :store")
              ->setParameter('store',$storeId);
        return $query;
    }

    protected function configureDatagridFilters(DatagridMapper $datagridMapper)
    {
        $datagridMapper
            ->add('product',ModelAutocompleteFilter::class,
                array(
                    'show_filter'=>true,
                ),
                null,
                array(
                    'property'=>array('code','name'),
               )
       );

}

Expected results

SELECT count(distinct(p.id)) 
FROM product p
WHERE p.store_id = *session_id*
AND (p.name LIKE '%toto%' OR p.code LIKE '%toto%' )

Actual results

SELECT count(distinct(p.id)) 
FROM product p
WHERE p.store_id = *session_id*
OR p.name LIKE '%toto%' OR p.code LIKE '%toto%' 

[VincentLanglet]

Do you have a minimal repository where I can debug this ? @tdumalin

[tdumalin]

Hi @VincentLanglet ,

unfortunately no, i can manage to create one when i have some free time

[phansys]

@VincentLanglet, I think this issue may be related to #5589.

[VincentLanglet]

Indeed @phansys, Time to try again https://github.com/sonata-project/SonataAdminBundle/pull/5589#issuecomment-504707598

[tdumalin]

@VincentLanglet I guess it means I don't need to create a micro repository ?

[VincentLanglet]

@VincentLanglet I guess it means I don't need to create a micro repository ?

I don't think it will be needed. But I also prefer to warn use, this bug is not easy to fix and exists for a long time...

[tdumalin]

@VincentLanglet I guess it means I don't need to create a micro repository ?

I don't think it will be needed. But I also prefer to warn use, this bug is not easy to fix and exists for a long time...

Ok no problem, for now i use the callback feature when i need to filter on multiple fields, thanks for the help

[VincentLanglet]

Changing the Datagrid::applyFilters method could be a solution

$originalWhereClause = $this->query->getWhereClause();
$this->query->resetWhereClause();

foreach ($this->getFilters() as $name => $filter) {
    $this->values[$name] = isset($this->values[$name]) ? $this->values[$name] : null;
    $filterFormName = $filter->getFormName();
    $value = $this->values[$filterFormName]['value'] ?? '';
    $type = $this->values[$filterFormName]['type'] ?? '';

     if ('' !== $value || '' !== $type) {
         $filter->apply($this->query, $data[$filterFormName]);
     }
}

$filterWhereClause = $this->query->getWhereClause();
$this->query->resetWhereClause();
$this->query->applyWhereClause($originalWhereClause);
$this->query->applyWhereClause($filterWhereClause);

Could be a solution. @franmomu does a getWhereClause, resetWhereClause and applyWhereClause can make sens for MongoDB ?

[franmomu]

🤔 mmm I think there is no API in doctrine/mongodb-odm to perform those operations and there is no concept of where.

[VincentLanglet]

🤔 mmm I think there is no API in doctrine/mongodb-odm to perform those operations and there is no concept of where.

In ORM, the issue is that if you use

->andWhere("$alias.lastname = Foo")

In the create query and you have two filters

• Firstname = Bar
• birthdate = 21/01/1991

If the filter are used with the AND condition, there is no issue, the request will be

->andWhere("$alias.lastname = Foo")
->andWhere("$alias.firstname = Bar")
->andWhere("$alias.birthdate = 21/01/1991")

Lastname = Foo AND Firstname = Bar AND Birthdate = 21/01/1991

But with the OR condition, it's

->andWhere("$alias.lastname = Foo")
->orWhere("$alias.firstname = Bar")
->orWhere("$alias.birthdate = 21/01/1991")

Lastname = Foo OR Firstname = Bar OR Birthdate = 21/01/1991

Instead of

Lastname = Foo AND [ Firstname = Bar OR Birthdate = 21/01/1991 ]

If there is no concept of where, how would you solve this in MondoDB persistence bundle ?

[VincentLanglet]

@tdumalin Do you still have the issue with the latest version ?

@phansys made some improvement with the OR filter