Doctrine2: How to create nested JOIN statements

Question

I am using Doctrine to build the following query:

SELECT 
    c0_.id AS id0,
    c0_.name AS name1,
    SUM(a1_.credit) AS sclr2,
    SUM(a1_.debit) AS sclr3
FROM
    complexes c0_
        LEFT JOIN
    acc_records a1_ 
        INNER JOIN
            acc_codes a2_ 
            ON (a2_.id = a1_.acccode_id AND a2_.l1 = 3 AND a2_.l2 = 10)
    ON c0_.id = a1_.complex_id
GROUP BY c0_.id

As you can see I put ON statements next to each other to make a nested join query.

The following code puts the ON statement next to the corresponding JOIN statement.

$er->createQueryBuilder('c')->select('c.id, c.name,sum(ad.credit) as cr, sum(ad.debit) as dr')
->leftJoin('c.accrecords','ad')
->innerJoin('AccCode', 'ac', 'WITH','ac.id = ad.acccode and ac.l1=3 and ac.l2=10')
->groupBy('c.id')
->getQuery()->getSQL();

The result is this:

SELECT 
    c0_.id AS id0,
    c0_.name AS name1,
    SUM(a1_.credit) AS sclr2,
    SUM(a1_.debit) AS sclr3
FROM
    complexes c0_
        LEFT JOIN
    acc_records a1_ ON c0_.id = a1_.complex_id
        INNER JOIN
    acc_codes a2_ ON (a2_.id = a1_.acccode_id AND a2_.l1 = 3
        AND a2_.l2 = 10)
GROUP BY c0_.id

which is not working for me. Does anyone have a solution?

Answer 1

 $em= $this->entityManager;

            $rsm = new ResultSetMapping();

            $rsm->addEntityResult('MembersManagementBundle:Message', 'm');
            $rsm->addFieldResult('m', 'id', 'id');
            $rsm->addFieldResult('m', 'message_date', 'messageDate');
            $rsm->addMetaResult('m', 'sender_id', 'sender_id');
            $rsm->addMetaResult('m', 'receiver_id', 'receiver_id'); 
            $rsm->addMetaResult('m', 'post_id', 'post_id');
            $rsm->addScalarResult('result', 'result');

            $query = $em->createNativeQuery('SELECT m.*, s.rs as result
                FROM  message AS m
                INNER JOIN (SELECT id, sender_id, MAX(message_date) as md FROM message WHERE message_seen=0 and  receiver_id =? GROUP BY post_id,sender_id) AS t
                INNER JOIN (SELECT id, sender_id, COUNT(message_seen) as rs FROM message WHERE message_seen=0 and receiver_id=? GROUP BY  post_id, sender_id) AS s
                ON m.message_date=t.md and t.id=s.id
                WHERE receiver_id =?
                GROUP BY post_id, sender_id
                ORDER BY message_date DESC', $rsm);
            $query->setParameter(1, $receiver_id)
            ->setParameter(2, $receiver_id)
            ->setParameter(3, $receiver_id);

            $n= $query->getResult();