Laravel: improve performance when having multiple eager loading queries

Question

I'm trying to improve the performance of my laravel application. I was already able to reduce the amount of queries from 68 to 20 by removing lazy loading in my views.

However, using eager loading, still 20 queries remain that do almost the same thing. My code looks like this:

$products = [
        'latest' => Product::with('vehicle', 'brand', 'type', 'photos')->withoutGlobalScope(ProductScope::class)->latest()->take(5)->get(),
        'most_viewed' => Product::with('vehicle', 'brand', 'type', 'photos')->withoutGlobalScope(ProductScope::class)->mostViewed()->take(5)->get(),
        'nearest' => Product::with('vehicle', 'brand', 'type', 'photos')->withoutGlobalScope(ProductScope::class)->nearest($address)->take(5)->get(),
    ];

This results in 15 queries (5 each) since every time the relations will also be queried again. Can these queries be combined in such a way that it might be reduced to 7 queries instead of 15?

What version of Laravel are you using?

Rwd
– Rwd

2019-04-10 11:36:40 +00:00
Commented Apr 10, 2019 at 11:36 — Rwd
– Rwd, Commented Apr 10, 2019 at 11:36
Sorry, forgot to mention, I'm using Laravel 5.8

Lex de Willigen
– Lex de Willigen

2019-04-10 13:43:39 +00:00
Commented Apr 10, 2019 at 13:43 — Lex de Willigen
– Lex de Willigen, Commented Apr 10, 2019 at 13:43

Rwd · Accepted Answer · 2019-04-15 06:19:11Z

1

Since the different collections should be passed by reference you should be able to combine them in to a single Eloquent Collection and then use Lazy Eager Loading:

$products = [
    'latest'      => Product::withoutGlobalScope(ProductScope::class)->latest()->take(5)->get(),
    'most_viewed' => Product::withoutGlobalScope(ProductScope::class)->mostViewed()->take(5)->get(),
    'nearest'     => Product::withoutGlobalScope(ProductScope::class)->nearest($address)->take(5)->get(),
];

//Merge the different Product collections into one for the lazy eager loading 

$collection = new \Illuminate\Database\Eloquent\Collection();

foreach ($products as $items) {
    foreach ($items as $model) {
        $collection->push($model);
    }
}

$collection->load('vehicle', 'brand', 'type', 'photos');

//$products should now contain the different collections but with the additional relationships.

The original collections in the $products array should now have all the relationships loaded but it should have only performed 4 queries instead of 12 for the relationships.

edited Apr 15, 2019 at 6:19

answered Apr 10, 2019 at 11:53

Rwd

35.3k7 gold badges71 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lex de Willigen Over a year ago

Your solutation works partly. I can see the amount of queries are reduced. However, if the same Product appears in multiple collections only the last Product has the relations.

Rwd Over a year ago

@LexdeWilligen Ah yeah, merge uses the key from the model so it was only ever loading one of them into the collection. I updated my answer which should give you what you want.

Lex de Willigen Over a year ago

Hmm you're half right again. All models are loaded nicely, however, for some reason the items in most_viewed and nearest are also added to latest. If I change the order, the items are pushed to the first $products array item.

Rwd Over a year ago

@LexdeWilligen I've updated my answer to just use a new Eloquent Collection instance which should solve the last issue.

Collectives™ on Stack Overflow

Laravel: improve performance when having multiple eager loading queries

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related