How to compare only the latest updated records in Doctrine?

Question

I am developing an application with Symfony2 and Doctrine and have a status table in which I store location and date files, for example:

ID | Book | Date | Location ------------------------------------------------ 1 | Book_1 | 2011-08-29 | Home 2 | Book_1 | 2011-08-30 | Office 3 | Book_1 | 2011-09-02 | Friend House 4 | Book_2 | 2011-09-02 | Office 5 | Book_2 | 2011-09-04 | Home 

A status record with the most recent date represents the current (or last known) location of this book. In the above example, Book_1 is currently in "Friend House", and Book_2 is in "Home".

The following code retrieves any entries that at some point had a Home location:

 $em = $this->getEntityManager(); $query = $em->createQuery('SELECT s FROM myBookTestBundle:Status s WHERE s.location=:x')->setParameter('x', 'Home'); $status = $query->getResult(); 

Instead, I’d like to select only those books whose current location is the same as Home. In the above example, this will be only the record identifier = 5 (Book_2).

Is there any way to do this with DQL?

Any help is greatly appreciated.

Thanks,
Ralph

Answer 1

Another question: "Can Doctrine2 DQL handle subqueries?".

Query for MySQL:

 select ID,Book,`Date`,Location from Status a where `Date` = (select max(`Date`) from Status group by Book having Book = a.Book) and Location = 'Home'; 

Answer 2

Thanks for your reply. I also found the following resource: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

And I was able to adapt it to the following DQL, which works correctly:

 SELECT s1 FROM myBookTestBundle:Status s1 WHERE s1.Location=:location AND s1.Date=(SELECT MAX(s2.Date) FROM myBookTestBundle:Status s2 WHERE s1.Book=s2.Book) 

However, according to the above article, it is more efficient to use the uncorrelated subquery with the LEFT JOIN. But if I try to write the DQL equivalent, I get an error message. I read somewhere that Doctrine does not support subqueries in FROM / JOIN operations.

Can anyone confirm this? And is there a way to make the above DQL as efficient as possible?

Thanks again,
Ralph