4.0.0-alpha-2 list count query executed twice

[mssimi]

Environment

Sonata packages

show

``` $ composer show --latest 'sonata-project/*' sonata-project/admin-bundle 4.0.0-alpha-2 = 4.0.0-alpha-2 The missing Symfony Admin Generator sonata-project/block-bundle 4.6.0 = 4.6.0 Symfony SonataBlockBundle sonata-project/cache 2.1.1 = 2.1.1 Cache library sonata-project/doctrine-extensions 1.13.0 = 1.13.0 Doctrine2 behavioral extensions sonata-project/doctrine-orm-admin-bundle 4.0.0-alpha-2 = 4.0.0-alpha-2 Integrate Doctrine ORM into the SonataAdminBundle sonata-project/exporter 2.7.0 = 2.7.0 Lightweight Exporter library sonata-project/form-extensions 1.9.0 = 1.9.0 Symfony form extensions sonata-project/twig-extensions 1.6.0 = 1.6.0 Sonata twig extensions ```

Symfony packages

show

``` $ composer show --latest 'symfony/*' symfony/acl-bundle v2.1.0 = v2.1.0 Symfony AclBundle symfony/amqp-messenger v5.3.2 = v5.3.2 Symfony AMQP extension Messenger Bridge symfony/asset v5.3.2 = v5.3.2 Manages URL generation and versioning of web assets such as CSS stylesheets, JavaScript files and image files symfony/browser-kit v5.3.0 = v5.3.0 Simulates the behavior of a web browser, allowing you to make requests, click on links and submit forms programmatically symfony/cache v5.3.3 = v5.3.3 Provides an extended PSR-6, PSR-16 (and tags) implementation symfony/cache-contracts v2.4.0 = v2.4.0 Generic abstractions related to caching symfony/config v5.3.3 = v5.3.3 Helps you find, load, combine, autofill and validate configuration values of any kind symfony/console v5.3.2 = v5.3.2 Eases the creation of beautiful and testable command line interfaces symfony/css-selector v5.3.0 = v5.3.0 Converts CSS selectors to XPath expressions symfony/debug-bundle v5.3.0 = v5.3.0 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 v5.3.3 = v5.3.3 Allows you to standardize and centralize the way objects are constructed in your application symfony/deprecation-contracts v2.4.0 = v2.4.0 A generic function and convention to trigger deprecation notices symfony/doctrine-bridge v5.3.3 = v5.3.3 Provides integration for Doctrine with various Symfony components symfony/doctrine-messenger v5.3.2 = v5.3.2 Symfony Doctrine Messenger Bridge symfony/dom-crawler v5.3.0 = v5.3.0 Eases DOM navigation for HTML and XML documents symfony/dotenv v5.3.0 = v5.3.0 Registers environment variables from a .env file symfony/error-handler v5.3.3 = v5.3.3 Provides tools to manage errors and ease debugging PHP code symfony/event-dispatcher v5.3.0 = v5.3.0 Provides tools that allow your application components to communicate with each other by dispatching events and listening to them symfony/event-dispatcher-contracts v2.4.0 = v2.4.0 Generic abstractions related to dispatching event symfony/expression-language v5.3.0 = v5.3.0 Provides an engine that can compile and evaluate expressions symfony/filesystem v5.3.3 = v5.3.3 Provides basic utilities for the filesystem symfony/finder v5.3.0 = v5.3.0 Finds files and directories via an intuitive fluent interface symfony/flex v1.13.3 = v1.13.3 Composer plugin for Symfony symfony/form v5.3.3 = v5.3.3 Allows to easily create, process and reuse HTML forms symfony/framework-bundle v5.3.3 = v5.3.3 Provides a tight integration between Symfony components and the Symfony full-stack framework symfony/http-client v5.3.3 = v5.3.3 Provides powerful methods to fetch HTTP resources synchronously or asynchronously symfony/http-client-contracts v2.4.0 = v2.4.0 Generic abstractions related to HTTP clients symfony/http-foundation v5.3.3 = v5.3.3 Defines an object-oriented layer for the HTTP specification symfony/http-kernel v5.3.3 = v5.3.3 Provides a structured process for converting a Request into a Response symfony/lock v5.3.2 = v5.3.2 Creates and manages locks, a mechanism to provide exclusive access to a shared resource symfony/mailer v5.3.3 = v5.3.3 Helps sending emails symfony/messenger v5.3.3 = v5.3.3 Helps applications send and receive messages to/from other applications or via message queues symfony/mime v5.3.2 = v5.3.2 Allows manipulating MIME messages symfony/monolog-bridge v5.3.3 = v5.3.3 Provides integration for Monolog with various Symfony components symfony/monolog-bundle v3.7.0 = v3.7.0 Symfony MonologBundle symfony/options-resolver v5.3.0 = v5.3.0 Provides an improved replacement for the array_replace PHP function symfony/orm-pack v2.1.0 = v2.1.0 A pack for the Doctrine ORM symfony/password-hasher v5.3.3 = v5.3.3 Provides password hashing utilities symfony/polyfill-intl-grapheme v1.23.0 = v1.23.0 Symfony polyfill for intl's grapheme_* functions symfony/polyfill-intl-icu v1.23.0 = v1.23.0 Symfony polyfill for intl's ICU-related data and classes symfony/polyfill-intl-idn v1.23.0 = v1.23.0 Symfony polyfill for intl's idn_to_ascii and idn_to_utf8 functions symfony/polyfill-intl-normalizer v1.23.0 = v1.23.0 Symfony polyfill for intl's Normalizer class and related functions symfony/polyfill-mbstring v1.23.0 = v1.23.0 Symfony polyfill for the Mbstring extension symfony/polyfill-php72 v1.23.0 = v1.23.0 Symfony polyfill backporting some PHP 7.2+ features to lower PHP versions symfony/polyfill-php73 v1.23.0 = v1.23.0 Symfony polyfill backporting some PHP 7.3+ features to lower PHP versions symfony/polyfill-php80 v1.23.0 = v1.23.0 Symfony polyfill backporting some PHP 8.0+ features to lower PHP versions symfony/polyfill-php81 v1.23.0 = v1.23.0 Symfony polyfill backporting some PHP 8.1+ features to lower PHP versions symfony/polyfill-uuid v1.23.0 = v1.23.0 Symfony polyfill for uuid functions symfony/profiler-pack v1.0.5 = v1.0.5 A pack for the Symfony web profiler symfony/property-access v5.3.0 = v5.3.0 Provides functions to read and write from/to an object or array using a simple string notation symfony/property-info v5.3.1 = v5.3.1 Extracts information about PHP class' properties using metadata of popular sources symfony/proxy-manager-bridge v5.3.0 = v5.3.0 Provides integration for ProxyManager with various Symfony components symfony/psr-http-message-bridge v2.1.0 = v2.1.0 PSR HTTP message bridge symfony/redis-messenger v5.3.2 = v5.3.2 Symfony Redis extension Messenger Bridge symfony/routing v5.3.0 = v5.3.0 Maps an HTTP request to a set of configuration variables symfony/security-acl v3.1.2 = v3.1.2 Symfony Security Component - ACL (Access Control List) symfony/security-bundle v5.3.3 = v5.3.3 Provides a tight integration of the Security component into the Symfony full-stack framework symfony/security-core v5.3.3 = v5.3.3 Symfony Security Component - Core Library symfony/security-csrf v5.3.3 = v5.3.3 Symfony Security Component - CSRF Library symfony/security-guard v5.3.0 = v5.3.0 Symfony Security Component - Guard symfony/security-http v5.3.3 = v5.3.3 Symfony Security Component - HTTP Integration symfony/sendgrid-mailer v5.3.0 = v5.3.0 Symfony Sendgrid Mailer Bridge symfony/serializer v5.3.2 = v5.3.2 Handles serializing and deserializing data structures, including object graphs, into array structures or other formats like XML and JSON. symfony/service-contracts v2.4.0 = v2.4.0 Generic abstractions related to writing services symfony/stopwatch v5.3.0 = v5.3.0 Provides a way to profile code symfony/string v5.3.3 = v5.3.3 Provides an object-oriented API to strings and deals with bytes, UTF-8 code points and grapheme clusters in a unified way symfony/templating v5.3.0 = v5.3.0 Provides all the tools needed to build any kind of template system symfony/translation v5.3.3 = v5.3.3 Provides tools to internationalize your application symfony/translation-contracts v2.4.0 = v2.4.0 Generic abstractions related to translation symfony/twig-bridge v5.3.3 = v5.3.3 Provides integration for Twig with various Symfony components symfony/twig-bundle v5.3.3 = v5.3.3 Provides a tight integration of Twig into the Symfony full-stack framework symfony/validator v5.3.3 = v5.3.3 Provides tools to validate values symfony/var-dumper v5.3.3 = v5.3.3 Provides mechanisms for walking through any arbitrary PHP variable symfony/var-exporter v5.3.3 = v5.3.3 Allows exporting any serializable PHP data structure to plain PHP code symfony/web-profiler-bundle v5.3.2 = v5.3.2 Provides a development tool that gives detailed information about the execution of any request symfony/workflow v5.3.0 = v5.3.0 Provides tools for managing a workflow or finite state machine symfony/yaml v5.3.3 = v5.3.3 Loads and dumps YAML files ```

