Laravel雄辩/流利地选择每组前1排

Question

我需要确定给定行的signed_out或signed_in日期是否大于一年。有时，当前可能会签出由NULL值指定的领土。如果是这样的话，我需要查看下一个最高的signed_in值，以确定该领土是否在一年内被签出。否则，我需要查看每个组的最后一个signed_in日期来确定这一点。

这些组基于territory_id列。

我的桌子：

-----------------------------------------------------------------
| id | territory_id | signed_out          | signed_in           |
-----------------------------------------------------------------
| 5  | 8            | 2012-12-09 00:00:00 | 2013-02-28 00:00:00 |
| 9  | 8            | 2014-03-23 14:48:17 | 2014-06-11 00:00:00 |
| 11 | 10           | 2014-06-25 11:07:59 | NULL                |
| 14 | 10           | 2012-06-27 21:28:17 | 2012-10-10 00:00:00 |
| 15 | 11           | 2014-06-27 21:28:48 | NULL                |
| 13 | 11           | 2012-09-12 20:23:07 | 2012-12-27 00:00:00 |
| 20 | 11           | 2011-10-30 21:05:29 | 2012-01-05 00:00:00 |
-----------------------------------------------------------------

预期结果: Rows 9、11、15

我看过许多group by和greatest-n-per-group标记的mysql问题的例子，但是我无法理解如何构建这个查询，特别是在Laravel中使用流利或流利的。如果我必须使用原始查询，我想这很好。

我试过的是：

TerritoriesSignInOut::where('signed_in', '<=', Carbon::now()->subYear())
                    ->orWhere(function($query)
                    {
                        $query->where('signed_out', '<=', Carbon::now()->subYear())
                              ->whereNull('signed_in');
                    })
                    ->groupBy('territory_id')
                    ->orderBy('signed_in', 'DESC')
                    ->get();

但这不能执行正确的子查询选择，这就是我认为该语句的问题所在。

任何帮助都是非常感谢的，因为我已经为此绞尽脑汁好几个小时了。

Answer 1

我认为这个查询应该适用于您，尽管您可能需要先测试它。你提供的数据似乎对我有用。

SELECT id, GREATEST(signed_in, COALESCE(signed_out, signed_in)) AS `my_date`
FROM (SELECT * FROM `territories` ORDER BY signed_in DESC) territories
GROUP BY territory_id
HAVING my_date <= DATE_SUB(NOW(), INTERVAL 1 YEAR);

要在Fluent中构建查询，这应该可以工作。它使用了相当多的原始数据，但我想如果您没有绑定任何数据，就没有注入的可能。

DB::table(DB::raw('(SELECT * FROM `territories` ORDER BY signed_in DESC) territories'))
    ->select(DB::raw('id, GREATEST(signed_in, COALESCE(signed_out, signed_in)) AS `my_date`'))
    ->groupBy('territory_id')
    ->having('my_date', '<=', DB::raw('DATE_SUB(NOW(), INTERVAL 1 YEAR)'))
    ->get();

您必须更改表的名称，我只是使用域，因为它很容易，但它显然不是一个地区表。