如何在Laravel Query Builder中实现带unnest()的PostgreSQL查询

你的目标是实现这条PostgreSQL原生查询，它通过unnest()把数组字段iteration_times拆成虚拟表，再和主表做交叉连接：

SELECT m.channel, m.qwertz as test, iteration.timestamp as iteration 
FROM m_testate as m, unnest(m.iteration_times) WITH ORDINALITY iteration(timestamp, id) 
WHERE m.project_id = 1 LIMIT 10

你之前用fromSub的写法逻辑不对，因为原生SQL是主表和unnest生成的虚拟表做交叉连接，不是从子查询中获取数据再关联。下面是两种正确的实现方式：

方式一：使用Eloquent模型 + fromRaw #

如果Measurement模型对应m_testate表，可以直接用fromRaw还原原生SQL的表结构：

$result = Measurement::select(
        'channel',
        'qwertz as test',
        DB::raw('iteration.timestamp as iteration')
    )
    ->fromRaw('m_testate as m, unnest(m.iteration_times) WITH ORDINALITY iteration(timestamp, id)')
    ->where('project_id', $project_id)
    ->limit(10)
    ->get();

方式二：使用Query Builder + crossJoinRaw #

用crossJoinRaw明确声明交叉连接，语义更清晰：

$result = DB::table('m_testate as m')
    ->select(
        'm.channel',
        'm.qwertz as test',
        'iteration.timestamp as iteration'
    )
    ->crossJoinRaw('unnest(m.iteration_times) WITH ORDINALITY iteration(timestamp, id)')
    ->where('m.project_id', $project_id)
    ->limit(10)
    ->get();

这两种写法都会生成和你原生SQL完全一致的查询语句，满足需求。

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