如何只匹配最后更新的文件?

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:

我正在开发一个具有Symfony2和Doctrine的应用程序，并有一个名为status的表，在其中存放位置和日期簿，例如:

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".

最近日期的状态记录表示该图书的当前(或最后的已知)位置。在上面的示例中，Book_1当前位于“Friend's House”中，Book_2位于“Home”中。

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

下面的代码获取任何在某一时刻具有“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).

相反，我只想选择那些当前位置匹配“Home”的书籍。在上面的示例中，这将仅是记录ID = 5 (Book_2)。

Is there any way to do this easily with DQL?

有什么方法可以轻松地使用DQL实现这一点吗?

Any help is greatly appreciated.

非常感谢您的帮助。

Thanks,
Ralph

谢谢你,拉尔夫

2 个解决方案

#1

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

另一个问题是:“教条2的DQL能处理子选择吗?”

The query for MySQL would be:

对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';

#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

谢谢你的回复。我还找到了以下资源:http://dev.sqmyl.com/doc/refman/5.0 / en/exam-maxim- column-group-row.html

And I was able to adapt this into the following DQL that works properly:

我能够将它应用到以下的DQL中，这是正确的:

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.

但是，根据上面的文章，使用一个不相关的子查询和一个左连接是更有效的。但是如果我尝试编写与DQL等价的代码，就会得到一个错误。我在某个地方读到，Doctrine不支持FROM/JOIN语句中的子查询。

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

谁能证实这一点?并且，是否有办法使上面的DQL尽可能地高效?

Thanks again,
Ralph

再次感谢,拉尔夫

#1