PHP version

$ php -v
PHP 8.0.5 (cli) (built: May  3 2021 11:58:58) ( NTS )
Copyright (c) The PHP Group
Zend Engine v4.0.5, Copyright (c) Zend Technologies
    with Zend OPcache v8.0.5, Copyright (c), by Zend Technologies
    with ddtrace v0.55.0, Copyright Datadog, by Datadog

Subject

After updating from 4.0.0-alpha-1 to 4.0.0-alpha-2, count query in every admin list is executed twice, this does not prevent list from working, but since for us count query is very slow and now is executed twice, lists are now almost twice as slow.

Below example query that I talk about, truncated, so I don't expose sensitive data.

SELECT COUNT(*) AS dctrn_count FROM (SELECT w0_.id AS id_0, w0_.configuration_type AS configuration_type_1, ...) dctrn_table

Expected results

That query should run only once per list, like it did in 4.0.0-alpha-1

[VincentLanglet]

Any idea where are the two executions of the count query @mssimi ?

[mssimi]

Not much, even this simple Admin class doubles count query.

final class PickListAdmin extends AbstractAdmin
{
    /** @var string */
    protected $classnameLabel = 'PickList';
    /** @var string */
    protected $baseRoutePattern = 'picklist';
    /** @var string */
    protected $baseRouteName = 'admin_picklist';

