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.
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?
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments