Question 391297 in Laravel Question posted in
The official Laravel Documentation can be found here.

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

2

Answers


  1. 0

    Please provide SHOW CREATE TABLE posts;

    The problem is that this is in the second subquery, which otherwise sees only v:

      and (`nv`.`id` < 1)

    On to other issues…

    Each sub-select does an ORDER BY but no LIMIT. Recommend adding LIMIT 3 to them.

    The outer select does a LIMIT without an ORDER BY. Add LIMIT 3.

    For pagination to, say, page 11, change to LIMIT 33 for inner queries and LIMIT 30, 3 for the outer query.

    More discussion:
    https://mysql.rjweb.org/doc.php/pagination#pagination_and_union

    Login or Signup to reply.
  2. 0

    You’re making 3 queries here; one to get a list of post IDs, one to get posts that are in that list and another to get ones that aren’t. For starters, mashing those last two together with UNION will leave you unable to tell which posts have been viewed and which have not in your controller/view. But you say in comments you only want this information for ordering, showing the unviewed posts first.

    So, you can reduce this to a single (ish) query by doing the viewed check as a subquery and passing it to the ORDER BY clause.

    $posts = DB::table('posts')
    ->select(['posts.*', 'post_images.image', 'users.username', 'profiles.image as profileimage'])
    ->selectRaw(
        '(EXISTS(SELECT * FROM viewed_posts WHERE user_id = ? AND post_id = posts.id)) AS viewedstate',
        [Auth::id()]
    )
    ->join('post_images', 'posts.id', '=', 'post_images.post_id')
    ->join('users', 'posts.user_id', '=', 'users.id')
    ->join('profiles', 'posts.user_id', '=', 'profiles.user_id')
    ->orderBy('viewedstate')
    ->orderByDesc('posts.id');

    An Eloquent (with a capital E) solution would put you back into multiple queries on the database side and return a nested object, but would make your controller code much more concise. It would involve setting up a scope on your Post model that adds the subquery column and then sorts by it:

    namespace AppModels;
 
use IlluminateDatabaseEloquentBuilder;
use IlluminateDatabaseEloquentModel;
 
class Post extends Model
{
    public function scopeUnreadFirst(Builder $query): void
    {
        $query->selectRaw(
            '(EXISTS(SELECT * FROM viewed_posts WHERE user_id = ? AND post_id = posts.id)) AS viewedstate',
            [Auth::id()]
        )->orderBy('viewedstate');
    }
}

    And assuming you already have expected relationships, the query would look something like this:

    $posts = Post::unreadFirst()
    ->with("images")
    ->with(
        "user",
        fn($q) => $q->select("id", "username")->with("profile:id,user_id,image")
    )
    ->orderByDesc("id");

    This is all untested, but should work in theory and at least give you a starting point to work with.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search