Laravel中Union与cursorPaginate联用报错及方案尝试遇阻

可行解决方案 #

方案一：修正Union分页逻辑（子查询包裹） #

针对Laravel对Union查询的游标分页别名处理bug，将Union结果作为子查询，外层统一处理分页：

public function getPosts()
{
    $viewedPostsIds = ViewedPost::where('user_id', Auth::id())->pluck('post_id')->toArray();
    
    // 未读帖子查询：统一字段+标记未读状态
    $postsNotViewed = DB::table('posts as p')
        ->select([
            'p.id', 'p.user_id', 'p.content', // 明确列出需要的posts字段
            'pi.image', 
            'u.username', 
            'pro.image as profileimage',
            DB::raw('0 as viewed')
        ])
        ->whereNotIn('p.id', $viewedPostsIds)
        ->join('post_images as pi', 'p.id', '=', 'pi.post_id')
        ->join('users as u', 'p.user_id', '=', 'u.id')
        ->join('profiles as pro', 'p.user_id', '=', 'pro.user_id')
        ->whereRaw('pi.id = (select min(id) from post_images where post_id = p.id)'); // 取单帖首图
    
    // 已读帖子查询：同未读字段结构+标记已读状态
    $postsViewed = DB::table('posts as p')
        ->select([
            'p.id', 'p.user_id', 'p.content',
            'pi.image', 
            'u.username', 
            'pro.image as profileimage',
            DB::raw('1 as viewed')
        ])
        ->whereIn('p.id', $viewedPostsIds)
        ->join('post_images as pi', 'p.id', '=', 'pi.post_id')
        ->join('users as u', 'p.user_id', '=', 'u.id')
        ->join('profiles as pro', 'p.user_id', '=', 'pro.user_id')
        ->whereRaw('pi.id = (select min(id) from post_images where post_id = p.id)');
    
    // 合并查询并包裹为子查询，外层做分页
    $combinedPosts = DB::table(
        $postsNotViewed->union($postsViewed)->orderBy('viewed', 'asc')->orderBy('id', 'desc'),
        'combined'
    )->select('combined.*');
    
    $cursorData = $combinedPosts->cursorPaginate(2);
    
    return [
        'data' => $cursorData->items(),
        'next_page' => $cursorData->nextPageUrl(),
        'has_more_pages' => $cursorData->hasMorePages(),
    ];
}

方案二：使用Exists标记状态（修复参数绑定） #

用Exists替代Union，通过mergeBindings解决游标分页的参数不匹配问题：

public function getPosts()
{
    $userId = Auth::id();
    // 构建已读状态子查询
    $viewedSubquery = ViewedPost::whereColumn('post_id', 'posts.id')->where('user_id', $userId);
    
    $posts = DB::table('posts')
        ->select([
            'posts.*',
            'post_images.image',
            'users.username',
            'profiles.image as profileimage',
            DB::raw('EXISTS(' . $viewedSubquery->toSql() . ') as viewed')
        ])
        ->join('post_images', function ($join) {
            $join->on('posts.id', '=', 'post_images.post_id')
                 ->whereRaw('post_images.id = (select min(id) from post_images where post_id = posts.id)');
        })
        ->join('users', 'posts.user_id', '=', 'users.id')
        ->join('profiles', 'posts.user_id', '=', 'profiles.user_id')
        ->orderBy('viewed', 'asc') // 未读优先
        ->orderByDesc('posts.id')
        ->mergeBindings($viewedSubquery); // 绑定子查询参数
    
    $cursorData = $posts->cursorPaginate(2);
    
    return [
        'data' => $cursorData->items(),
        'next_page' => $cursorData->nextPageUrl(),
        'has_more_pages' => $cursorData->hasMorePages(),
    ];
}

问题背景与错误分析 #

1. Union查询的别名错误 #

Laravel游标分页会将分页条件（如id < ?）错误应用到第二个Union子查询，引用第一个子查询的别名（nv.id），导致字段不存在错误：

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'nv.id' in 'where clause'

对应原始代码：

public function getPosts()
{
    $viewedPostsIds = ViewedPost::select('post_id')->where('user_id', Auth::id())->get();
    $postsNotViewed = DB::table('posts as nv')
        ->select(['nv.*', 'pi.image', 'unv.username', 'p.image as profileimage'])
        ->whereNotIn('nv.id', $viewedPostsIds)
        ->orderByDesc('nv.id')
        ->join('post_images as pi', 'nv.id', '=', 'pi.post_id')
        ->join('users as unv', 'nv.user_id', '=', 'unv.id')
        ->join('profiles as p', 'nv.user_id', '=', 'p.user_id');

    $postsViewed = DB::table('posts as v')
        ->select(['v.*', 'piv.image', 'uv.username', 'pr.image as profileimage'])
        ->whereIn('v.id', $viewedPostsIds)
        ->orderByDesc('v.id')
        ->join('post_images as piv', 'v.id', '=', 'piv.post_id')
        ->join('users as uv', 'v.user_id', '=', 'uv.id')
        ->join('profiles as pr', 'v.user_id', '=', 'pr.user_id');

    $cursorData = $postsNotViewed->union($postsViewed)->cursorPaginate(2);

    return [
        'data' => $cursorData->groupBy('post_id')->flatten(1),
        'next_page' => $cursorData->nextPageUrl(),
        'has_more_page' => $cursorData->hasMorePages(),
    ];
}

2. 统一别名后的分页失效 #

将两个子查询别名统一为nv后，首次查询正常，但第二页返回0条结果——因为分页条件无法正确区分两个子查询的结果集。

3. orderByRaw的SQL风险与错误 #

手动拼接orderByRaw("p.id in (...)")存在SQL注入风险，且MySQL中IN返回布尔值，游标分页无法正确处理该排序后的游标逻辑。

4. Exists方案的参数绑定问题 #

原始Exists方案未合并子查询的绑定参数，导致Laravel生成的SQL中参数数量不匹配，触发HY093错误。

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