Sql query into Datagrid EventListener

Hello guys!

Is it possible to add a sql query in the datagrid event listener?

That would be the query:

PgSQL

SELECT COALESCE(t2.first_name, ' ') || ' ' || COALESCE(t2.last_name, '') FROM ibv_household_member AS t1 JOIN orocrm_contact AS t2 ON t2.id = t1.contact_id WHERE t1.household_id IN (SELECT DISTINCT t3.household_id FROM ibv_household_member AS t3 WHERE t3.contact_id = t0.id) AND t1.responsible = 't' LIMIT 1

1 2 3 4 5 6 7

SELECT COALESCE(t2.first_name, ' ') || ' ' || COALESCE(t2.last_name, '') FROM ibv_household_member AS t1 JOIN orocrm_contact AS t2 ON t2.id = t1.contact_id WHERE t1.household_id IN (SELECT DISTINCT t3.household_id                           FROM ibv_household_member AS t3                           WHERE t3.contact_id = t0.id) AND t1.responsible = 't' LIMIT 1

And, for while, my event listener is like that:

PHP

namespace Ibv\Bundle\RdContactBundle\EventListener\Datagrid; use Oro\Bundle\DataGridBundle\Event\BuildBefore; use Oro\Bundle\DataGridBundle\Datasource\Orm\OrmDatasource; use Doctrine\ORM\EntityRepository; use Doctrine\ORM\Query\Expr\Join; class RdGridFields { public function onBuildBefore(BuildBefore $event) { $config = $event->getConfig(); $query = $config->getOrmQuery(); $query->addSelect(SELECT COALESCE(t2.first_name, ' ') || ' ' || COALESCE(t2.last_name, '') FROM ibv_household_member....); $config->offsetAddToArrayByPath('[columns][newColumn]', [ 'label' => 'rd.contact.household_responsible.grid.label' ]); } }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

namespace Ibv\Bundle\RdContactBundle\EventListener\Datagrid;   use Oro\Bundle\DataGridBundle\Event\BuildBefore; use Oro\Bundle\DataGridBundle\Datasource\Orm\OrmDatasource; use Doctrine\ORM\EntityRepository; use Doctrine\ORM\Query\Expr\Join;   class RdGridFields {     public function onBuildBefore(BuildBefore $event)     {        $config = $event->getConfig();        $query = $config->getOrmQuery();          $query->addSelect(SELECT COALESCE(t2.first_name, ' ') || ' ' || COALESCE(t2.last_name, '') FROM ibv_household_member....);          $config->offsetAddToArrayByPath('[columns][newColumn]', [         'label'         => 'rd.contact.household_responsible.grid.label'        ]);     } }

If possible, how to do it?

Thanks! :D

Topic

[William RadiParticipant]

Hello, guys!

Any news?

[Author]

Replies