Wrong search results when using custom query

[guillaumesmo]

Hi,

I have overloaded the createQuery in my Admin class, but the search results include wrong items because the resulting query is invalid :

public function createQuery($context = 'list')
{
    $query = parent::createQuery($context);
    $query->andWhere($query->getRootAlias() . '.deletedAt IS NOT NULL');
    return $query;
}

SELECT DISTINCT d0_.id AS id0 FROM data d0_ WHERE d0_.deletedAt IS NULL OR d0_.firstName LIKE '%search%' OR d0_.lastName LIKE '%search%' LIMIT 25 OFFSET 0

The search criteria should be wrapped in a AND (...), right now they are just appended as OR

Tested with sonata-project/admin-bundle 2.3.2

[soerenbernstein]

+1
Have the same exact problem.
Any work arounds for now?

[rande]

By default the search does a OR on text field. It is not a bug, it is correct behavior to look over all fields.

[guillaumesmo]

I agree that the different criteria must be searched on with an OR, but the custom query should be respected in the first place, so the final query should look like this :

SELECT DISTINCT d0_.id AS id0 FROM data d0_ WHERE d0_.deletedAt IS NULL AND (d0_.firstName LIKE '%search%' OR d0_.lastName LIKE '%search%') LIMIT 25 OFFSET 0

[rande]

There is nothing in the Admin Bundle which set this criteria d0_.deletedAt IS NULL in the query.

[soerenbernstein]

The problem is, that the OR filter part should be in parantheses so an earlier added AND where clause is not destroyed. I'm not sure if this is a error in the Doctrine\ORM\QueryBuilder class, but adding the and part first will not result in the intended where clause. Adding it after appliing all the OR filters will result in an where clause where all the OR filter are in parentheses.

I've fiddled around in the Datagrid class and found a rather crud solution. It's probably not the best, but seems to solve the problem with a minimum amount of changes.

[soerenbernstein]

@rande So, I updated mix fix according to your notes and created a new PR. Is there any chance this will be included?.

[brossdu]

Any news concerning this issue ?