Hey everyone, I'm seeing duplicate DB queries and hoping to optimize this.
Setup:
OrderGroup has many OrderGroupCartItemOrderGroupCartItem belongs to a Useruser_idIn my controller:
$orderGroup = OrderGroup::query()
->with(['shop', 'host', 'pickupCity', 'cartItems', 'cartItems.user'])
->whereUlid($ulid)
->firstOrFail();
But Laravel Debugbar shows duplicate queries like:
select * from "users" where "users"."id" in (3)
twice.
I'm accessing the user's name in the frontend (Inertia + Vue), so I assume it's trying to resolve cartItems.user more than once.
Question: How can I avoid these duplicate queries and make this more optimal?
Thanks!
Debug the SQL query like this, what it will show?
$sql = OrderGroup::query()->with(['shop', 'host', 'pickupCity', 'cartItems', 'cartItems.user'])->whereUlid($ulid)->toRawSql();
dd($sql);
Are you sure this doubled SQL query is from the controller? Maybe it's a part of authentication or authorization or something else.
@Glukinho That will only show the order groups query SQL. The relations are loaded in separate SQL statements.
Btw, instead of doing ->toRawSql() and dd() separately, you can call ->dd() on the query builder. That'll show the SQL with all bindings.
I'm accessing the user's name in the frontend (Inertia + Vue), so I assume it's trying to resolve cartItems.user more than once.
Queries are executed on the back end. To cause a database query, you'd have to make some kind of API call that triggers one. Reading props on the front end does nothing on its own.
Relations are loaded in separate database queries. When you do OrderGroup::query()->..., only the order groups are loaded in the first query, then a secondary query is executed for each relation, using the foreign keys retrieved in the first query. You may see the same query executed on users multiple times if you have other queries in the controller (or middlewares).
You should show the whole controller method.
@JussiMannisto This is the whole controller method. There are no middlewares active on the route.
public function show(string $ulid)
{
$orderGroup = OrderGroup::query()
->with(['shop', 'host', 'pickupCity', 'cartItems', 'cartItems.user'])
->whereUlid($ulid)
->firstOrFail();
return Inertia::render('order-groups/show', [
'orderGroup' => $orderGroup,
]);
}
The duplicate queries probably come somewhere from those relations. I don't the database structure so I can't say where.
If you have Telescope enabled in development, you can pinpoint where the queries come from by checking the Queries page.
Have you tried temporarily removing individual relations from the ->where() array to see if the duplicates go away? That's the fastest way to see if they're the cause. The other is to check the relations themselves and think about how Laravel has to retrieve them.
Ps. Debugbar always shows the latest request. If you make any API calls from the order group page, Debugbar will display whatever queries were executed during those. You can check the request history tab (on the right side of the bar) to see which request exactly causes those duplicates.
Install Laravel Telescope to see which queries are duplicated. Often, some other part of your view or logic outside this controller is causing the N+1.
You don't actually need Telescope. I checked Debugbar, and you can view where each query is coming from by clicking on them.
Hey everyone,
Just figured out that the duplicate issue was happening because the ordergroup has a host ID (user ID) that matched the one in the cart. That’s not always the case, so it’s actually normal behavior.
Also, just wanted to shout out Telescope, definitely a game changer for me. 🙌
Oh, and learned something new today: automaticallyEagerLoadRelationships. Definitely gonna keep that in my back pocket!
Appreciate all the help, as always! Thanks, guys! 😊
Please or to participate in this conversation.