Sonataadminbundle: ProxyQuery with complex Order / Where Query
What I want to do
Add a special order to a list (a bit more complex since I cannot use functions / parameters in the order of doctrine2)
how would it look like
createQuery() {
return parent::createQuery($context)
->select('c, FIELD(c.status, :statusList) as HIDDEN statusList')
->from($this->getClass(), 'c')
->orderBy('statusList')
->setParameter('statusList', ["ordered", "shipped"])
;
}
why is it not working
Sonata\DoctrineORMAdminBundle\Datagrid\ProxyQuery::getFixedQueryBuilder resets the DQLPart "select"
what would be great
some way to extend the select, perhaps a callback for the query builder that can be passed to the ProxyQuery
workaround
I basically copied the whole function and added the callback - this way I can use it from my admin:
<?php
namespace AdminBundle\Datagrid;
use Doctrine\DBAL\Types\Type;
use Doctrine\ORM\Query;
use Doctrine\ORM\QueryBuilder;
use Sonata\DoctrineORMAdminBundle\Datagrid\ProxyQuery as AbstractProxyQuery;
class ProxyQuery extends AbstractProxyQuery
{
private $callback = [];
public function addCallback($callback)
{
$this->callback[] = $callback;
}
/**
* @param QueryBuilder $queryBuilder
*
* @return QueryBuilder
*/
protected function getFixedQueryBuilder(QueryBuilder $queryBuilder)
{
$queryBuilderId = clone $queryBuilder;
$rootAlias = current($queryBuilderId->getRootAliases());
// step 1 : retrieve the targeted class
$from = $queryBuilderId->getDQLPart('from');
$class = $from[0]->getFrom();
$metadata = $queryBuilderId->getEntityManager()->getMetadataFactory()->getMetadataFor($class);
// step 2 : retrieve identifier columns
$idNames = $metadata->getIdentifierFieldNames();
// step 3 : retrieve the different subjects ids
$selects = array();
$idxSelect = '';
foreach ($idNames as $idName) {
$select = sprintf('%s.%s', $rootAlias, $idName);
// Put the ID select on this array to use it on results QB
$selects[$idName] = $select;
// Use IDENTITY if id is a relation too. See: http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html
// Should work only with doctrine/orm: ~2.2
$idSelect = $select;
if ($metadata->hasAssociation($idName)) {
$idSelect = sprintf('IDENTITY(%s) as %s', $idSelect, $idName);
}
$idxSelect .= ($idxSelect !== '' ? ', ' : '').$idSelect;
}
$queryBuilderId->resetDQLPart('select');
$queryBuilderId->add('select', 'DISTINCT '.$idxSelect);
// for SELECT DISTINCT, ORDER BY expressions must appear in idxSelect list
/* Consider
SELECT DISTINCT x FROM tab ORDER BY y;
For any particular x-value in the table there might be many different y
values. Which one will you use to sort that x-value in the output?
*/
// todo : check how doctrine behave, potential SQL injection here ...
if ($this->getSortBy()) {
$sortBy = $this->getSortBy();
if (strpos($sortBy, '.') === false) { // add the current alias
$sortBy = $rootAlias.'.'.$sortBy;
}
$sortBy .= ' AS __order_by';
$queryBuilderId->addSelect($sortBy);
}
// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! CHANGED START
foreach ($this->callback as $callback) {
$callback($queryBuilder);
}
// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! CHANGED END
$results = $queryBuilderId->getQuery()->execute(array(), Query::HYDRATE_ARRAY);
$platform = $queryBuilderId->getEntityManager()->getConnection()->getDatabasePlatform();
$idxMatrix = array();
foreach ($results as $id) {
foreach ($idNames as $idName) {
// Convert ids to database value in case of custom type, if provided.
$fieldType = $metadata->getTypeOfField($idName);
$idxMatrix[$idName][] = $fieldType && Type::hasType($fieldType)
? Type::getType($fieldType)->convertToDatabaseValue($id[$idName], $platform)
: $id[$idName];
}
}
// step 4 : alter the query to match the targeted ids
foreach ($idxMatrix as $idName => $idx) {
if (count($idx) > 0) {
$idxParamName = sprintf('%s_idx', $idName);
$idxParamName = preg_replace('/[^\w]+/', '_', $idxParamName);
$queryBuilder->andWhere(sprintf('%s IN (:%s)', $selects[$idName], $idxParamName));
$queryBuilder->setParameter($idxParamName, $idx);
$queryBuilder->setMaxResults(null);
$queryBuilder->setFirstResult(null);
}
}
return $queryBuilder;
}
}
and from my admin:
class CartAdmin extends AbstractAdmin{
/**
* @param string $context
*
* @return \Sonata\AdminBundle\Datagrid\ProxyQueryInterface
*/
public function createQuery($context = 'list')
{
/** @var ModelManager $mm */
$mm = $this->getModelManager();
$queryBuilder = $mm->getEntityManager($this->getClass())->createQueryBuilder();
$queryBuilder->select('c')
->from($this->getClass(), 'c')
;
$proxyQuery = new \AdminBundle\Datagrid\ProxyQuery($queryBuilder);
$proxyQuery->addCallback(function(QueryBuilder $queryBuilder) {
$queryBuilder
->addSelect('FIELD(c.status, :statusList) as HIDDEN statusList')
->setParameter('statusList', ["ordered", "shipped"])
->orderBy('statusList')
->addOrderBy('c.createdAt', 'ASC')
;
});
return $proxyQuery;
}
}
wodka
All 22 comments
Adding an extension point, ok, but adding it to the admin class… not ok. The admin class is already very big, and we're trying to split it into components. To do this properly, there should be a new component dedicated to the listing IMO. cc @core23 @fbourigault
greg0ire
on 17 Feb 2017
The important part would be the ProxyQuery.
The createQuery method of the Admin exists only to extend the list query as far as I understand.
wodka
on 17 Feb 2017
IMHO, you didn't understand the ProxyQuery::getFixedQueryBuilder code. The part before the change you suggested clone the current query builder and is used to only get the objects ids. Then those ids are injected in the queryBuilder we received as an argument using a andWhere and adding an offset and a limit.
So you could write a AdminExtension that do what you want in the configureQuery.
fbourigault
on 17 Feb 2017
@fbourigault : the sort order matters if you want to get the right ids.
greg0ire
on 17 Feb 2017
So the solution is to find a less destructive alternative to $queryBuilderId->resetDQLPart('select');
fbourigault
on 17 Feb 2017
Perhaps reset everything that is not "HIDDEN"?
i. E. collect all dqlparts for select that contain HIDDEN and restore those after the reset
wodka
on 17 Feb 2017
If they really are separate parts , that could be a good solution indeed. Can you try dumping the select part, to see what it looks like?
greg0ire
on 17 Feb 2017
just tried the following:
// Sonata\DoctrineORMAdminBundle\Datagrid\ProxyQuery::getFixedQueryBuilder
$keep = ['DISTINCT '.$idxSelect];
/** @var Query\Expr\Select $select */
foreach ($queryBuilderId->getDQLPart('select') as $select) {
foreach ($select->getParts() as $part) {
if (false !== strpos($part, 'HIDDEN')) {
$keep[] = $part;
}
}
}
$queryBuilderId->resetDQLPart('select');
$queryBuilderId->select($keep);
there is one requirement for this to work properly:
the select must be in a separate addSelect for the query builder (the $part will be exactly the given string)
- I have not tested this with expressions!
And there is a problem if parameters are passed to the query inside the pager (computeNbResult) because it also removes the select but keeps the parameters from the original query.
wodka
on 17 Feb 2017
You mean parameters can be used in the SELECT clause?
greg0ire
on 17 Feb 2017
Yes:
$queryBuilder
->addSelect('FIELD(c.status, :statusList) as HIDDEN statusList')
->setParameter('statusList', ["ordered", "shipped"])
->orderBy('statusList')
Oh yeah I see
greg0ire
on 17 Feb 2017
I'd throw a NotImplementedYet exception when detecting a parameter (unless you want to implement it)
greg0ire
on 17 Feb 2017
... I cannot even find a single occurrence of setParameter for a Pager. Somehow I would have to reset all parameters for the pager that would have been used in the select. :/
wodka
on 17 Feb 2017
Maybe it would be a proper solution to add a simple interface for setting multiple sort conditions to the ProxyQuery?
coyl
on 12 Jun 2017
partially - problem is that the sort condition might be dependent on the select itself, if the select is cleared the error is raised
wodka
on 12 Jun 2017
I stumbled over a related issue and post my solution just for information.
I've got a many-to-many relation of A::$b and B::$a. When listing Entities of A, I want to be able to sort by properties of class B. Setting the FieldDescriptionOptions sortable to _true_ does not worked. So I tried to add the ORDER BY clause by using a HIDDEN select field but coming to the same issue as shown above.
After a some hour of investigation I found that one can use a property path on sortable. So my solution to my problem is:
protected function configureListFields(ListMapper $list)
{
$list
->add('b', null, [
'sortable' => 'b.foo'
])
;
}
[Source: [https://stackoverflow.com/a/21145167](https://stackoverflow.com/a/21145167)]
This is an undocumented feature! So we (including me) should extend it.
FireLizard
on 10 Nov 2017
Can you document it please?
greg0ire
on 10 Nov 2017
Hi @greg0ire ,
I cannot update the documentation because I don't know much about the sortable-feature.
Now when using the sortable option on ManyToOne association it does not work:
Code
protected function configureListFields(ListMapper $list)
{
$list->add('object', null, [
'sortable' => 'property',
]);
}
Stack Trace
[1] Symfony\Component\Debug\Exception\FatalThrowableError: Type error: Argument 1 passed to Sonata\DoctrineORMAdminBundle\Datagrid\ProxyQuery::entityJoin() must be of the type array, null given, called in /usr/local/apache2/htdocs/vendor/sonata-project/doctrine-orm-admin-bundle/src/Datagrid/ProxyQuery.php on line 140
at n/a
in /usr/local/apache2/htdocs/vendor/sonata-project/doctrine-orm-admin-bundle/src/Datagrid/ProxyQuery.php line 214
at Sonata\DoctrineORMAdminBundle\Datagrid\ProxyQuery->entityJoin(null)
in /usr/local/apache2/htdocs/vendor/sonata-project/doctrine-orm-admin-bundle/src/Datagrid/ProxyQuery.php line 140
at Sonata\DoctrineORMAdminBundle\Datagrid\ProxyQuery->setSortBy(null, null)
in /usr/local/apache2/htdocs/vendor/sonata-project/admin-bundle/src/Datagrid/Datagrid.php line 152
at Sonata\AdminBundle\Datagrid\Datagrid->buildPager()
in /usr/local/apache2/htdocs/vendor/sonata-project/admin-bundle/src/Datagrid/Datagrid.php line 262
at Sonata\AdminBundle\Datagrid\Datagrid->getForm()
in /usr/local/apache2/htdocs/vendor/sonata-project/admin-bundle/src/Controller/CRUDController.php line 157
at Sonata\AdminBundle\Controller\CRUDController->listAction()
in /usr/local/apache2/htdocs/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php line 135
at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), '1')
in /usr/local/apache2/htdocs/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php line 57
at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), '1', true)
in /usr/local/apache2/htdocs/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/DependencyInjection/ContainerAwareHttpKernel.php line 67
at Symfony\Component\HttpKernel\DependencyInjection\ContainerAwareHttpKernel->handle(object(Request), '1', true)
in /usr/local/apache2/htdocs/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/Kernel.php line 183
at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
in /usr/local/apache2/htdocs/web/app_dev.php line 28
https://github.com/sonata-project/SonataDoctrineORMAdminBundle/pull/728
This PR fixes the issue here?
jordisala1991
on 23 Apr 2018
Closing for now, if the issue is not solved after the PR I mention, please ping me to reopen.
If you have additional problems, please create a new issue @FireLizard
jordisala1991
on 24 Apr 2018
not really for me - my issue was with an additional select query.
wodka
on 24 Apr 2018
Is this still relevant? If so, what is blocking it? Is there anything you can do to help move it forward?
This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
![stale[bot] picture](https://avatars3.githubusercontent.com/in/1724?v=4&s=40)
stale[bot]
on 30 Jan 2020
Related issues
ajeetvarma
·
3Comments
vazgen
·
4Comments
aglemakinafr
·
4Comments
parijathakumar
·
3Comments
OskarStark
·
4Comments
Most helpful comment
So the solution is to find a less destructive alternative to
$queryBuilderId->resetDQLPart('select');