Selecting the last record in a one-to-many relationship with DQL (Doctrine Query Language).

join-last-record-with-querybuilder.php

<?php

/**
 * This example demonstrates how to join the last record in a
 * one-to-many relationship using the Doctrine QueryBuilder.
 * 
 * @author Ricard Derheim
 */

use Doctrine\ORM\Query\Expr;

// $entityManager instanceof EntityManager

$queryBuilder = $entityManager->createQueryBuilder();
$queryBuilder
    ->select('e, i')
    ->from('Entity', 'e')
    ->innerJoin('e.items', 'i')
    ->leftJoin('e.items', 'i2', Expr\Join::WITH, 'e = i2.property AND i.id < i2.id')
    ->where('i2.id IS NULL');
    
$query = $queryBuilder->getQuery();
$result = $query->getResult();

join-last-record.dql

SELECT      e, i

FROM        Entity e
 
INNER JOIN  e.items i
 
LEFT JOIN   e.items i2
 
WITH        e = i2.entity
    AND     i.id < i2.id
 
WHERE       i2.id IS NULL