Sorter For Dynamic DataGrid

Hi everyone. I wanted to write a sorter for a datagrid which is getting its columns and data from a listener.
This is my two methods in my listener class.
I want to know how to make sorter for these columns.
Also during searching I found out a method in “DatagridConfiguration” called “addSorter”. But I don’t know how to use it IF it’s gonna work for adding sorter to our columns.

Thank You,
Aryana.

PHP

/** * @param BuildBefore $event */ public function onBuildBefore(BuildBefore $event) { $filters = $event->getDatagrid()->getParameters()->get('_filter'); $datagridConfiguration = $event->getConfig(); $this->addGroupContactsColumn($datagridConfiguration, $filters); } /** * @param DatagridConfiguration $datagridConfiguration */ protected function addGroupContactsColumn(DatagridConfiguration $datagridConfiguration, $filter) { $couter = 0; $visitor = null; $visitfromDate = null; $visittoDate = null; if (isset($filter['visitDate'])) { $visitfromDate = new \DateTime($filter['visitDate']['value']['start'], new \DateTimeZone('UTC')); $visittoDate = new \DateTime($filter['visitDate']['value']['end'], new \DateTimeZone('UTC')); } if (isset($filter['visitorName'])) { $visitor = $filter['visitorName']; } $qb = $this->em->getRepository("WebSysVisitBundle:Visit")->createQueryBuilder("v") ->addSelect("g.id as groupId , g.label as groupName") ->join("v.visitor", "user") ->join("v.customer", "contact") ->join("contact.groups", "g") ->addGroupBy("g.id ,g.label"); if ($visitor) { $searchstring = $visitor['value']; $qb->andWhere(" user.firstName like :searchstring or user.lastName like :searchstring ") ->setParameter('searchstring', $searchstring); } if ($visitfromDate) { $qb->andWhere(" Date(v.inTime) >= :fromDate ") ->setParameter('fromDate', $visitfromDate->format('Y-m-d')); } if ($visittoDate) { $qb->andWhere(" Date(v.inTime) <= :toDate ") ->setParameter('toDate', $visittoDate->format('Y-m-d')); } $result = $qb->getQuery()->getResult(); foreach ($result as $group) { $column = [ 'label' => $group['groupName'], ]; // column name should be ther same as the field alias in the select query $datagridConfiguration->addColumn($group['groupName'], $column); } }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68

/**      * @param BuildBefore $event      */     public function onBuildBefore(BuildBefore $event)     {           $filters = $event->getDatagrid()->getParameters()->get('_filter');           $datagridConfiguration = $event->getConfig();           $this->addGroupContactsColumn($datagridConfiguration, $filters);         }       /**      * @param DatagridConfiguration $datagridConfiguration      */     protected function addGroupContactsColumn(DatagridConfiguration $datagridConfiguration, $filter)     {           $couter = 0;         $visitor = null;         $visitfromDate = null;         $visittoDate = null;           if (isset($filter['visitDate'])) {             $visitfromDate = new \DateTime($filter['visitDate']['value']['start'], new \DateTimeZone('UTC'));             $visittoDate = new \DateTime($filter['visitDate']['value']['end'], new \DateTimeZone('UTC'));         }         if (isset($filter['visitorName'])) {             $visitor = $filter['visitorName'];         }         $qb = $this->em->getRepository("WebSysVisitBundle:Visit")->createQueryBuilder("v")             ->addSelect("g.id as groupId , g.label as groupName")             ->join("v.visitor", "user")             ->join("v.customer", "contact")             ->join("contact.groups", "g")             ->addGroupBy("g.id ,g.label");           if ($visitor) {             $searchstring = $visitor['value'];               $qb->andWhere(" user.firstName like :searchstring or user.lastName like :searchstring ")                 ->setParameter('searchstring', $searchstring);         }         if ($visitfromDate) {             $qb->andWhere(" Date(v.inTime) >= :fromDate ")                 ->setParameter('fromDate', $visitfromDate->format('Y-m-d'));         }         if ($visittoDate) {             $qb->andWhere(" Date(v.inTime) <= :toDate ")                 ->setParameter('toDate', $visittoDate->format('Y-m-d'));         }           $result = $qb->getQuery()->getResult();           foreach ($result as $group) {             $column = [                 'label' => $group['groupName'],             ];             // column name should be ther same as the field alias in the select query               $datagridConfiguration->addColumn($group['groupName'], $column);         }         }

• This topic was modified 5 years, 3 months ago by  a.azadi.
• This topic was modified 5 years, 3 months ago by  a.azadi.

Topic

[Yevhen ShyshkinParticipant]

Hello, a.azadi.

It depends on the way you work with datasource.

If you have modified original datasource (e.g. by adding new select and/or join parts) and it is in fact presented in DB then you may add a sorter for it. Have a look at the example – here listener adds a new sorter for a column that was added previously in the same listener. offsetSetByPath method is used here, but addSorter can be used as well – addSorter is just a shortcut for this kind of operations.

If you haven’t modified your datasource and just mixing the data using separate query or any other source then standard out of the box sorters will not work for you, unfortunately.

[a.azadiParticipant]

Thank you for your answer and help. I wrote this line of code after the addColumn method line:

PHP

$datagridConfiguration-> offsetSetByPath(sprintf('[sorters][columns][%s]', $group['groupName']), ['data_name' => $group['groupName']]);

1 2 3

$datagridConfiguration-> offsetSetByPath(sprintf('[sorters][columns][%s]', $group['groupName']), ['data_name' => $group['groupName']]);

It adds the sorter but sorters won’t work at all. No response when I use sorter on any column.
I tried to debug and try some other things but could not find a solution.
Do you have any idea about the problem here?

Thank You Again,
Aryana.

[Yevhen ShyshkinParticipant]

Hello Aryana.

It does not work because main datagrid query is not aware about these columns, so you have no result.

Standard grid uses one main query to get data, filter data and sort data – so, default filters and sorter work on this query. In your case main query remained untouched – it doesn’t have columns for group names as group name in your case is just a label, not a field in the query.

When you are working with grid you are not using main query – instead you have created new additional query ($qb = $this->em->getRepository(“WebSysVisitBundle:Visit”)…) to get groups and manually applied filters. That is why main query is not aware about your groups and sorters do not work.

If you want to fix it you may add new join for each found group and then set required query column to each sorter.

[a.azadiParticipant]

Hello Yehven.

Unfortunately I did not understand your solution. I asked some of my friends and they could not understand this fix too: ” If you want to fix it you may add new join for each found group and then set required query column to each sorter. ”
Can you please tell me more about it?

Thank you a lot.

• This reply was modified 5 years, 2 months ago by  a.azadi.

[Yevhen ShyshkinParticipant]

Imagine you have some main entity e.g. Customer and then you can show a filterable/sortable column for each associated Group. You have 3 groups with IDs 1, 2 and 3.

To do that you have to join Group entity three times like that:

MySQL

SELECT c.id, c.name, g1.data, g2.data, g3.data FROM oro_customer c LEFT JOIN oro_customer_group g1 ON c.group_id = g1.id AND g1.id = 1 LEFT JOIN oro_customer_group g2 ON c.group_id = g2.id AND g1.id = 2 LEFT JOIN oro_customer_group g3 ON c.group_id = g3.id AND g1.id = 3

1 2 3 4 5 6 7 8 9 10 11

SELECT c.id, c.name, g1.data, g2.data, g3.data FROM oro_customer c LEFT JOIN oro_customer_group g1 ON c.group_id = g1.id AND g1.id = 1 LEFT JOIN oro_customer_group g2 ON c.group_id = g2.id AND g1.id = 2 LEFT JOIN oro_customer_group g3 ON c.group_id = g3.id AND g1.id = 3

By doing that you now can work with three separate joins/aliases and create filter/sorter for each of them.

Of course, this is just a demonstration of the approach, but using it you may have a new column for each group, select some column and then add a filter a sorted using these new relations.

[a.azadiParticipant]

I have some columns which I’m getting from this query:

PHP

$qb = $this->em->getRepository("WebSysVisitBundle:Visit")->createQueryBuilder("v") ->addSelect("g.id as groupId , g.label as groupName") ->join("v.visitor", "user") ->join("v.customer", "contact") ->join("contact.groups", "g") ->addGroupBy("g.id ,g.label");

1 2 3 4 5 6

$qb = $this->em->getRepository("WebSysVisitBundle:Visit")->createQueryBuilder("v")             ->addSelect("g.id as groupId , g.label as groupName")             ->join("v.visitor", "user")             ->join("v.customer", "contact")             ->join("contact.groups", "g")             ->addGroupBy("g.id ,g.label");

Now, my result is not static. It’s dynamic based on the data in the database I’m using. It may get 10 rows from the database or it may get only one.
So my columns varies between maybe 1 to 20 columns.
I tried to add select to my query like this:

PHP

$result = $qb->getQuery()->getResult(); $queryBuilder = $datasource->getQueryBuilder(); foreach ($result as $group) { $queryBuilder->addSelect($group['groupName']." as ".$group['groupName']); }

1 2 3 4 5 6

$result = $qb->getQuery()->getResult();           $queryBuilder = $datasource->getQueryBuilder();         foreach ($result as $group) {             $queryBuilder->addSelect($group['groupName']." as ".$group['groupName']);         }

I got exception which I think is obvious because $group[‘groupName’] is not a class. It’s a string and it’s exactly my column name which I want to use sort and filter on.

And this in my datagrid:

YAML

websys-count-visit-visitor-grid: pageTitle: websys.visit.report.count_visit_by_visitor.title source: type: orm acl_resource: oro_report_view query: select: - concat(user.firstName, concat(' ', user.lastName)) as visitorName - organization.name as orgName - user.id as userId from: - { table: '%websys_visit.visit.class%', alias: v} join: left: - { join: v.visitor, alias: user } - { join: v.customer, alias: contact } - { join: contact.groups, alias: cg } - { join: user.owner, alias: organization } groupBy: user.id,user.firstName columns: visitorName: label: websys.visit.visitor.label orgName: label: Business Unit filters: columns: visitorName: type: string data_name: visitorName orgName: type: string data_name : orgName visitDate: label: websys.visit_contact.visit_date.label type: datetime data_name: v.inTime sorters: columns: visitorName: data_name: visitorName options: export: true entityHint: percent visit visitor

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

websys-count-visit-visitor-grid:         pageTitle: websys.visit.report.count_visit_by_visitor.title         source:             type: orm             acl_resource: oro_report_view             query:                 select:                     - concat(user.firstName, concat(' ', user.lastName)) as visitorName                     - organization.name as orgName                     - user.id as userId                 from:                     - { table: '%websys_visit.visit.class%', alias: v}                 join:                     left:                         - { join: v.visitor, alias: user }                         - { join: v.customer, alias: contact }                         - { join: contact.groups, alias: cg }                         - { join: user.owner, alias: organization }                 groupBy: user.id,user.firstName         columns:             visitorName:                 label: websys.visit.visitor.label             orgName:                 label: Business Unit         filters:             columns:                 visitorName:                     type: string                     data_name: visitorName                 orgName:                     type: string                     data_name : orgName                 visitDate:                     label: websys.visit_contact.visit_date.label                     type: datetime                     data_name: v.inTime         sorters:             columns:                 visitorName:                     data_name: visitorName         options:             export: true             entityHint: percent visit visitor

I guess I can’t use your solution or maybe I misunderstood your solution.

I hope I could have been explain my problem a little better. I appreciate your help and sorry if I have problem in speaking English.

Aryana.

• This reply was modified 5 years, 2 months ago by  a.azadi.

[MichaelKeymaster]

Hi Aryana,

Let me kinda “re-start” this thread from a different point.

If I understood the code you posted correctly, you want to add multiple columns to this grid. For example, if there are 20 different contact groups – then this grid will 20 additional columns, so that there will be 22 columns:
visitorName, orgName, groupName1, groupName2, groupName3, groupName4, groupName5, groupName6, groupName7, groupName8, groupName9, groupName10, groupName11, groupName12, groupName13, groupName14, groupName15, groupName16, groupName17, groupName18, groupName19, groupName20
, and each row in this grid will have 22 cells.

What should be displayed inside those additional 20 cells in each row?

[a.azadiParticipant]

Hi Michael,

Yeah it’s correct.

This is my onResultAfter Code:

PHP

/** * @param OrmResultAfter $event */ public function onResultAfter(OrmResultAfter $event) { // here we take the value of name_filter parameter $records = $event->getRecords(); $params = $event->getDatagrid()->getParameters(); $filter = $event->getDatagrid()->getParameters()->get('_filter'); $records = $event->getRecords(); $this->addPercentageVisitRecords($records, $filter); } /** * @param ResultRecord[] $records * @throws \Doctrine\ORM\ORMException */ protected function addPercentageVisitRecords(array $records, $filter) { $visitfromDate = null; $visittoDate = null; if (isset($filter['visitDate'])) { $visitfromDate = new \DateTime($filter['visitDate']['value']['start'], new \DateTimeZone('UTC')); $visittoDate = new \DateTime($filter['visitDate']['value']['end'], new \DateTimeZone('UTC')); } if (isset($filter['visitorName'])) { $visitor = $filter['visitorName']; } foreach ($records as $record) { $userID = $record->getValue('userId'); $qb = $this->em->getRepository("WebSysVisitBundle:Visit")->createQueryBuilder("v") ->addSelect("count(v.id) as visitCount") ->join("v.visitor", "user") ->join("v.customer", "contact") ->join("contact.groups", "g") ->addGroupBy("user.id"); $qb->andWhere(" user.id = :visitorId ") ->setParameter('visitorId', $userID); if ($visitfromDate) { $qb->andWhere(" Date(v.inTime) >= :fromDate ") ->setParameter('fromDate', $visitfromDate->format('Y-m-d')); } if ($visittoDate) { $qb->andWhere(" Date(v.inTime) <= :toDate ") ->setParameter('toDate', $visittoDate->format('Y-m-d')); } $result = $qb->getQuery()->getResult(); $qb->addGroupBy("g.id"); $qb->addSelect("g.label as groupId"); $resultperGroup = $qb->getQuery()->getResult(); foreach ($resultperGroup as $group) { $groupId = $group['groupId']; $count = $group['visitCount']; $record->addData([$groupId => $count]); } } }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72

/**      * @param OrmResultAfter $event      */     public function onResultAfter(OrmResultAfter $event)     {         // here we take the value of name_filter parameter         $records = $event->getRecords();         $params = $event->getDatagrid()->getParameters();           $filter = $event->getDatagrid()->getParameters()->get('_filter');             $records = $event->getRecords();         $this->addPercentageVisitRecords($records, $filter);       }       /**      * @param ResultRecord[] $records      * @throws \Doctrine\ORM\ORMException      */     protected function addPercentageVisitRecords(array $records, $filter)     {           $visitfromDate = null;         $visittoDate = null;           if (isset($filter['visitDate'])) {             $visitfromDate = new \DateTime($filter['visitDate']['value']['start'], new \DateTimeZone('UTC'));             $visittoDate = new \DateTime($filter['visitDate']['value']['end'], new \DateTimeZone('UTC'));         }         if (isset($filter['visitorName'])) {             $visitor = $filter['visitorName'];         }           foreach ($records as $record) {             $userID = $record->getValue('userId');             $qb = $this->em->getRepository("WebSysVisitBundle:Visit")->createQueryBuilder("v")                 ->addSelect("count(v.id) as visitCount")                 ->join("v.visitor", "user")                 ->join("v.customer", "contact")                 ->join("contact.groups", "g")                 ->addGroupBy("user.id");               $qb->andWhere(" user.id = :visitorId ")                 ->setParameter('visitorId', $userID);               if ($visitfromDate) {                 $qb->andWhere(" Date(v.inTime) >= :fromDate ")                     ->setParameter('fromDate', $visitfromDate->format('Y-m-d'));             }             if ($visittoDate) {                 $qb->andWhere(" Date(v.inTime) <= :toDate ")                     ->setParameter('toDate', $visittoDate->format('Y-m-d'));             }               $result = $qb->getQuery()->getResult();               $qb->addGroupBy("g.id");             $qb->addSelect("g.label as groupId");             $resultperGroup = $qb->getQuery()->getResult();               foreach ($resultperGroup as $group) {                 $groupId = $group['groupId'];                 $count = $group['visitCount'];                 $record->addData([$groupId => $count]);             }           }         }

Getting the data from the query and adding it to my datagrid.

[Author]

Replies