doctrine dql where clause interpretation with associated entity

Question

Given this entity

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 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'm hoping to get "the SystemRecords for the user with the specified serial, but only those with the most recent date out of any SystemRecord". In other words, procedurally, "find the most recent date of any SystemRecord for any user. Then find records for the specified user which occurred on that date."

If I were writing 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, doctrine is giving 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 = ?)

Which isn't what I want. That gives me "SystemRecords for all users whose SystemRecords have the same date as the most recent SystemRecords for the user with the specified serial".

How do I formulate my query using dql?

---

Jingoism

How do you define in dql what field(s) should be used to join two tables? Is it supposed to derive this information implicitly somehow? In your dql text I don't see any mentioning that r should be joined with u on r.user_serial = u.serial. – Maintenance

In any case, if you add and r.user_serial = :serial or and r.user_serial = u.serial to the where part of the dql text you should get the SQL query that produces correct results. – Maintenance

@VladimirBaranov I imagine doctrine uses the fields with the @Id annotation to figure out the primary key(s) to use when joining entity associations. I can't add your suggested additions because the entity doesn't have a field named user_serial, so doctrine throws an error. – Jingoism

Answer 1

If I understand you correctly you need to use a sub query like you did but I think you are missing the in expression. With QueryBuilder you would built the query to get your result like this (I always write my queries with 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 similar question on stackoverflow.

EDIT:

If you really want the DQL then you can easily get it from your QueryBuilder instance after building the query using the getDQL method like this:

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

Ist

This works. I really did want to know how to do it via dql though. If I don't get an answer eventually I'll accept this, and will upvote for now. Thanks! – Jingoism

@Jingoism Why do you can get the DQL? Using Querybuilder is more flexible. But you can easily get the DQL from your QueryBuilder instance. I will edit my answer to show you how... – Ist

I wanted to know the dql mostly out of curiosity - I guess i like the familiar and concise declarative style that reminds me of sql. Anyway, the getDQL() method helped me solve it, see my edited answer. Thank you! – Jingoism

Answer 2

I was able to solve/avoid my problem by avoiding a join

$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);

Resulting sql(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 notable difference is that instead of specifying the id for the associated entity(via u.serial = :serial, I'm now specifying the entity itself(via r.user = :user). This allows me to omit the join, too. btw - The serial field is tagged with @ORM\Id in my User entity.

However, this is just avoiding the problem. I'm still perplexed by how doctrine interprets the query when a join is present.

Edit - real solution found

Thanks to Wilt, after using the query builder and then using the getDQL() method I found the missing detail. The working dql is

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

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.

Jingoism