Laravel - N+1, multiple relations

Question

I want to optimize my application. There are a lot of similar relations in multiple models but I can't find a way to decrease amount of queries.

Example:

class User extends Model {
    $with = ['images', 'products', 'orders'];
    //relations goes here
}

class Product extends Model {
    $with = ['images'];
    //relations goes here
}

class Order extends Model {
    $with = ['products'];
    //relations goes here
}

In this case, when I call User::all() those are queries that are being called:

• select from users...
• select from images...
• select from products...
• select from images...
• select from orders...
• select from products...
• select from images

Since products are related to both orders and users query is called twice. Images query is called triple. This is just a simplified example but in real scenario I have like 180 queries that are calling select from like 10-20 tables and I'm sure it could be optimized but I have no idea how. I'm aware that I can simply remove $with and use ->with() specifying which models I actually need to load. The problem is the project is really big, there is over 1000 endpoints and it'd take months to verify each of them, not to mention other modules that are gathering data as well.

I know that the order of eager loading would make a difference because when loading user->images laravel doesn't know yet what are product->images IDs. And thus not all tables can have just one query but if it was loaded this order it should be fine:

• select from users
• select from orders
• select from products (combining user->products and user->orders->products)
• select from images (combining user->images and user->products->images and user->orders->products->images)

That way it could go down from 7 queries to 4 queries. And in real life scenario I could go down like 80-90%.

I could even write some function that analyzes all relations and returns optimized order.

Answer 1

I recommend you install the Laravel N+1 Query Detector package to help detect and address the N+1 query problem in their Laravel application. This package monitors your queries in real-time, while you develop your application and notify you when you should add eager loading (N+1 queries).