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?

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`. — Vladimir Baranov, May 14 '15 at 08:51
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. — Vladimir Baranov, May 14 '15 at 10:59
@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. — goat, May 14 '15 at 22:59

score 2 · Accepted Answer · edited May 23 '17 at 11:59

2

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

edited May 23 '17 at 11:59

Community

1
1

answered May 20 '15 at 07:49

Wilt

41,477
12
152
203

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! – goat May 27 '15 at 21:54
@goat 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... – Wilt May 28 '15 at 07:23
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! – goat Jun 02 '15 at 21:10

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

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.

doctrine dql where clause interpretation with associated entity

2 Answers2

EDIT:

Edit - real solution found