CakePHP (子)查询以获取每个组的最年轻记录

Question

在一个项目中，我管理有状态的发票，这种状态在他们的一生中都会改变。状态更改保存在另一个数据库表中，与此类似：

|id|invoice_id|user_id|old_status_id|new_status_id|change_date        |
-----------------------------------------------------------------------
| 1|         1|      1|            1|            3|2013-11-11 12:00:00|
| 2|         1|      2|            3|            5|2013-11-11 12:30:00|
| 3|         2|      3|            1|            2|2013-11-10 08:00:00|
| 4|         1|      1|            5|            6|2013-11-11 13:10:00|
| 5|         2|      2|            2|            5|2013-11-10 09:00:00|

对于每一张发票，我想检索最后一次状态更改。因此，结果应该包含ids 4和5的记录。

|id|invoice_id|user_id|old_status_id|new_status_id|change_date        |
-----------------------------------------------------------------------
| 4|         1|      1|            5|            6|2013-11-11 13:10:00|
| 5|         2|      2|            2|            5|2013-11-10 09:00:00|

如果我按invoice_id分组并使用最大值(Change_date)，我将检索最年轻的日期，但其他字段的字段值不会从包含组中最年轻日期的记录中提取。

这对我来说是第一次挑战。

如果可能的话，第二个挑战是使用CakePHP的方法实现查询。

第三项挑战是将结果过滤到属于当前用户的记录。因此，如果当前用户有id 1，则结果是

|id|invoice_id|user_id|old_status_id|new_status_id|change_date        |
-----------------------------------------------------------------------
| 4|         1|      1|            5|            6|2013-11-11 13:10:00|

如果他或她有用户id 2，则结果是

|id|invoice_id|user_id|old_status_id|new_status_id|change_date        |
-----------------------------------------------------------------------
| 5|         2|      2|            2|            5|2013-11-10 09:00:00|

对于id 3的用户，结果将为空。

换句话说，我做的是，而不是，希望找到用户所做的所有最新的更改，而不管他是否是最后一个做出更改的人。相反，我希望找到所有发票更改，其中该用户是目前为止最后一个谁作出了更改。其动机是，我希望使用户能够撤消他的更改，这只有在他之后没有其他用户执行其他更改时才有可能。

Answer 1

万一有人需要答案

严格集中于：

我想找到所有发票上的更改，到目前为止，那个用户是最后一个修改的人

将SQL写成

SELECT foo.*
FROM foo
LEFT JOIN foo AS after_foo
  ON foo.invoice_id = after_foo.invoice_id
    AND foo.change_date < after_foo.change_date
WHERE after_foo.id IS NULL
  AND foo.user_id = 1;

在Cakephp的JOIN中使用find子句实现。

建议的算法的SQL如下所示：

SELECT foo.*
FROM foo
JOIN (SELECT invoice_id, MAX(change_date) AS most_recent
      FROM foo
      GROUP BY invoice_id) AS recently
  ON recently.invoice_id = foo.invoice_id
    AND recently.most_recent = foo.change_date
WHERE foo.user_id = 1;