Doctrine query builder inner join with subselect

Question

I have this query in SQL that's working fine:

SELECT tl.*
FROM table1 tl
INNER JOIN table2 tl2
ON tl.id = tl2.other_id
INNER JOIN
(
   SELECT other_id, MAX(date) maxDATE
   FROM table2
   GROUP BY other_id
) 
tlv2 ON tl2.other_id = tlv2.other_id AND
tl2.date = tlv2.maxDATE WHERE tl.access=0
ORDER BY tlv2.maxDATE DESC

Now the problem is, I cant seem to figure out how to translate this into the Doctrine query builer.

I have this:

$subquery = $this->getEntityManager()->getRepository(Table2::class)
    ->createQueryBuilder('tl2')
    ->select(array('other_id','MAX(date) maxDate'))
    ->groupBy('other_id')
    ->getDQL();
    $qb = $this->createQueryBuilder('tl');
    $qb->select('tl')
       ->innerJoin(Table2::class,'tl2','WITH','tl.id = tl2.other_id')
       ->innerJoin("(".$subquery.")",'tlv2','WITH','tl2.date = tlv2.maxDATE')
       ->where('tl.access = 0')
       ->orderBy('tlv2.maxDATE','DESC');

but it's giving me the error:

Subquery is not supported here

On the line where I put the $subquery variable.

I cant seem to figure out what I'm doing wrong here. What am I doing wrong?

Answer 1

as mentioned here if you are using the ORM then the mapping will fail, you can only run this without hydration, in case you are using the DBAL only it will work as you expect

If you would go for native query solution and still need a mapped result then you can use \Doctrine\ORM\EntityManager::createNativeQuery with \Doctrine\ORM\Query\ResultSetMapping

$sql = <<<SQL
    SELECT tl.*
    FROM table1 tl
    INNER JOIN table2 tl2
    ON tl.id = tl2.other_id
    INNER JOIN
    (
       SELECT other_id, MAX(date) maxDATE
       FROM table2
       GROUP BY other_id
    ) 
    tlv2 ON tl2.other_id = tlv2.other_id AND
    tl2.date = tlv2.maxDATE WHERE tl.access=0
    ORDER BY tlv2.maxDATE DESC
SQL;


$rsm = new Query\ResultSetMapping();
$rsm->addEntityResult(Entity::class, 'e');
$rsm->addFieldResult('e','id', 'id');
$rsm->addFieldResult('e','date', 'createAt');

$result = $this->entityManager->createNativeQuery($sql, $rsm)->getResult();