Level 88
It sounds like you need to rethink your application and database structure. I would recommend you to take a look at polymorphic relations.
Documentation: https://laravel.com/docs/5.2/eloquent-relationships#polymorphic-relations
Jul 23, 2016
4
Level 30
Unions, Pagination, and Sorting
So it looks like unions and paginations cannot be done with Laravel? Please let me know if I am wrong.
Problem: I am trying to display a list of Picture and Video objects ordered by 'created_at'
Temporary solution: Get Collection of all Pictures, Collection of all Videos and do a "push" on them and sortByDesc('created_at') Then I do a ->forPage($page, $perPage) and that paginates nicely!
However, as you can see, that means with EVERY request I am retrieving EVERY record, putting it into memory and filtering.
I tried thinking of solutions like: Picture::paginate(10) and Video::paginate(10)
But because of the sortByDesc('created_at') this cannot be done. Why? because, say, I am on page 2. That means I get Pictures 11-20 and Videos 11-20, BUT because of the created_at sorting, I may need to display Videos 0-20 BEFORE ANY pictures are displayed. But this Video::paginate(10) will not even retrieve Videos 1-10 (since I am on page two).
Any solutions would be greatly appreciated!
Level 43
Try to do raw SQL statement to do union on tables, or just DB facade.
Level 75
Level 30
I have a solution.
$pictures= $this->pictures()
->select('id', 'created_at')
->addSelect(DB::raw('"Pictures" as model'));
$videos= $this->videos()
->select('id', 'created_at')
->addSelect(DB::raw('"Videos" as model'));
$items = $videos->unionAll($pictures)
->orderBy('created_at')
->skip(($page - 1) * $perPage)
->take($perPage)
->get();
$items->transform(function ($item, $key) {
if ($item->model == "Picture") {
return Picture::find($item->id);
} else {
return Video::find($item->id);
}
});
We only select the 'id' and 'created_at' from the two models. This is because "unionAll" needs to have the same amount of columns in each. We then can order by created_at and use skip() and take() to do some pagination.
I put the addSelect() in there so that I can differentiate what Model it is (since id = 1 could be either)
Then, I take the collection (made up of id, created_at, and model) and cycle through them and transform it to an Actual model.
I am open to more "Eloquent" ways. ha.
Please or to participate in this conversation.