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: Second page:
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(),
];
}
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
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()
];
}