Right join query in symfony3 with doctrine

Question

I want to convert the sql query below to doctrine query in symfony.

select p.name,p.id,sum(t.amount) as bal from transactions t right join processors p on t.processor_id=p.id where user_id=18 or user_id is null group by p.id

The above code fetches balance from transactions table by summing up amounts of each transaction for a user for each processor.

Result:

Processor1 --------- 43

Processor2 --------- 12

Processor3 --------- NULL

Processor4 --------- NULL

Processor5 --------- NULL

The query i tried with dql is:

$sql = $procRepo->createQueryBuilder('t');
        $sql->select('p.name');
        $sql->leftJoin('t.processorId','p');
        $sql->addSelect('sum(t.amount) as bal');
        $sql->groupBy('p.id');
        $sql->orderBy('p.name')->getQuery()->getResult();

Result:

Processor1 --------- 43

Processor2 --------- 12

So my problem is i also want to get the NULL rows.

Note: I am using Symfony 3

Can anybody help?

Answer 1

You needs to invert the join statement to gets all processors:

$sql = $procRepo->createQueryBuilder('p');
$sql->select('p.name', 'sum(t.amount) as bal');
$sql->leftJoin('p.transaction', 't');
$sql->groupBy('p.id');
$result = $sql->orderBy('p.name')->getQuery()->getResult();

This query must be made in your ProcessorRepository.