search

sonata-project / SonataDoctrineORMAdminBundle

Integrate Doctrine ORM into the SonataAdminBundle
https://docs.sonata-project.org/projects/SonataDoctrineORMAdminBundle
MIT License
445 stars 345 forks source link

Wrong results with `simple` pager after 3.31 #1403

Closed franmomu closed 3 years ago

franmomu commented 3 years ago

Environment

Sonata packages

show

``` $ composer show --latest 'sonata-project/*' sonata-project/admin-bundle 3.96.0 3.96.0 The missing Symfony Admin Generator sonata-project/block-bundle 4.5.3 4.5.3 Symfony SonataBlockBundle sonata-project/cache 2.1.1 2.1.1 Cache library sonata-project/doctrine-extensions 1.12.0 1.12.0 Doctrine2 behavioral extensions sonata-project/doctrine-mongodb-admin-bundle 3.x-dev c18eb59 3.x-dev c18eb59 Symfony Sonata / Integrate Doctrine MongoDB ODM into the SonataAdminBundle sonata-project/doctrine-orm-admin-bundle 3.32.1 3.32.1 Integrate Doctrine ORM into the SonataAdminBundle sonata-project/exporter 2.6.2 2.6.2 Lightweight Exporter library sonata-project/form-extensions 1.9.0 1.9.0 Symfony form extensions sonata-project/translation-bundle 2.x-dev c131469 2.x-dev c131469 SonataTranslationBundle sonata-project/twig-extensions 1.5.1 1.5.1 Sonata twig extensions ```

Symfony packages

show

