phpmysqllaravelpaginationlaravel-query-builder

Laravel - Error with union and cursorPaginate


I am trying to do union with DB query builder because with models is not working the union with paginate/cursorPaginate.

I have this but give an SQL error because Laravel is not building the right query (maybe my fault, I don't known where to change)

The error is:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'nv.id' in 'where clause'
(Connection: mysql, SQL: 
  (
    select `nv`.*, `pi`.`image`, `unv`.`username`, `p`.`image` as `profileimage`
    from `posts` as `nv`
    inner join `post_images` as `pi` on `nv`.`id` = `pi`.`post_id`
    inner join `users` as `unv` on `nv`.`user_id` = `unv`.`id`
    inner join `profiles` as `p` on `nv`.`user_id` = `p`.`user_id`
    where `nv`.`id` not in (1, 2, 3, 4, 5, 6, 8)
    and (`nv`.`id` < 1)
    order by `nv`.`id` desc
  ) union (
    select `v`.*, `piv`.`image`, `uv`.`username`, `pr`.`image` as `profileimage`
    from `posts` as `v`
    inner join `post_images` as `piv` on `v`.`id` = `piv`.`post_id`
    inner join `users` as `uv` on `v`.`user_id` = `uv`.`id`
    inner join `profiles` as `pr` on `v`.`user_id` = `pr`.`user_id`
    where `v`.`id` in (1, 2, 3, 4, 5, 6, 8)
    and (`nv`.`id` < 1)
    order by `v`.`id` desc
  )
  limit 3
)"

And that is right! I don't know WHY Laravel uses the nv.id alias where the correct is v.id... The function code is:

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(),
    ];

}

Edit: If I edit the 2nd query to match the same alias as the first (nv.) works but the second page give me 0 results: First page: enter image description here Second page: enter image description here

Edit 2: Trying with 'order by id in (...)' (for remove the union) gives me a meaningless error:

    public function getPosts()
    {
        $viewedPostsIds = ViewedPost::select('post_id')->where('user_id', Auth::id())->get()->pluck('post_id')->all();
        $posts = DB::table('posts as p')->selectRaw('p.*,max(i.image) as image, u.username, max(pro.image) as profileimage')
            //   ->whereNotIn('nv.id', $viewedPostsIds)->orderByDesc('nv.id')
            ->join('post_images as i', 'p.id', '=', 'i.post_id')
            ->join('users as u', 'p.user_id', '=', 'u.id')
            ->join('profiles as pro', 'p.user_id', '=', 'pro.user_id')
            ->orderByRaw("p.id in (" . implode(',', $viewedPostsIds) . ") DESC")
            ->groupByRaw('p.id');

        $cursorData = $posts->cursorPaginate(2);
        return [
            'data' => $cursorData->items(),//->groupBy('p.id')->flatten(1),
            'next_page' => $cursorData->nextPageUrl(),
            'has_more_pages' => $cursorData->hasMorePages(),
        ];
    }

The error: enter image description here

Edit 3: Without the group by SAME error:

        $viewedPostsIds = ViewedPost::select('post_id')->where('user_id', Auth::id())->get()->pluck('post_id')->all();
        $posts = DB::table('posts as p')->selectRaw('p.*,i.image as image, u.username, pro.image as profileimage')
            //   ->whereNotIn('nv.id', $viewedPostsIds)->orderByDesc('nv.id')
            ->join('post_images as i', 'p.id', '=', 'i.post_id')
            ->join('users as u', 'p.user_id', '=', 'u.id')
            ->join('profiles as pro', 'p.user_id', '=', 'pro.user_id')
            ->whereRaw('i.id = (select min(id) from post_images where post_id = p.id)')
            ->orderByRaw("p.id in (" . implode(',', $viewedPostsIds) . ") DESC");
            

        $cursorData = $posts->cursorPaginate(2);
        return [
            'data' => $cursorData->items(),//->groupBy('p.id')->flatten(1),
            'next_page' => $cursorData->nextPageUrl(),
            'has_more_pages' => $cursorData->hasMorePages(),
        ];

Edit 4: Tried the @miken32 DB Query builder solution but gives me an error with the cursorPaginator:

"SQLSTATE[HY093]: Invalid parameter number (Connection: mysql, SQL: select `posts`.*, `post_images`.`image`, `users`.`username`, `profiles`.`image` as `profileimage`, (EXISTS(SELECT * FROM viewed_posts WHERE user_id = 1 AND post_id = posts.id)) AS viewedstate from `posts` inner join `post_images` on `posts`.`id` = `post_images`.`post_id` inner join `users` on `posts`.`user_id` = `users`.`id` inner join `profiles` on `posts`.`user_id` = `profiles`.`user_id` where ((EXISTS(SELECT * FROM viewed_posts WHERE user_id = 0 AND post_id = posts.id)) > 0 or ((EXISTS(SELECT * FROM viewed_posts WHERE user_id = 5 AND post_id = posts.id)) = ? and (`posts`.`id` < ?))) order by `viewedstate` asc, `posts`.`id` desc limit 3)"

Without the paginator works great, but I need paginate


Solution

  • Cursor pagination doesn't work with arbitrary queries. There are some limitations and your query is not compatible with them. It means that you should use the regular paginate() method instead.

    I would do something like this:

    public function getPosts()
    {
        $posts = DB::table('posts as p')
            ->selectRaw('p.*, i.image as image, u.username, pro.image as profileimage')
            ->selectRaw('(exists (select 1 from viewed_posts where user_id = ? and post_id = p.id)) as viewed', [Auth::id()])
            ->join('post_images as i', 'p.id', '=', 'i.post_id')
            ->join('users as u', 'p.user_id', '=', 'u.id')
            ->join('profiles as pro', 'p.user_id', '=', 'pro.user_id')
            ->whereRaw('i.id = (select min(id) from post_images where post_id = p.id)')
            ->orderBy('viewed')
            ->orderByDesc('p.id');
    
        $paginator = $posts->paginate(2);
        return [
            'data' => $paginator->items(),
            'next_page' => $paginator->nextPageUrl(),
            'has_more_page' => $paginator->hasMorePages()
        ];
    }