如何将含LEFT JOIN LATERAL的PostgreSQL查询转换为Laravel Eloquent查询

实现步骤 #

1. 前置准备 #

如果需要用Eloquent模型调用，提前创建对应两张表的模型即可，纯查询构造器实现可跳过本步：

• NctsInfo 对应 ncts_info 表
• DrugCenter 对应 drug_centers 表

2. 核心查询代码 #

首先引入DB门面：

use Illuminate\Support\Facades\DB;

方式一：纯查询构造器实现（最贴合原SQL逻辑）

// 构造子查询A
$subQueryA = DB::table('drug_centers')
    ->leftJoin(
        DB::raw('LATERAL json_to_recordset(nct_numbers) AS tbl(nct_number text)'),
        DB::raw('true'),
        '=',
        DB::raw('true')
    );

// 主查询
$result = DB::table('ncts_info')
    ->leftJoinSub($subQueryA, 'A', fn($join) => $join->on('A.nct_number', '=', 'ncts_info.nct_id'))
    ->select('ncts_info.nct_id', 'A.agency_model', 'ncts_info.indexation_id')
    ->get();

方式二：结合Eloquent模型实现

如果需要返回NctsInfo模型实例而非数组结果，替换主查询的表调用为模型调用即可：

$result = \App\Models\NctsInfo::leftJoinSub($subQueryA, 'A', fn($join) => $join->on('A.nct_number', '=', 'ncts_info.nct_id'))
    ->select('ncts_info.nct_id', 'A.agency_model', 'ncts_info.indexation_id')
    ->get();

注意事项 #

• 本次用到的LATERAL、json_to_recordset都是PostgreSQL特有的语法，Laravel查询构造器没有做原生封装，因此需要用DB::raw包裹对应的SQL片段。
• 后续如果需要加筛选、分页等逻辑，直接在主查询后链式调用where()、paginate()等方法即可，和普通Eloquent查询用法完全一致。

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