``` $ composer show --latest 'symfony/*' symfony/amazon-mailer v4.4.21 v5.2.6 Symfony Amazon Mailer Bridge symfony/asset v4.4.20 v5.2.4 Manages URL generation and versioning of web assets such as CSS stylesheets, JavaScript files and image files symfony/browser-kit v4.4.20 v5.2.4 Simulates the behavior of a web browser, allowing you to make requests, click on links and submit forms programmatically symfony/cache v4.4.21 v5.2.6 Provides an extended PSR-6, PSR-16 (and tags) implementation symfony/cache-contracts v2.2.0 v2.2.0 Generic abstractions related to caching symfony/config v4.4.20 v5.2.4 Helps you find, load, combine, autofill and validate configuration values of any kind symfony/console v4.4.21 v5.2.6 Eases the creation of beautiful and testable command line interfaces symfony/css-selector v4.4.20 v5.2.4 Converts CSS selectors to XPath expressions symfony/debug v4.4.20 v4.4.20 Provides tools to ease debugging PHP code symfony/debug-bundle v4.4.20 v5.2.4 Provides a tight integration of the Symfony Debug component into the Symfony full-stack framework symfony/debug-pack v1.0.9 v1.0.9 A debug pack for Symfony projects symfony/dependency-injection v4.4.21 v5.2.6 Allows you to standardize and centralize the way objects are constructed in your application symfony/deprecation-contracts v2.2.0 v2.2.0 A generic function and convention to trigger deprecation notices symfony/doctrine-bridge v4.4.21 v5.2.6 Provides integration for Doctrine with various Symfony components symfony/dom-crawler v4.4.20 v5.2.4 Eases DOM navigation for HTML and XML documents symfony/dotenv v4.4.20 v5.2.4 Registers environment variables from a .env file symfony/error-handler v4.4.21 v5.2.6 Provides tools to manage errors and ease debugging PHP code symfony/event-dispatcher v4.4.20 v5.2.4 Provides tools that allow your application components to communicate with each other by dispatching events and listening to them symfony/event-dispatcher-contracts v1.1.9 v2.2.0 Generic abstractions related to dispatching event symfony/expression-language v4.4.20 v5.2.4 Provides an engine that can compile and evaluate expressions symfony/filesystem v4.4.21 v5.2.6 Provides basic utilities for the filesystem symfony/finder v4.4.20 v5.2.4 Finds files and directories via an intuitive fluent interface symfony/flex v1.12.2 v1.12.2 Composer plugin for Symfony symfony/form v4.4.21 v5.2.6 Allows to easily create, process and reuse HTML forms symfony/framework-bundle v4.4.21 v5.2.6 Provides a tight integration between Symfony components and the Symfony full-stack framework symfony/http-client v4.4.21 v5.2.6 Provides powerful methods to fetch HTTP resources synchronously or asynchronously symfony/http-client-contracts v2.3.1 v2.3.1 Generic abstractions related to HTTP clients symfony/http-foundation v4.4.20 v5.2.4 Defines an object-oriented layer for the HTTP specification symfony/http-kernel v4.4.21 v5.2.6 Provides a structured process for converting a Request into a Response symfony/inflector v4.4.21 v5.2.6 Converts words between their singular and plural forms (English only) symfony/intl v4.4.20 v5.2.4 Provides a PHP replacement layer for the C intl extension that includes additional data from the ICU library symfony/lock v4.4.21 v5.2.6 Creates and manages locks, a mechanism to provide exclusive access to a shared resource symfony/mailer v4.4.21 v5.2.6 Helps sending emails symfony/maker-bundle v1.30.2 v1.30.2 Symfony Maker helps you create empty commands, controllers, form classes, tests and more so you can forget about writing boilerplate code. symfony/messenger v4.4.21 v5.2.5 Helps applications send and receive messages to/from other applications or via message queues symfony/mime v4.4.21 v5.2.6 Allows manipulating MIME messages symfony/monolog-bridge v4.4.21 v5.2.5 Provides integration for Monolog with various Symfony components symfony/monolog-bundle v3.7.0 v3.7.0 Symfony MonologBundle symfony/options-resolver v4.4.20 v5.2.4 Provides an improved replacement for the array_replace PHP function symfony/panther v1.0.1 v1.0.1 A browser testing and web scraping library for PHP and Symfony. symfony/phpunit-bridge v5.2.6 v5.2.6 Provides utilities for PHPUnit, especially user deprecation notices management symfony/polyfill-apcu v1.22.1 v1.22.1 Symfony polyfill backporting apcu_* functions to lower PHP versions symfony/polyfill-intl-grapheme v1.22.1 v1.22.1 Symfony polyfill for intl's grapheme_* functions symfony/polyfill-intl-icu v1.22.1 v1.22.1 Symfony polyfill for intl's ICU-related data and classes symfony/polyfill-intl-idn v1.22.1 v1.22.1 Symfony polyfill for intl's idn_to_ascii and idn_to_utf8 functions symfony/polyfill-intl-normalizer v1.22.1 v1.22.1 Symfony polyfill for intl's Normalizer class and related functions symfony/polyfill-mbstring v1.22.1 v1.22.1 Symfony polyfill for the Mbstring extension symfony/polyfill-php72 v1.22.1 v1.22.1 Symfony polyfill backporting some PHP 7.2+ features to lower PHP versions symfony/polyfill-php73 v1.22.1 v1.22.1 Symfony polyfill backporting some PHP 7.3+ features to lower PHP versions symfony/polyfill-php80 v1.22.1 v1.22.1 Symfony polyfill backporting some PHP 8.0+ features to lower PHP versions symfony/polyfill-uuid v1.22.1 v1.22.1 Symfony polyfill for uuid functions symfony/process v4.4.20 v5.2.4 Executes commands in sub-processes symfony/profiler-pack v1.0.5 v1.0.5 A pack for the Symfony web profiler symfony/property-access v4.4.20 v5.2.4 Provides functions to read and write from/to an object or array using a simple string notation symfony/property-info v4.4.20 v5.2.4 Extracts information about PHP class' properties using metadata of popular sources symfony/proxy-manager-bridge v4.4.20 v5.2.4 Provides integration for ProxyManager with various Symfony components symfony/routing v4.4.20 v5.2.6 Maps an HTTP request to a set of configuration variables symfony/security-acl v3.1.1 v3.1.1 Symfony Security Component - ACL (Access Control List) symfony/security-bundle v4.4.21 v5.2.6 Provides a tight integration of the Security component into the Symfony full-stack framework symfony/security-core v4.4.21 v5.2.6 Symfony Security Component - Core Library symfony/security-csrf v4.4.20 v5.2.4 Symfony Security Component - CSRF Library symfony/security-guard v4.4.20 v5.2.4 Symfony Security Component - Guard symfony/security-http v4.4.21 v5.2.6 Symfony Security Component - HTTP Integration symfony/serializer v4.4.20 v5.2.4 Handles serializing and deserializing data structures, including object graphs, into array structures or other formats like XML and JSON. symfony/service-contracts v2.2.0 v2.2.0 Generic abstractions related to writing services symfony/stopwatch v4.4.20 v5.2.4 Provides a way to profile code symfony/string v5.2.6 v5.2.6 Provides an object-oriented API to strings and deals with bytes, UTF-8 code points and grapheme clusters in a unified way symfony/templating v4.4.20 v5.2.4 Provides all the tools needed to build any kind of template system symfony/test-pack v1.0.7 v1.0.7 A pack for functional and end-to-end testing within a Symfony app symfony/translation v4.4.21 v5.2.6 Provides tools to internationalize your application symfony/translation-contracts v2.3.0 v2.3.0 Generic abstractions related to translation symfony/twig-bridge v4.4.21 v5.2.6 Provides integration for Twig with various Symfony components symfony/twig-bundle v4.4.20 v5.2.4 Provides a tight integration of Twig into the Symfony full-stack framework symfony/validator v4.4.21 v5.2.6 Provides tools to validate values symfony/var-dumper v4.4.21 v5.2.6 Provides mechanisms for walking through any arbitrary PHP variable symfony/var-exporter v4.4.20 v5.2.4 Allows exporting any serializable PHP data structure to plain PHP code symfony/web-link v4.4.21 v5.2.5 Manages links between resources symfony/web-profiler-bundle v4.4.21 v5.2.6 Provides a development tool that gives detailed information about the execution of any request symfony/yaml v4.4.21 v5.2.5 Loads and dumps YAML files ```

