如何在Laravel Eloquent中实现同表子查询及SQL转换

需求逻辑 #

查询actions表中满足以下全部规则的记录：

• recurring_pattern字段非空
• parent_id字段非空
• 所属parent_id分组下，due_date为所有晚于当前时间的记录中的最小值

对应原生SQL如下：

SELECT *
FROM actions A1
WHERE A1.recurring_pattern IS NOT NULL AND
    A1.parent_id IS NOT NULL AND
    A1.due_date = (
        SELECT MIN(due_date)
        FROM actions A2
        WHERE A2.parent_id = A1.parent_id AND 
            due_date > NOW()
    )

实现代码 #

首先确保你已创建绑定actions表的Action模型，使用以下Eloquent查询即可得到和原生SQL完全一致的结果：

<?php

use App\Models\Action;
use Illuminate\Support\Facades\DB;

$actions = Action::whereNotNull('recurring_pattern')
    ->whereNotNull('parent_id')
    ->where('due_date', function ($subQuery) {
        $subQuery->select(DB::raw('MIN(due_date)'))
            ->from('actions AS A2')
            ->whereColumn('A2.parent_id', 'actions.parent_id')
            ->where('due_date', '>', DB::raw('NOW()'));
    })
    ->get();

写法说明 #

• 前两个whereNotNull链式调用，直接对应原生SQL中两个字段非空的筛选条件
• 第三个where方法传入闭包构造同表子查询，生成的SQL结构和原生写法完全一致，不会额外产生性能损耗
• whereColumn方法用于对比两个表的字段值，避免框架将字段名误解析为普通字符串参数
• 如果你更偏好Laravel的便捷写法，可以用框架自带的now()辅助函数替代原生NOW()调用，框架会自动适配不同数据库的时间语法，简化后的写法如下：

$actions = Action::whereNotNull('recurring_pattern')
    ->whereNotNull('parent_id')
    ->where('due_date', function ($subQuery) {
        $subQuery->selectRaw('MIN(due_date)')
            ->from('actions')
            ->whereColumn('parent_id', 'actions.parent_id')
            ->where('due_date', '>', now());
    })
    ->get();

提示：如果你的Action模型配置了软删除、全局Scope等特性，子查询建议直接读取模型绑定的表名，自动继承所有全局约束，避免漏筛数据，示例：

$subQuery->selectRaw('MIN(due_date)')
    ->from( (new Action())->getTable() )
    // 后续筛选条件保持不变

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