Laravel 8中unionAll()结合orderBy无法正常工作的问题

问题原因 #

在使用UNION ALL时，数据库会忽略子查询中的ORDER BY子句（除非子查询搭配LIMIT使用）。你当前的代码中，两个子查询各自添加了orderBy，这些排序并不会对最终合并后的结果产生影响，反而可能干扰外层排序的执行逻辑。

解决方案 #

方案1：仅对合并后的结果排序（推荐） #

移除两个子查询中的orderBy，只在unionAll之后添加全局排序，确保对合并后的完整数据集生效：

$events = Event::select('id', 'event_name as name', 'publish_at', DB::raw("'EVENT' as type"))
    ->customer($id);

$news = News::select('id', 'name', 'publish_at', DB::raw("'NEWS' as type"))
    ->customer($id)
    ->unionAll($events)
    ->orderBy('publish_at', 'asc');

生成的SQL会变成：

(
    select "id", "name", "publish_at", 'NEWS' as type
    from "news" where exists 
        (
            select * from "customers"
            inner join "news_customers" 
            on "customers"."id" = "news_customers"."customer_id"
            where "news"."id" = "news_customers"."news_id" 
            and "news_customers"."customer_id" = 3
        ) 
)    
union all    
(
    select "id", "event_name" as "name", "publish_at", 'EVENT' as type
    from "events" where exists 
    (
        select * from "customers" 
        inner join "event_customers" 
        on "customers"."id" = "event_customers"."customer_id" 
        where "events"."id" = "event_customers"."event_id" 
        and "event_customers"."customer_id" = 3
    )
) 
order by "publish_at" asc

方案2：子查询排序后合并（需配合LIMIT） #

如果业务需要先对子查询结果排序，再取指定数量的数据进行合并，必须给子查询添加limit（否则数据库会忽略子查询的排序）：

$events = Event::select('id', 'event_name as name', 'publish_at', DB::raw("'EVENT' as type"))
    ->customer($id)
    ->orderBy('publish_at', 'asc')
    ->limit(100); // 必须添加LIMIT才能让子查询排序生效

$news = News::select('id', 'name', 'publish_at', DB::raw("'NEWS' as type"))
    ->customer($id)
    ->orderBy('publish_at', 'asc')
    ->limit(100)
    ->unionAll($events)
    ->orderBy('publish_at', 'asc');

关键说明 #

• 数据库规范中，UNION/UNION ALL操作会将多个结果集合并，子查询的排序仅在搭配LIMIT时有效，用于限制子查询返回的条目顺序和数量。
• 全局排序必须放在unionAll之后，才能对合并后的完整结果集生效。

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