Combine two eloquent collections into single collection and paginate

Question

I have a news site with an overview of the posts on the homepage. Simple Eloquent collection with pagination, nothing special to be honest (model = Post). Now a new model has been added (Video), which should be added to the overview of the posts on the homepage.

So the goal is to get an eloquent collection of the posts and an eloquent collection of the videos (which are not related to each other), then combine them into one collection and paginate over them.

I've looked around and tried a couple of things but none of them seem to work. Most of the time they say to use $collection->merge($otherCollection) but this doesn't work since a video with id 1 won't be shown if there's a post with the same id, which will be the case.

An other option was to define a relationship, but since they are not related at all, that won't work either.

What I have at the moment, to just get the posts:

if ($highlighted) {
    $posts = Post::where([
        ['status_id', '=', 4],
        ['published_at', '<=', Carbon::now()],
        ['id', '!=', $highlighted->id],
     ])->latest('published_at')->paginate(14);
} else {
     $posts = Post::where([
         ['status_id', '=', 4],
         ['published_at', '<=', Carbon::now()]
     ])->latest('published_at')->paginate(15);
}

Now I want to retrieve the videos in a similar way

$videos = Video::where([
    ['status_id', '=', 4],
    ['published_at', '<=', Carbon::now()]
])->latest('published_at')->paginate(15);

And then combine them into one Eloquent collection and paginate the total instead of the videos and posts seperately

Something like: $collected = $videos + $posts and then ->latest('published_at')->paginate(15)

(I haven't added any examples of what I tried for the sake of the length of this post and since I feel like that is not the way to go, using ->merge(). Also I need an eloquent collection, using the pure DB queries doesn't work, unless that can be converted into an Eloquent collection)

Maybe it's not possible with an eloquent collection and I should be using the query builder?

Any feedback / help is much appreciated. (Feel free to ask for extra code / info)

Have you tried running two queries and combing them into an array? Then just looping through the array on your blade? — FullStackOfPancakes, Mar 28 '19 at 23:11
So here's the problem. `paginate` will run 2 queries. 1) to count the total expected results (and therefore know how many total pages there are and 2) the actual query for the current page (defaulting to the 1st one). That's a problem with union-type queries like yours so your options are (a) manually create the query to count the length of all results and then another manual query with a `union` and limits and then manually make a `LengthAwarePaginator` object based on the results or (b) https://stackoverflow.com/questions/30420505/how-can-i-paginate-a-merged-collection-in-laravel-5 — apokryfos, Mar 29 '19 at 17:15

score 3 · Accepted Answer · answered Apr 01 '19 at 07:43

Thanks to the comments and answer posted here I managed to find a solution that seems to work:

For retrieving the data I did the following:

$posts = Post::where([
    ['status_id', '=', 4],
    ['published_at', '<=', Carbon::now()]
])->get();
$videos = Video::where([
    ['status_id', '=', 4],
    ['published_at', '<=', Carbon::now()]
])->get();

$collected = $videos->union($posts)->sortByDesc('published_at');

As mentioned in a comment on this question: https://stackoverflow.com/a/30421846/4666299 I used https://gist.github.com/simonhamp/549e8821946e2c40a617c85d2cf5af5e for the pagination.

This way I could just then do:

$items = (collect($collected))->paginate(15);

Now I have a single collection I can loop over in Blade. Thanks for all the help!

How can I install https://gist.github.com/simonhamp/549e8821946e2c40a617c85d2cf5af5e ? — Sead Lab, Dec 26 '20 at 16:48

score 1 · Answer 2 · answered Mar 28 '19 at 23:23

1

You can send two different queries, merge your two results in an Illuminate\Support\Collection, then sortBy('published_at') and then use skip(($numPage - 1) * $perPage) and limit($perPage) to do your pagination.

Let me know if it helped

answered Mar 28 '19 at 23:23

Florian Laforgue

301
1
5

Combine two eloquent collections into single collection and paginate

2 Answers2