Join query to union query using ORM

[onepoint0]

Hi Devs,

I have an SQL I'm trying to convert into an ORM but I can't figure it out or find an example of the implementation in the code base. The raw SQL is this:

select
    CASE WHEN first_name = LAG(first_name) OVER (ORDER BY first_name) THEN NULL ELSE first_name END AS name,
    CASE WHEN last_name = LAG(last_name) OVER (ORDER BY last_name) THEN NULL ELSE last_name END AS last_name,
    CASE WHEN l.role = LAG(l.role) OVER (ORDER BY l.role) THEN NULL ELSE l.role END as role,
    CASE WHEN a.lower = LAG(a.lower) OVER (ORDER BY a.lower) THEN NULL ELSE a.lower END as email,
    CASE WHEN p.numeric = LAG(p.numeric) OVER (ORDER BY p.numeric) THEN NULL ELSE p.numeric END as phone_no,
    CASE WHEN opportunity_amount = LAG(opportunity_amount) OVER (ORDER BY opportunity_amount) THEN NULL ELSE opportunity_amount END as opportunity_amount,
    CASE WHEN product_of_interest = LAG(product_of_interest) OVER (ORDER BY product_of_interest) THEN NULL ELSE product_of_interest END as product_of_interest,
    c.type,c.name as 'Activity Name',c.status,c.date_start,c.date_end
from  `lead` l
join   entity_email_address ee
on     l.id = ee.entity_id
join   email_address a
on     ee.email_address_id = a.id
join   entity_phone_number pn
on     l.id = pn.entity_id
join   phone_number p
on     pn.phone_number_id = p.id
join  (select 'Call' as type,name,status,date_start,date_end,parent_id from `call`
         union all
         select 'Meeting' as type,name,status,date_start,date_end,parent_id from `meeting`) as c
on l.id = c.parent_id
order by first_name,date_start;

It outputs the following, where the last five columns (type, activity name, status, date start and date end) are coming from the union join of Call and Meeting:



I have tried various ideas around the following code, but can't make it work. The union query works fine, and the main select from Lead works fine, but sticking the main select and the union together doesn't (it's the $q part that is failing):

        $subQuery1 = QueryBuilder::create()
            ->select('id','parent_id')
            ->from('Call')
            ->build();

        $subQuery2 = QueryBuilder::create()
            ->select('id','parent_id')
            ->from('Meeting')
            ->build();

        $union = $this->entityManager
            ->getQueryBuilder()
            ->union()
            ->all()
            ->query($subQuery1)
            ->query($subQuery2)
            ->build();

        $q = $this->entityManager
            ->getQueryBuilder()
            ->select()
            ->from('Lead') 
            ->fromQuery($union, 'c') // from query is not right, but what??
            ->where([
                'lead.id:' => 'u.parent_id'
            ])
            ->build();​

        $sth = $this->entityManager
            ->getQueryExecutor()
            ->execute($q);

        foreach ($sth as $u) {
            $GLOBALS['log']->error(print_r('in loop',true));            
            $GLOBALS['log']->error(print_r($u,true));            
        }​

Can anyone please help?

Cheers,
Clare

[yuri]

Hi Clare,

I recommend to output SQLs to the log for debugging purpose. ('sql' => true) https://docs.espocrm.com/administration/log/#parameters