如何只匹配最近更新的记录在Doctrine？ [英] How to match only last updated records in Doctrine?

问题描述

我正在使用Symfony2和Doctrine开发一个应用程序，并且有一个名为 status 的表，用于存储位置和日期记录，例如：

  ID |书|日期|位置
 --------------------------------------------- --- 
 1 | Book_1 | 2011-08-29 |首页
 2 | Book_1 | 2011-08-30 |办公室
 3 | Book_1 | 2011-09-02 |朋友之家
 4 | Book_2 | 2011-09-02 |办公室
 5 | Book_2 | 2011-09-04 |首页
  

具有最近日期的状态记录表示当前（或最后知道）该书的位置。在上面的例子中，Book_1目前在朋友之家中，Book_2在Home中。

以下代码获取任何记录在某个时候有一个位置Home：

  $ em = $ this-> getEntityManager（）; 
 $ query = $ em-> createQuery（'SELECT s FROM myBookTestBundle：Status s WHERE s.location =：x'） - > setParameter（'x'，'Home'）; 
 $ status = $ query-> getResult（）; 
  

相反，我只想选择那些当前位置匹配的图书家。在上面的例子中，这只是记录ID = 5（Book_2）。

有没有什么方法可以轻松地使用DQL？

非常感谢任何帮助。

谢谢，

Ralph

解决方案

另一个问题是：Do Doctrine2的DQL句柄子选择？

MySQL的查询将是：

  select ID，Book ，`Date`，位置
从状态a 
其中`Date` = 
（从状态组中选择max（`Date`）由Book具有Book = a.Book）
和位置='家'; 
  

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

解决方案

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

这篇关于如何只匹配最近更新的记录在Doctrine？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！