Laravel 12中带表达式排序的UNION联合查询报错求助

PostgreSQL 对 UNION 后的 ORDER BY 子句有严格限制，不允许直接使用表达式或函数，只能引用结果集的列名。根据报错提示，有两种可行的解决方式：

方式一：在每个子查询中添加排序用的计算列 #

把用于排序的表达式（判断是否精确匹配John）作为列添加到两个查询中，之后直接通过这个列排序：

$members = Members::selectRaw(
    "member_code as theCode, full_name as theName, 1 as isMember, CASE WHEN full_name ilike ? THEN 1 ELSE 0 END as sort_order",
    ['%john%']
)->where('full_name', 'ilike', '%john%');

$nonMembers = NonMembers::selectRaw(
    "member_code as theCode, full_name as theName, 0 as isMember, CASE WHEN full_name ilike ? THEN 1 ELSE 0 END as sort_order",
    ['%john%']
)->where('full_name', 'ilike', '%john%')
->union($members)
->orderBy('sort_order', 'desc')
->orderBy('theName', 'asc')
->get();

这种方式让 UNION 后的结果集包含 sort_order 列，PostgreSQL 可以直接识别该列并用于排序，同时满足精确匹配的记录排在最前，其余按姓名升序的需求。

方式二：将 UNION 结果作为子查询，在外层排序 #

把两个表的 UNION 查询作为子查询，在外层查询中使用表达式排序，绕开 PostgreSQL 对 UNION 直接排序的限制：

// 先定义UNION查询
$unionQuery = NonMembers::selectRaw("member_code as theCode, full_name as theName, 0 as isMember")
    ->where('full_name', 'ilike', '%john%')
    ->union(
        Members::selectRaw("member_code as theCode, full_name as theName, 1 as isMember")
            ->where('full_name', 'ilike', '%john%')
    );

// 外层查询使用表达式排序
$results = DB::table($unionQuery, 'combined')
    ->orderByRaw("theName ilike ? desc, theName asc", ['%john%'])
    ->get();

这种方式将 UNION 结果包装成临时表，外层查询不受 UNION 的排序限制，可以直接使用原有的表达式完成排序逻辑。

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