php - Laravel Eloquent /流利每组选择前1行

我需要确定给定行是否有一个大于一年的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 |
-----------------------------------------------------------------

预期结果：行9，11，15
我看过很多标记mysql问题的例子，但是我无法理解如何构建这个查询，特别是在使用雄辩或流畅的方法构建这个查询时。如果我必须使用原始查询，我想没关系。
我试过的：

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();

但这并没有执行正确的子查询select，我认为这是该语句的问题所在。
任何帮助都是非常感谢的，因为我已经为此绞尽脑汁好几个小时了。

最佳答案

我相信这个查询应该对您有用，尽管您可能想先测试它。你提供的数据似乎对我有用。

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构建查询，这应该是可行的。它使用了相当多的raw，但我想如果您没有绑定任何数据，就没有注入的可能。

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();

不过，您必须更改表的名称，我只是使用了territories，因为它很简单，但显然不是territories表。