mysql - 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|

对于每张发票，我想检索最后一次状态更改。因此，结果应该包含 ID 为 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 分组并使用 max(change_date)，我将检索最年轻的日期，但其他字段的字段值不是从包含该组中最年轻日期的那些记录中获取的。

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

挑战 #2 是尽可能使用 CakePHP 的方法实现查询。

挑战 #3 是将结果过滤为属于当前用户的那些记录。所以如果当前用户的 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 的 find 中使用 JOIN 子句实现。

---

建议算法的 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;