Doctrine dql where interpretation of a sentence with an associated object

Question

Given this object

class SystemRecord { /** * @ORM\Id * @ORM\Column(type="integer", name="ID") * @ORM\GeneratedValue * @var int */ private $id; /** * @ORM\ManyToOne(targetEntity="Application\Entity\User") * @ORM\JoinColumn(name="USER_SERIAL", referencedColumnName="SERIAL", nullable=false) * @var User */ private $user; /** * @ORM\Column(type="utcdatetime", name="DATE_DATA_WAS_FETCHED", nullable=false) * @var DateTimeInterface */ private $dateDataWasFetched; } 

... and this is dql

 $dql = " select r from Application\\Entity\\SystemRecord r join Application\\Entity\\User u where r.dateDataWasFetched = ( select max(r2.dateDataWasFetched) from Application\\Entity\\SystemRecord r2 ) and u.serial = :serial "; $query = $this->getEntityManager()->createQuery($dql); $query->setParameter('serial', $user->getSerial()); $sql = $query->getSql(); 

... I hope to get "SystemRecords for the user with the specified serial number, but only those who have the most recent date from any SystemRecord." In other words, procedurally, "find the latest date of any SystemRecord for any user. Then find the records for the specified user that occurred on that date."

If I wrote sql, I would write

 select * from SYSTEM_RECORDS r join USER u on r.USER_SERIAL = u.SERIAL where DATE_DATA_WAS_FETCHED = (select max(DATE_DATA_WAS_FETCHED) from SYSTEM_RECORDS) and u.SERIAL = ? 

But, the doctrine gives me the following sql

 SELECT ...fields from s0_ ... FROM SYSTEM_RECORDS s0_ INNER JOIN USER u1_ ON (s0_.DATE_DATA_WAS_FETCHED = (SELECT max(s2_.DATE_DATA_WAS_FETCHED) AS dctrn__1 FROM SYSTEM_RECORDS s2_) AND u1_.SERIAL = ?) 

This is not what I want. This gives me "SystemRecords for all users whose SystemRecords have the same date as the most recent SystemRecords for the user with the specified serial number."

How to formulate my query using dql?

---

Answer 1

If you understand correctly, you need to use an additional query like you, but I think you are missing the in expression. Using QueryBuilder you built a query to get this result (I always write my queries using QueryBuilder ):

 $qb->select(r) ->from('SystemRecord', 'r') ->join('r.user', 'u') ->where( $qb->expr()->in( 'r.dateDataWasFetched', "SELECT max(r2.dateDataWasFetched) FROM Application\\Entity\\SystemRecord r2" ) ) ->andWhere('u.serial' = :user_serial) ->setParameter('user_serial', $user->getSerial()) ->getQuery() ->getResult(); 

This answer is based on this answer to a similar question in the overflow stack .

EDIT:

If you really want DQL , then you can easily get it from your QueryBuilder instance after creating the query using the getDQL method as follows:

 $dql = $qb->getQuery()->getDQL(); 

Answer 2

I was able to solve / avoid my problem by avoiding the connection

 $dql = " select r from Application\\Entity\\SystemRecord r where r.dateDataWasFetched = ( select max(r2.dateDataWasFetched) from Application\\Entity\\SystemRecord r2 ) and r.user = :user "; $query = $this->getEntityManager()->createQuery($dql); $query->setParameter('user', $user); 

Sql result (correct)

 SELECT ...fields from s0_ ... FROM SYSTEM_RECORDS s0_ WHERE s0_.DATE_DATA_WAS_FETCHED = (SELECT max(s1_.DATE_DATA_WAS_FETCHED) AS dctrn__1 FROM SYSTEM_RECORDS s1_) AND s0_.USER_SERIAL = ? 

The significant difference is that instead of specifying an identifier for the associated object (via u.serial = :serial I now specify the object itself (via r.user = :user ), which also allows me to skip the connection. Btw - The serial field is marked with @ORM\Id in my User object.

However, this just fixes the problem. I still wonder how the doctrine interprets the request when a connection is present.

Change - real solution found

Thanks to Wilt, after using the query builder and then using the getDQL () method, I discovered the missing part. Working dql

 select r from Application\Entity\SystemRecord r join r.user u where r.dateDataWasFetched = ( select max(r2.dateDataWasFetched) from Application\\Entity\\SystemRecord r2 ) and u.serial = :serial 

Please note that the difference between the DQL in my original question and this working solution is join Application\\Entity\\User u vs join r.user u , respectively.