How to match only last updated records in Doctrine?
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,
RalphThe 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';
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
精彩评论