PHP version

show

``` $ php -v PHP 7.4.16 (cli) (built: Mar 4 2021 20:52:51) ( NTS ) Copyright (c) The PHP Group Zend Engine v3.4.0, Copyright (c) Zend Technologies with Zend OPcache v7.4.16, Copyright (c), by Zend Technologies with Xdebug v2.9.2, Copyright (c) 2002-2020, by Derick Rethans with blackfire v1.53.0~mac-x64-non_zts74, https://blackfire.io, by Blackfire ```

Subject

Let's say we have a entity A with a one to one to B and B with one to many to C.

After 3.31, using simple pager shows less results (the same repeated but one shown 1) with a DQL in AAdmin like this:

$query
          ->leftJoin($query->getRootAliases()[0] . '.b', 'b')
          ->leftJoin('b.c', 'c')
          ->where('b.id IS NOT NULL AND b.state = :state')
          ->setParameter('state', 'some_state');

Minimal repository with the bug

Steps to reproduce

Having an A admin configured with pager as simple.

services:
    app.admin.a:
        class: AppBundle\Admin\AAdmin
        tags:
            - { name: sonata.admin, manager_type: orm, label: "A", pager_type: "simple" }
        arguments:
            - ~
            - AppBundle\Entity\A
            - ~

And a DQL like:

$query
          ->leftJoin($query->getRootAliases()[0] . '.b', 'b')
          ->leftJoin('b.c', 'c')
          ->where('b.id IS NOT NULL AND b.state = :state')
          ->setParameter('state', 'some_state');

It runs this query:

SELECT a0_.id AS id_0, /*all fields from A*/ 
FROM A a0_ 
LEFT JOIN B a1_ ON a0_.b_id = a1_.id 
LEFT JOIN C a2_ ON a1_.id = a2_.b_id 
WHERE a1_.id IS NOT NULL AND a1_.state = 'some_state' 
LIMIT 33;

Showing the same result 33 times.

Removing the leftJoin with C:

$query
          ->leftJoin($query->getRootAliases()[0] . '.b', 'b')
          ->where('b.id IS NOT NULL AND b.state = :state')
          ->setParameter('state', 'some_state');
SELECT a0_.id AS id_0, /*all fields from A*/  
FROM A a0_ 
LEFT JOIN B a1_ ON a0_.b_id = a1_.id 
WHERE a1_.id IS NOT NULL AND a1_.state = 'some_state' 
LIMIT 33;

shows the proper results, but left join is needed for fetch-joins.

Expected results

Expect showing proper results.

Actual results

Showing the same result repeated.

franmomu commented 3 years ago

Updated, I guess the Simple pager should be also updated according to the last changes of Pager.

franmomu commented 3 years ago

friendly ping @VincentLanglet

VincentLanglet commented 3 years ago

friendly ping @VincentLanglet

Do you know how the simple pager should be updated ?

franmomu commented 3 years ago

friendly ping @VincentLanglet

Do you know how the simple pager should be updated ?

Nope, since the changes introduced in 3.31 (https://github.com/sonata-project/SonataDoctrineORMAdminBundle/pull/1319) broke Pager and SimplePager (when using in combination with fetch-joins) and Pager has been fixed (https://github.com/sonata-project/SonataDoctrineORMAdminBundle/pull/1368), I guess something similar has to be done.

VincentLanglet commented 3 years ago

Nope, since the changes introduced in 3.31 (#1319) broke Pager and SimplePager (when using in combination with fetch-joins) and Pager has been fixed (#1368), I guess something similar has to be done.

Since the simple pager should work with everything, I don't know if there is a way to do some manipulations in the SimplePager the same way. We're just using the doctrine Paginator in this persistence bundle with the getDoctrineQuery method which is in the DoctrineORMAdmin interface.

So the solution would be to change the query... but if the query is

$query
          ->leftJoin($query->getRootAliases()[0] . '.b', 'b')
          ->leftJoin('b.c', 'c')
          ->where('b.id IS NOT NULL AND b.state = :state')
          ->setParameter('state', 'some_state');

I find natural that the execute method runs

SELECT a0_.id AS id_0, /*all fields from A*/ 
FROM A a0_ 
LEFT JOIN B a1_ ON a0_.b_id = a1_.id 
LEFT JOIN C a2_ ON a1_.id = a2_.b_id 
WHERE a1_.id IS NOT NULL AND a1_.state = 'some_state' 
LIMIT 33;

So it would be great to find a way without modifying too much the query.

Maybe adding back the distinct solve the issue ? Can you try this @franmomu ? Or if you have time to write a functional test I'll try to take a look in this week.

franmomu commented 3 years ago

Done in https://github.com/sonata-project/SonataDoctrineORMAdminBundle/pull/1424