Symfony Query Builder (Subquery)

Question

How would this SQL query look in the Query Builder? Unfortunately, subselects are not possible, right?

SQL:

SELECT t.* FROM domain_check_result AS t
INNER JOIN (SELECT *, MAX(checkDate) AS MaxDate
FROM domain_check_result
GROUP BY domain_id) AS tm ON t.domain_id = tm.domain_id AND t.checkDate = tm.MaxDate

What have you tried so far? Where are you stuck? – Nico Haase Aug 04 '20 at 14:59 — Nico Haase, Aug 04 '20 at 14:59

score 0 · Answer 1 · answered Aug 06 '20 at 16:30

To pick latest result from your group (domains) you can rewrite your query as below and it would be easier to translate this query in DQL and query builder

SQL

SELECT 
  a.* 
FROM
  domain_check_result a 
  LEFT JOIN domain_check_result b 
    ON a.domain_id = b.domain_id 
    AND a.checkDate < b.checkDate 
WHERE b.checkDate IS NULL 
ORDER BY a.checkDate DESC

DQL

SELECT a 
FROM AppBundle\Entity\DomainCheckResult a
    LEFT JOIN AppBundle\Entity\DomainCheckResult b 
    WITH a.domain = b.domain 
    AND a.checkDate < b.checkDate
WHERE b.checkDate IS NULL
ORDER BY a.checkDate DESC

And in query you could translate your DQL as

$repo = $DM->getRepository( 'AppBundle\Entity\DomainCheckResult' );
$results = $repo->createQueryBuilder( 'a' )
                ->select( 'a' )
                ->leftJoin(
                    'AppBundle\Entity\DomainCheckResult',
                    'b',
                    'WITH',
                    'a.domain = b.domain AND a.checkDate < b.checkDate'
                )
                ->where( 'b.checkDate IS NULL' )
                ->orderBy( 'a.checkDate','DESC' )
                ->getQuery()
                ->getResult();

Reference: Doctrine Query Language get Max/Latest Row Per Group

Symfony Query Builder (Subquery)

1 Answers1