Count the number of Laravel and Mysql related models through intermediate tables using additional conditions.
<p>I have a MySQL query (built using Laravel Eloquent's eager loading and the withCount function) that is having some performance issues when dealing with large data sets, is there any way to improve the query below? </p><p>I need to get all the stores and count the number of products related to the store (associated through an intermediate table), but there is an additional condition that the type_id of the store is equal to the type_id of the product. I think this second condition is causing the query not to use the correct index. </p><p>There is an intermediate table between the two models. </p><p> store(id, type_id, owner_id) product(id, type_id) store product(shop_id, product_id) </p><p> I have indexes on all foreign keys, also There is a composite index on shop_product(shop_id, product_id). </p><p>So my query is this: </p><p><br /></p>
<pre class="brush:php;toolbar:false;">select
shops.*,
(
select
count(*)
from
products
inner join shop_products on
products.id = shop_products.product_id
where
shops.id = shop_products.shop_id
and products.type_id = shops.type_id)
from
shops
where
shops.owner_id in (?)</pre>
<p>is it possible that this query could be optimized somehow, maybe not using this laravel's withCount whereColumn query?</p>
<pre class="brush:php;toolbar:false;">... Shop::withCount(['products' => fn($query) => $query->whereColumn('products. type_id', '=', 'shops.type_id')]);</pre>
<p>The complete query is like this:</p>
<pre class="brush:php;toolbar:false;">Shop::whereIn('owner_id', [123])
->withCount(['products' => fn($query) => $query->whereColumn('products.type_id', '=', 'shops.type_id')])
->get()</pre>
<p>Do I need to add a combined index on store(id, type_id) and product(id, type_id)? </p>