How to match only last updated records in Doctrine?

Question

I am developing an application with Symfony2 and Doctrine, and have a table called status where I store the location and date books, such as:

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's House
4   | Book_2      | 2011-09-02 | Office
5   | Book_2      | 2011-09-04 | Home

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

The following code gets any records that at some point had a location of "Home":

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

Instead, I would like to select only those books whose current location matches "Home". In the above example, that would only be record ID = 5 (Book_2).

Is there any way to do this easily with DQL?

Any help is greatly appreciated.

Thanks,
Ralph

Answer 1

The other question is: "Can Doctrine2's DQL handle subselects?".

The query for MySQL would be:

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 this into the following DQL that works properly:

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 an uncorrelated sub-query with a LEFT JOIN. But if I try to write the DQL equivalent, I get an error. I read somewhere that Doctrine does not support sub-queries in FROM/JOIN statements.

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

Thanks again,
Ralph