CODEWITHSUNDEEP
filterdatagridorocrm
Mario
Mario

Reputation: 13

Orocrm datagrid Filters don't work for aggregate field column

I had a query with subquery with aggregate field:

$qRoles = $this->getEntityManager()
    ->createQueryBuilder()
    ->addSelect('group_concat(r.label)')
    ->from(UserAccessRole::class, 'uar')
    ->leftJoin(Role::class, 'r', 'WITH', 'uar.role = r.id')
    ->where('uar.userId = a.id')
    ->addGroupBy('uar.userId');

    $qb = $this->getEntityManager()
        ->createQueryBuilder()
        ->from(Account::class, 'a')
        ->addSelect('a.name')
        ->addSelect('(' . $qRoles->getDql() . ') as roles');

    return $qb;

And a yaml like:

    accounts-datagrid:
    source:
        skip_acl_apply: true
        query_builder: '@mybundle.acl.repository->getVisibleAccounts()'
    columns:
        name:
            label: name
        roles:
            label: Ruoli
            frontend_type: string
    filters:
        columns:
            name:
                type: string
                data_name: a.name
            roles:
                type: string
                data_name: roles
                filter_by_having: false
    sorters:
        columns:
            name:
                data_name: a.name
            roles:
                data_name: roles

I obtain a data grid like this:

NAME       ROLES
admin      Seller,Buyer,Administrator
pippo      Seller
pluto      Buyer
...        ...

When i insert a filter string for ROLES column Es: 'Seller'

I had an internal 500 error

[Syntax Error] line 0, col 407: Error: Expected =, <, <=, <>, >, >=, !=, got 'LIKE' 500 Internal Server Error - QueryException 1 linked Exception: QueryException »

[2/2] QueryException: [Syntax Error] line 0, col 407: Error: Expected =, <, <=, <>, >, >=, !=, got 'LIKE'

[1/2] QueryException: SELECT a.id FROM mybundle...\Account a INNER JOIN a.user u WHERE (SELECT group_concat(r.label) FROM mybundle...\UserAccessRole uar LEFT JOIN mybundle...\Role r WITH uar.role = r.id WHERE uar.userId = a.id GROUP BY uar.userId) LIKE :roles1085237485

Why is there an error on this kind of field? If i filter on other fields everything is ok.

Please help me, this thinks get me crazy. Thank you

Upvotes: 0

Views: 124

Answers (1)

Vladi
Vladi

Reputation: 1

I found that you can set the option force_like to true. Maybe it helps you

https://doc.oroinc.com/backend/configuration/yaml/datagrids/#filters

Upvotes: 0

Related Questions