    protected function configureListFields(ListMapper $list): void
    {
        $list
            ->addIdentifier('id', null, [
                'label' => 'Pick List',
            ]);
    }
}

[VincentLanglet]

Do you use the classic Pager of the SimplePager ?

[mssimi]

I have only one list with simple pager, other use default one and I can confirm, issue is not on list with simple pager.

[VincentLanglet]

@mssimi There is three occurence of admin.datagrid.results|length > 0 in the base_list.html.twig template. Is it better if you create a variable

{% set datagrid_has_results = admin.datagrid.results|length > 0 %}

?

[VincentLanglet]

I would say the issue we have is that

In the list action, there is the following code

$formView = $datagrid->getForm()->createView();

and getForm is building the Pager.

public function getForm(): FormInterface
    {
        $this->buildPager();
        \assert(null !== $this->form);

        return $this->form;
    }

Then in the view, getResults is building the pager again.

public function getResults(): iterable
    {
        $this->buildPager();

        if (null === $this->results) {
            $this->results = $this->pager->getCurrentPageResults();
        }

        return $this->results;
    }

Could you try to split the buildPager method to something like

public function buildPager(): void
    {
        if ($this->bound) {
            return;
        }

         $this->buildForm();

        $this->applyFilters($this->form->getData() ?? []);
        $this->applySorting();

        $this->pager->setMaxPerPage($this->getMaxPerPage(25));
        $this->pager->setPage($this->getPage(1));
        $this->pager->setQuery($this->query);
        $this->pager->init();

        $this->bound = true;
    }

and changing getForm to

    public function getForm(): FormInterface
    {
        if (null === $this->form) {
            $this->buildForm();
        }
        \assert(null !== $this->form);

        return $this->form;
    }

This way we'll stop to build the Pager in the controller.

Can u tell me if it fix the issue ?

[VincentLanglet]

@mssimi This is what I have in mind: https://github.com/sonata-project/SonataAdminBundle/pull/7329 Can you try it ?

[mssimi]

I will try it.

[mssimi]

@VincentLanglet It is still executed twice.

[VincentLanglet]

Do you have a public repository to debug this ?

[mssimi]

Cannot provide mine, we could maybe try sonata demo?

[mssimi]

Quite busy now, I will try to investigate it later.

[VincentLanglet]

I tried to reproduce the issue on a symfony demo project.

• I do have two query
• The PR I created reduce the number of call to the count method.

I'm not sure it's an easy to have two count queries. For instance, we might count the total number of rows and the number of rows on the page.

Also, I checked on my Sonata 3 project, and I have two count queries So this is not a Sonata-4 regression. (And the second query cost almost nothing)

[VincentLanglet]

I see what's happening between Sonata 4.0.0-alpha-1 and 4.0.0-alpha-2 (or dev-master).

In alpha 1, the Pager::getCurrentPageResults of DoctrineORMAdminBundle is returning

$paginator = new Paginator($query->getDoctrineQuery(), 1 === \count($identifierFieldNames));

return $paginator->getIterator();

and so when writing admin.datagrid.results|length the whole select is executed, and then a count is made on this result. So we were loosing the benefit of the improved count query of the Paginator. But if somewhere else we're iterating on the results, since the select is already executed, there is no extra queries.

In alpha 2, the Pager::getCurrentPageResults is returning

return $query->execute();

which is a Paginator instead so the Datagrid::getResult() method also return a Paginator. So when writing admin.datagrid.results|length the count method of the Paginator is called. Then if somewhere else, we're iterating on the results, getIterator is called so another query is made.

Changing

public function getCurrentPageResults(): iterable
    {
        $query = $this->getQuery();
        if (!$query instanceof ProxyQueryInterface) {
            throw new \TypeError(sprintf('The pager query MUST implement %s.', ProxyQueryInterface::class));
        }

        return $query->execute();
    }

to

public function getCurrentPageResults(): iterable
    {
        $query = $this->getQuery();
        if (!$query instanceof ProxyQueryInterface) {
            throw new \TypeError(sprintf('The pager query MUST implement %s.', ProxyQueryInterface::class));
        }

        return $query->execute()->getIterator();
    }

seems to solve the issue.

@mssimi Can you confirm ?

[VincentLanglet]

See https://github.com/sonata-project/SonataDoctrineORMAdminBundle/pull/1480

[mssimi]

@VincentLanglet I can confirm that changing public function getCurrentPageResults(): iterable solves that :)

So this is not a Sonata-4 regression. (And the second query cost almost nothing)

For me both queries cost 2-4s, I have over million rows in DB.

[VincentLanglet]

Solve on SonataDoctrineORMAdmin@dev-master