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

Paginator broken when using entity inheritance #1361

Closed Devristo closed 3 years ago

Devristo commented 3 years ago

Environment

Sonata packages

show

``` $ composer show --latest 'sonata-project/*' sonata-project/admin-bundle 3.93.0 3.93.0 The missing Symfony Admin Generator sonata-project/block-bundle 4.5.2 4.5.2 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-orm-admin-bundle 3.31.0 3.31.0 Integrate Doctrine ORM into the SonataAdminBundle sonata-project/exporter 2.5.1 2.5.1 Lightweight Exporter library sonata-project/form-extensions 1.9.0 1.9.0 Symfony form extensions sonata-project/twig-extensions 1.5.1 1.5.1 Sonata twig extensions ```

Symfony packages

show

``` $ composer show --latest 'symfony/*' 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/cache v4.4.20 v5.2.4 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.20 v5.2.5 Eases the creation of beautiful and testable command line interfaces symfony/debug v4.4.20 v4.4.20 Provides tools to ease debugging PHP code symfony/dependency-injection v4.4.20 v5.2.5 Allows you to standardize and centralize the way objects are constructed in your application symfony/doctrine-bridge v4.4.20 v5.2.5 Provides integration for Doctrine with various Symfony components symfony/dotenv v4.4.20 v5.2.4 Registers environment variables from a .env file symfony/error-handler v4.4.20 v5.2.4 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.20 v5.2.4 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.20 v5.2.5 Allows to easily create, process and reuse HTML forms symfony/framework-bundle v4.4.20 v5.2.5 Provides a tight integration between Symfony components and the Symfony full-stack framework 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.20 v5.2.5 Provides a structured process for converting a Request into a Response symfony/inflector v4.4.20 v5.2.4 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/mime v4.4.20 v5.2.5 Allows manipulating MIME messages symfony/options-resolver v4.4.20 v5.2.4 Provides an improved replacement for the array_replace PHP function 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/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/routing v4.4.20 v5.2.4 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.20 v5.2.5 Provides a tight integration of the Security component into the Symfony full-stack framework symfony/security-core v4.4.20 v5.2.5 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.20 v5.2.5 Symfony Security Component - HTTP Integration 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.4 v5.2.4 Provides an object-oriented API to strings and deals with bytes, UTF-8 code points and grapheme clusters in a unified way symfony/translation v4.4.20 v5.2.5 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.20 v5.2.5 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.20 v5.2.5 Provides tools to validate values symfony/var-dumper v4.4.20 v5.2.5 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-profiler-bundle v4.4.20 v5.2.4 Provides a development tool that gives detailed information about the execution of any request symfony/yaml v4.4.20 v5.2.5 Loads and dumps YAML files ```

PHP version

$ php -v
PHP 8.0.3 (cli) (built: Mar  6 2021 03:11:06) ( NTS )
Copyright (c) The PHP Group
Zend Engine v4.0.3, Copyright (c) Zend Technologies
    with Zend OPcache v8.0.3, Copyright (c), by Zend Technologies
    with Xdebug v3.0.3, Copyright (c) 2002-2021, by Derick Rethans

Subject

Since v3.31 listing entities which extend another entities is broken. The generated count query uses GROUP BY (since https://github.com/sonata-project/SonataDoctrineORMAdminBundle/pull/1319 ) but the non-aggregated fields are not part of the group by statement.

Reverting to v3.30 solves the issue.

Minimal repository with the bug

https://github.com/Devristo/sonata-admin-entity-inheritance

Steps to reproduce

git clone git@github.com:Devristo/sonata-admin-entity-inheritance.git sonata-admin-entity-inheritance
cd sonata-admin-entity-inheritance
docker-compose up -d
bin/console doctrine:schema:update --force
bin/console doctrine:fixtures:load -n
symfony serve

Then go to: https://127.0.0.1:8000/admin/app/sub/list

Expected results

You will see a listing.

Actual results


An exception has been thrown during the rendering of a template ("An exception occurred while executing 'SELECT b0_.id AS id_0, s1_.other_field AS other_field_1, b0_.discr AS discr_2 FROM sub s1_ INNER JOIN base b0_ ON s1_.id = b0_.id GROUP BY b0_.id ORDER BY b0_.id ASC LIMIT 32':

SQLSTATE[42803]: Grouping error: 7 ERROR: column "s1_.other_field" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT b0_.id AS id_0, s1_.other_field AS other_field_1, b0_...
^").
VincentLanglet commented 3 years ago

@greg0ire Can you help me with this error ?

VincentLanglet commented 3 years ago

@Devristo can you try https://github.com/sonata-project/SonataDoctrineORMAdminBundle/pull/1368 ?

greg0ire commented 3 years ago

@VincentLanglet yes!

See https://stackoverflow.com/a/33830796/353612

TL;DR: if you have an aggregated query, you can't select non-aggregated fields, as highlighted here:

The problem with specifying non-grouped and non-aggregate fields in group by selects is that engine has no way of knowing which record's field it should return in this case. Is it first? Is it last?

    SELECT b0_.id AS id_0, s1_.other_field AS other_field_1, b0_.discr AS discr_2 FROM sub s1_
INNER JOIN base b0_ ON s1_.id = b0_.id
  GROUP BY b0_.id
  ORDER BY b0_.id ASC
     LIMIT 32

Here, we group by b0_.id, but what guarantee do we have that all fields from sub that join with base have the same value for other_field?

Devristo commented 3 years ago

@Devristo can you try #1368 ?

1368 seems to fix my issue. Both in the reproducer as in the project I hit this initially.

Thanks a lot for maintaining these awesome bundles!