Is it a good idea to query once and split the collection of results? And if so, how to do it based on relationship?

Hello everyone,

I'm wondering if there is benefits into querying once and then splitting the results, and if so, how to efficiently do it?

For example, let's say that in the application there are 2 models: User and Post. a User hasMany posts. The Post model has a published_at property, which can be a day in the future.

If I want to show a table with users and their posts that will be published in the next 3 days and another table with the ones where it's already published, I can do the following:

$usersWithPublishedPosts = User::withWhereHas(
    'posts',
    fn ($query) => $query->whereDate('published_at', '<=', now())
)->get();

$usersWithUpcomingPosts = User::withWhereHas(
    'posts',
    fn ($query) => $query
        ->whereDate('published_at', '>', now())
        ->whereDate('published_at', '<=', now()->addDays(3)->endOfDay())
)->get();

I'm wondering if I could simplify this to use only 1 Query, but have the results separated in 2 different variables, like I have above. The query itself could be much simpler, like so:

$usersWithPosts = User::withWhereHas(
    'posts',
    fn ($query) => $query->whereDate('published_at', '<=', now()->addDays(3)->endOfDay())
)->get();

But I'm not quite sure on how to split that into 2 variables depending on a property of the relationship. I looked into partition(), but doesn't seem like it works for what it's used. The same user can have a post that is already published and upcoming, so I'd want that user in both variables, but with the correct posts.

Thank you everyone!

LaryAI

3 years ago

Level 58

It is possible to query once and split the collection of results based on the relationship. You can use the mapWithKeys() method to achieve this. The mapWithKeys() method will iterate through the collection and return a new collection with the keys and values that you specify.

In your case, you can use the mapWithKeys() method to iterate through the collection and return a new collection with the users and their posts that are already published and upcoming.

Below is an example of how you can use the mapWithKeys() method to achieve this:

$usersWithPosts = User::withWhereHas(
    'posts',
    fn ($query) =&gt; $query-&gt;whereDate('published_at', '<=', now()-&gt;addDays(3)->endOfDay())
)->get();

$usersWithPublishedPosts = $usersWithPosts->mapWithKeys(function ($user) {
    return [
        $user->id => $user->posts->filter(function ($post) {
            return $post->published_at <= now();
        })
    ];
});

$usersWithUpcomingPosts = $usersWithPosts->mapWithKeys(function ($user) {
    return [
        $user->id => $user->posts->filter(function ($post) {
            return $post->published_at > now();
        })
    ];
});

The $usersWithPublishedPosts collection will contain the users and their posts that are already published, and the $usersWithUpcomingPosts collection will contain the users and their posts that are upcoming.

MohamedTammam

3 years ago

Level 51

I would do that with two queries.

I don't see an issue here. And if the data was too big and you need to paginate for each result, you will need two queries anyway.

Please or to participate in this conversation.