[Autocomplete] Autocomplete only with filter_query loads all choices without criteria

[norkunas]

cross posting symfony/symfony#52503 (comment)

Something is not right with this solution. Coming from ux's autocomplete. Currently we are facing a problem that doctrine choice loader does an extra query without any criteria and tries to load 200k rows from our db before the autocomplete queries with criteria in this order:

SELECT ... FROM companies c0_
SELECT COUNT(*) AS dctrn_count FROM (SELECT DISTINCT id_32 FROM (SELECT ... FROM companies c0_ WHERE c0_.supplier = ? ORDER BY c0_.created_at DESC) dctrn_result) dctrn_table
SELECT DISTINCT id_32 FROM (SELECT DISTINCT id_32, created_at_30 FROM (SELECT ... FROM companies c0_ WHERE c0_.supplier = ?) dctrn_result_inner ORDER BY created_at_30 DESC) dctrn_result LIMIT 25	
SELECT ... FROM companies c0_ WHERE c0_.supplier = ? AND c0_.id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ORDER BY c0_.created_at DESC

Backtrace for the first query:

1	Doctrine\DBAL\Connection->executeQuery (line 33)
2	Doctrine\ORM\Query\Exec\SingleSelectExecutor->execute (line 327)
3	Doctrine\ORM\Query->_doExecute (line 1212)
4	Doctrine\ORM\AbstractQuery->executeIgnoreQueryCache (line 1166)
5	Doctrine\ORM\AbstractQuery->execute (line 44)
6	Symfony\Bridge\Doctrine\Form\ChoiceList\ORMQueryBuilderLoader->getEntities (line 56)
7	Symfony\Bridge\Doctrine\Form\ChoiceList\DoctrineChoiceLoader->loadChoices (line 29)
8	Symfony\Component\Form\ChoiceList\Loader\AbstractChoiceLoader->loadChoiceList (line 59)
9	Symfony\Component\Form\ChoiceList\Loader\AbstractChoiceLoader->doLoadChoicesForValues (line 108)
10	Symfony\Bridge\Doctrine\Form\ChoiceList\DoctrineChoiceLoader->doLoadChoicesForValues (line 38)
11	Symfony\Component\Form\ChoiceList\Loader\AbstractChoiceLoader->loadChoicesForValues (line 36)
12	Symfony\Component\Form\ChoiceList\Factory\Cache\ChoiceLoader->loadChoicesForValues (line 37)
13	Symfony\UX\Autocomplete\Form\ChoiceList\Loader\ExtraLazyChoiceLoader->loadChoicesForValues (line 46)
14	Symfony\UX\Autocomplete\Form\ChoiceList\Loader\ExtraLazyChoiceLoader->loadValuesForChoices (line 81)
15	Symfony\Component\Form\ChoiceList\LazyChoiceList->getValuesForChoices (line 34)
16	Symfony\Component\Form\Extension\Core\DataTransformer\ChoiceToValueTransformer->transform (line 992)
17	Symfony\Component\Form\Form->normToView (line 301)
18	Symfony\Component\Form\Form->setData (line 412)
19	Symfony\Component\Form\Form->initialize (line 176)
20	Symfony\Component\Form\FormBuilder->getForm (line 28)
21	Symfony\Component\Form\FormFactory->create (line 142)
22	Symfony\UX\Autocomplete\Form\WrappedEntityTypeAutocompleter->getForm (line 107)
23	Symfony\UX\Autocomplete\Form\WrappedEntityTypeAutocompleter->isGranted (line 49)
24	Symfony\UX\Autocomplete\AutocompleteResultsExecutor->fetchResults (line 44)
25	Symfony\UX\Autocomplete\Controller\EntityAutocompleteController->__invoke (line 181)
26	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 76)
27	Symfony\Component\HttpKernel\HttpKernel->handle (line 197)
28	Symfony\Component\HttpKernel\Kernel->handle (line 35)
29	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 29)

Edit: okay, found the cause - autocomplete allows to configure filter_query which allows to query choices based on the user entered value, but then the query_builder is not configured and DoctrineChoiceLoader loads everything, so fix should be in symfony/ux

[yceruto]

Hi @norkunas, I have moved your comment to where it belongs and where it should be addressed.

[norkunas]

thanks @yceruto

I currently think that there is no way to avoid this unless we could somehow provide query value to the query_builder 😞

[smnandre]

So the main problem is in the UX code, in Doctrine, both ?

If it's in UX could you elaborate a bit on what is not working as expected ?

[norkunas]

I gave already the details. How I supposed to load autocomplete when it tries to load all rows from db? When not using in live component, it renders properly without queries, but querying tries to load everything. And in live component if you dynamically add autocomplete to the form it does the same.

Previous solution worked when Autocomplete was not extending EntityType, so we need to find a solution how to avoid loading everything.