Laravel - Union + Paginate at the same time?
Solution 1
You're right, pagination cause problem. Right now, you can create a view and query the view instead of the actual tables, or create your Paginator
manually:
$page = Input::get('page', 1);
$paginate = 5;
$recipes = DB::table("recipes")->select("id", "title", "user_id", "description", "created_at")
->where("user_id", "=", $id);
$items = DB::table("posts")->select("id", "title", "user_id", "content", "created_at")
->where("user_id", "=", $id)
->union($recipes)
->get();
$slice = array_slice($items->toArray(), $paginate * ($page - 1), $paginate);
$result = Paginator::make($slice, count($items), $paginate);
return View::make('yourView',compact('result'));
Solution 2
I faced this kind of issue already. I found a thread also not about pagination
but about unions
.
Please see this link : Sorting UNION queries with Laravel 4.1
@Mohamed Azher has shared a nice trick and it works on my issue.
$query = $query1->union($query2);
$querySql = $query->toSql();
$query = DB::table(DB::raw("($querySql order by foo desc) as a"))->mergeBindings($query);
This creates an sql like below:
select * from (
(select a as foo from foo)
union
(select b as foo from bar)
) as a order by foo desc;
And you can already utilize Laravel's paginate
same as usual like $query->paginate(5)
. (but you have to fork it a bit to fit to your problem)
Solution 3
Reiterating jdme's answer with a more elegant method from Illuminate\Database\Query\Builder
.
$recipes = DB::table("recipes") ..
$items = DB::table("posts")->union($recipes) ..
$query = DB::query()
->fromSub($items, "some_query_name");
// Let's paginate!
$query->paginate(5);
I hope this helps!
Solution 4
The accepted answer works great for Query Builder.
But here's my approach for Laravel Eloquent Builder.
Assume that we're referring to same Model
$q1 = Model::createByMe(); // some condition
$q2 = Model::createByMyFriend(); // another condition
$q2->union($q1);
$querySql = $q2->toSql();
$query = Model::from(DB::raw("($querySql) as a"))->select('a.*')->addBinding($q2->getBindings());
$paginated_data = $query->paginate();
I'm using Laravel 5.6
Solution 5
order by
$page = Input::get('page', 1);
$paginate = 5;
$recipes = DB::table("recipes")->select("id", "title", "user_id", "description", "created_at")
->where("user_id", "=", $id);
$items = DB::table("posts")->select("id", "title", "user_id", "content", "created_at")
->where("user_id", "=", $id)
->union($recipes)
->orderBy('created_at','desc')
->get();
$slice = array_slice($items, $paginate * ($page - 1), $paginate);
$result = Paginator::make($slice, count($items), $paginate);
return View::make('yourView',compact('result'))->with( 'result', $result );
View page :
@foreach($result as $data)
{{ $data->your_column_name;}}
@endforeach
{{$result->links();}} //for pagination
its help to more peoples.. because nobody cant understand show data in view page union with pagination and orderby .. thank u
Related videos on Youtube
Lior
Updated on June 18, 2020Comments
-
Lior about 4 years
Brief:
I am trying to union 2 tables
recipes
andposts
then add->paginate(5)
to the queries.But for some reason I get this error:
Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL: (select count(*) as aggregate from
posts
Code:
$recipes = DB::table("recipes")->select("id", "title", "user_id", "description", "created_at") ->where("user_id", "=", $id); $items = DB::table("posts")->select("id", "title", "user_id", "content", "created_at") ->where("user_id", "=", $id) ->union($recipes) ->paginate(5)->get();
Am i doing something wrong?
Without
->paginate(5)
the query works fine.-
rmondesilva about 5 yearsEncountered this problem. Try to use
->simplePaginate(n)
instead.
-
-
Lior almost 10 yearsThanks! how would you add orderBy to the union query? i tried to add it after the ->union but the "order by" ends up within one of the parentheses (version 4.2.8).
-
Alexandru Eftimie over 7 yearsLoading all the rows in php and then slicing them is not very efficient, especially for large tables.
-
mickmackusa about 7 yearsCode-only answers do very little to educate SO readers. Your answer may or may not be a correct answer, but it is in the moderation queue after being marked as "low-quality". Please take a moment to improve your answer with an explanation.
-
Qh0stM4N about 6 yearsgreat answer, I am build spesific group by (exlude null and zero index) in merged 2 table union, this way working paginate and ordering.
-
Martin Tonev over 5 yearsThe solution is right but for small amounts of data.
-
Taras Chernata over 4 yearsWow Johnny! thank you so much, it works for me fine, after a day of struggling with similar issue)) Does it mean it will work fine if we have a lot of data, right??
-
cawecoy about 4 yearsI can't hold myself to say... Superb!
-
Bogdan Burym over 3 yearsExactly the best answer, and the only right one.
-
Ngoc Nam over 3 yearsIn order to get a large amout of data, it is a bad solution. Because
->get
will get all data, then slice the array. -
sangam about 3 yearsUseful when I don't want to miss my model as it has appended props.
-
Zain Farooq over 2 yearsIt is not actually the solution but just virtual pagination to make the users happy.
-
Abraham Putra Prakasa over 2 yearscan't believe this is accepted answer lol