在Laravel ORM中能否基于多列字段实现查询结果去重？

distinct()失效原因 #

Laravel的distinct()方法不接受字段列表作为参数，该方法仅会对查询时SELECT的所有字段整体做去重判断。由于你查询的重复条目包含不同的id、created_at、updated_at值，SELECT全字段的场景下distinct自然不会判定为重复条目，因此达不到预期去重效果。

该需求完全可以仅通过SQL实现，以下是两种常用实现方案： #

方案1：窗口函数方案（支持MySQL 8.0+/PostgreSQL/SQLite 3.25+版本） #

核心逻辑是按6个discipline字段分区，每个分区内按更新时间倒序排序，取每个分区排序第一的条目即为最新的无重复数据。
原生SQL示例：

SELECT * FROM (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY discipline_one, discipline_two, discipline_three, discipline_four, discipline_five, discipline_six
            ORDER BY updated_at DESC, id DESC
        ) AS rn
    FROM result_saves
    WHERE username = ?
) t WHERE rn = 1

对应的Laravel ORM实现：

$waybackWhen = \App\Models\ResultSave::query()
    ->selectRaw('*, ROW_NUMBER() OVER (PARTITION BY discipline_one, discipline_two, discipline_three, discipline_four, discipline_five, discipline_six ORDER BY updated_at DESC, id DESC) as rn')
    ->where('username', $result->username)
    ->having('rn', '=', 1)
    ->get();

方案2：低版本数据库兼容方案 #

如果数据库版本不支持窗口函数，可以用分组取最新值关联的方式实现：
原生SQL示例：

SELECT r.* FROM result_saves r
INNER JOIN (
    SELECT 
        discipline_one, discipline_two, discipline_three, discipline_four, discipline_five, discipline_six,
        MAX(updated_at) as latest_updated, MAX(id) as latest_id
    FROM result_saves
    WHERE username = ?
    GROUP BY discipline_one, discipline_two, discipline_three, discipline_four, discipline_five, discipline_six
) t ON 
    r.discipline_one = t.discipline_one
    AND r.discipline_two = t.discipline_two
    AND r.discipline_three = t.discipline_three
    AND r.discipline_four = t.discipline_four
    AND r.discipline_five = t.discipline_five
    AND r.discipline_six = t.discipline_six
    AND r.updated_at = t.latest_updated
    AND r.id = t.latest_id
WHERE r.username = ?

对应的Laravel ORM实现：

$subquery = \App\Models\ResultSave::query()
    ->selectRaw('discipline_one, discipline_two, discipline_three, discipline_four, discipline_five, discipline_six, MAX(updated_at) as latest_updated, MAX(id) as latest_id')
    ->where('username', $result->username)
    ->groupBy('discipline_one', 'discipline_two', 'discipline_three', 'discipline_four', 'discipline_five', 'discipline_six');

$waybackWhen = \App\Models\ResultSave::query()
    ->joinSub($subquery, 't', function ($join) {
        $join->on('result_saves.discipline_one', '=', 't.discipline_one')
            ->on('result_saves.discipline_two', '=', 't.discipline_two')
            ->on('result_saves.discipline_three', '=', 't.discipline_three')
            ->on('result_saves.discipline_four', '=', 't.discipline_four')
            ->on('result_saves.discipline_five', '=', 't.discipline_five')
            ->on('result_saves.discipline_six', '=', 't.discipline_six')
            ->on('result_saves.updated_at', '=', 't.latest_updated')
            ->on('result_saves.id', '=', 't.latest_id');
    })
    ->where('result_saves.username', $result->username)
    ->get('result_saves.*');

内容的提问来源于stack exchange，提问作者Jaquarh