Modify DataGrid Query

Hi,

1) what I want is to modify datagrid query to apply filter’s value on join condition instead of where clause. Because I have left join in my query and applying filters on where clause converts my left join to inner join. I can add join condition with offsetSetByPath() method of DataGridConfig in onPreBuild listener. But can not prevent to apply filters on where clause.

2) Another thing I want is to add orWhere() clause in datagrid query. I use addOrWhere() of getOrmQuery() of DataGridConfig in onPreBuild listener followed by this tutorial After that I get the SQL in onResultBefore listener and I see my condition apply with ‘AND’ instead of ‘OR’.

My oro version is 1.8

Topic

[Ilya AntypenkoParticipant]

Hi Nava

1) You can use any methods from ConfigObject class.
For example offsetUnsetByPath.

Also you can work with this object as with array.

2) Could you please check and share DQL before getting results?
You need to call $queryBuilder->getQuery()->getSQL()

> My oro version is 1.8

This version of CRM is no longer supported.
Please consider upgrading to the latest version
https://oroinc.com/orocrm/doc/current/community/release-process

[NavaParticipant]

Ilya Antypenko,

Thanks for your consideration.

1) I think ‘where’ part in this path ‘[source][query][where]’ is all dql where parts in .yml files. But this where that I want to prevent to apply is filter’s value that user chooses. So offSetUnsetByPath() method of configObject doesnt help me. Do you have any idea?

2) sure.
This is my where in onPreBuild listener:

PHP

$event->getConfig()->getOrmQuery()->addOrWhere('v.createdAt is null');

1	$event->getConfig()->getOrmQuery()->addOrWhere('v.createdAt is null');

‘or where’ part is added successfully, because my condition is added in this index:

PHP

$event->getConfig()->getOrmQuery()->getWhere()['or'][0]

1	$event->getConfig()->getOrmQuery()->getWhere()['or'][0]

I get dql in onResultBefore:

PHP

$dql = $event->getDatagrid()->getDatasource()->getQueryBuilder()->getQuery()->getDQL();

1	$dql = $event->getDatagrid()->getDatasource()->getQueryBuilder()->getQuery()->getDQL();

The where part after applying filter is:

MySQL

WHERE v.createdAt is null AND v.inTime >= :vdate1206514550 AND v.inTime <= :vdate673657023

1	WHERE v.createdAt is null AND v.inTime >= :vdate1206514550 AND v.inTime <= :vdate673657023

This shows that my condition is added But is added with ‘AND’ instead of ‘OR’
what I expect to add was:

PHP

WHERE v.createdAt is null OR (v.inTime >= :vdate1206514550 AND v.inTime <= :vdate673657023)

1	WHERE v.createdAt is null OR (v.inTime >= :vdate1206514550 AND v.inTime <= :vdate673657023)

[Ilya AntypenkoParticipant]

> But this where that I want to prevent to apply is filter’s value that user chooses.
Oh, ok.

You can implement your own ExtensionVisitorInterface which will modify
$datasource->getQueryBuilder() according to your requirements.

Here you can find an example of FilterExtension which is adding filter conditions to the datagrid query builder:
https://github.com/oroinc/platform/blob/25224753c20a0acf4a6645d861c912f2af58c9ad/src/Oro/Bundle/FilterBundle/Grid/Extension/OrmFilterExtension.php#L30

and applying them to the query builder:
https://github.com/oroinc/platform/blob/25224753c20a0acf4a6645d861c912f2af58c9ad/src/Oro/Bundle/FilterBundle/Datasource/Orm/OrmFilterDatasourceAdapter.php#L58

Some information about datagrid extension you can find here:
https://github.com/laboro/dev/blob/03df860b2c36c2348d8324676f819891dce03f67/package/platform/src/Oro/Bundle/DataGridBundle/Resources/doc/backend/extensions.md

and

https://oroinc.com/b2b-ecommerce/doc/current/dev-guide/extend-and-customize/customizing-data-grid-in-orocommerce

Another solution is to override you specific filter and don’t add condition to where at all.
For example ChoiceFilter:
https://github.com/oroinc/platform/blob/25224753c20a0acf4a6645d861c912f2af58c9ad/src/Oro/Bundle/FilterBundle/Resources/config/filters.yml#L60-L67

> ‘or where’ part is added successfully, because my condition is added in this index:

As you can see this configuration directly passes to Doctrine.
https://github.com/oroinc/platform/blob/2.6/src/Oro/Bundle/DataGridBundle/Datasource/Orm/QueryConverter/YamlConverter.php#L201-L216

Additional debug is required, but I found such ticket in Doctrine’s bug tracker:
https://github.com/doctrine/orm/issues/7534

Hope my answers will help you.

[Author]

Replies