Unvalid data_name for Datagrid Listener Filter

Hi everyone.

I need to use a filter in my datagrid listener. I want to set a filter in my datagrid which its data_name is not valid because I want to do my join and add my where in my listener.
I have 2 column.
One is static in datagrid which I am adding the where clause for it in preBuild method. Here is the code for this method:

PHP

/** * @param PreBuild $event */ public function preBuild(PreBuild $event) { $originalQb = $event->getConfig()->getOrmQuery(); $busUnitName = $event->getParameters()->get('_filter')['busUnitName']['value']; if ($busUnitName) { $originalQb->addInnerJoin('er.owner', 'owner') ->addInnerJoin('owner.businessUnits', 'bus_unit') ->addAndWhere('bus_unit.name LIKE \'%'.$busUnitName.'%\''); } }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

/**      * @param PreBuild $event      */     public function preBuild(PreBuild $event)     {         $originalQb = $event->getConfig()->getOrmQuery();           $busUnitName = $event->getParameters()->get('_filter')['busUnitName']['value'];           if ($busUnitName) {             $originalQb->addInnerJoin('er.owner', 'owner')                 ->addInnerJoin('owner.businessUnits', 'bus_unit')                 ->addAndWhere('bus_unit.name LIKE \'%'.$busUnitName.'%\'');         }     }

My first problem is this method is not giving me the right result. I can see the query is adding my where clause and also in onBuildBefore I checked my query. The where clause is set. But it’s not working.
Ok. Now my second problem is with adding the data for second column in my onResultAfter which I need to add the filter to its query too.
Here is the code:

PHP

/** * @param OrmResultAfter|OrmResultAfter $event */ public function onResultAfter(OrmResultAfter $event) { $qb = $this->em->createQueryBuilder() ->select('campaign.id, COUNT(DISTINCT g.report) AS g_number') ->from('WebSysVisitBundle:EventReport', 'er') ->innerJoin('er.visit', 'visit') ->innerJoin('visit.campaign', 'campaign') ->innerJoin('visit.category', 'category') ->innerJoin('WebSysVisitBundle:EventGuest', 'g', 'WITH', 'IDENTITY(g.report) = er.id') ->andWhere('category.category =\'normal\' ') ->groupBy('campaign.id'); $campaignName = $event->getDatagrid()->getParameters()->get('_filter')['campaignName']['value']; $busUnitName = $event->getDatagrid()->getParameters()->get('_filter')['busUnitName']['value']; $vRegion = $event->getDatagrid()->getParameters()->get('_filter')['vRegion']['value']; $vCity = $event->getDatagrid()->getParameters()->get('_filter')['vCity']['value']; $vDistrict = $event->getDatagrid()->getParameters()->get('_filter')['vDistrict']['value']; $vInTime = $event->getDatagrid()->getParameters()->get('_filter')['vInTime']['value']; if ($campaignName) { $qb->andWhere('campaign.name LIKE \'%'.$campaignName.'%\''); } if ($busUnitName) { $qb->innerJoin('er.owner', 'owner') ->innerJoin('owner.businessUnits', 'bus_unit') ->andWhere('bus_unit.name LIKE \'%'.$busUnitName.'%\''); } if ($vRegion) { $qb->andWhere('visit.stringRegion LIKE \'%'.$vRegion.'%\''); } if ($vCity) { $qb->andWhere('visit.stringCity LIKE \'%'.$vCity.'%\''); } if ($vDistrict) { $qb->andWhere('visit.stringDistrict LIKE \'%'.$vDistrict.'%\''); } if ($vInTime) { $qb->andWhere('Date(visit.inTime) >= :from') ->andWhere('Date(visit.inTime) <= :to') ->setParameter('from', $vInTime['start']) ->setParameter('to', $vInTime['end']); } $records = $event->getRecords(); $result = $qb->getQuery()->getResult(); foreach ($records as $r) { foreach ($result as $guest) { if ($r->getValue('cid') == $guest['id']) { $r->addData(['guestsNumber' => $guest['g_number']]); break; } } } }

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

/**      * @param OrmResultAfter|OrmResultAfter $event      */     public function onResultAfter(OrmResultAfter $event)     {                  $qb = $this->em->createQueryBuilder()             ->select('campaign.id, COUNT(DISTINCT g.report) AS g_number')             ->from('WebSysVisitBundle:EventReport', 'er')             ->innerJoin('er.visit', 'visit')             ->innerJoin('visit.campaign', 'campaign')             ->innerJoin('visit.category', 'category')             ->innerJoin('WebSysVisitBundle:EventGuest', 'g', 'WITH', 'IDENTITY(g.report) = er.id')             ->andWhere('category.category =\'normal\' ')             ->groupBy('campaign.id');           $campaignName = $event->getDatagrid()->getParameters()->get('_filter')['campaignName']['value'];         $busUnitName = $event->getDatagrid()->getParameters()->get('_filter')['busUnitName']['value'];         $vRegion = $event->getDatagrid()->getParameters()->get('_filter')['vRegion']['value'];         $vCity = $event->getDatagrid()->getParameters()->get('_filter')['vCity']['value'];         $vDistrict = $event->getDatagrid()->getParameters()->get('_filter')['vDistrict']['value'];         $vInTime = $event->getDatagrid()->getParameters()->get('_filter')['vInTime']['value'];           if ($campaignName) {             $qb->andWhere('campaign.name LIKE \'%'.$campaignName.'%\'');         }         if ($busUnitName) {             $qb->innerJoin('er.owner', 'owner')                 ->innerJoin('owner.businessUnits', 'bus_unit')                 ->andWhere('bus_unit.name LIKE \'%'.$busUnitName.'%\'');         }           if ($vRegion) {             $qb->andWhere('visit.stringRegion LIKE \'%'.$vRegion.'%\'');         }           if ($vCity) {             $qb->andWhere('visit.stringCity LIKE \'%'.$vCity.'%\'');         }           if ($vDistrict) {             $qb->andWhere('visit.stringDistrict LIKE \'%'.$vDistrict.'%\'');         }           if ($vInTime) {             $qb->andWhere('Date(visit.inTime) >= :from')                 ->andWhere('Date(visit.inTime) <= :to')                 ->setParameter('from', $vInTime['start'])                 ->setParameter('to', $vInTime['end']);         }           $records = $event->getRecords();           $result = $qb->getQuery()->getResult();           foreach ($records as $r) {             foreach ($result as $guest) {                 if ($r->getValue('cid') == $guest['id']) {                     $r->addData(['guestsNumber' => $guest['g_number']]);                     break;                 }             }         }     }

As you can see I am adding same filters to this query too(city and etc. I have these in my datagrid with valid data_name).
But I can’t add businessUnits to this query because when I set the filter in view onResultAfter won’t be called.
This is my main problem.

The main question is how can I make onResultAfter being called even when I’m using unvalid data_name for my filters? Is there any other way so I can add my join and filter in listener? Also with this problem I can’t get my sql and see what is my native query and why my first problem is happening.

My oro version is 1.8

Thanks,
Aryana

• This topic was modified 4 years, 10 months ago by  a.azadi.
• This topic was modified 4 years, 10 months ago by  a.azadi.

Topic