search

Home  >  Q&A  >  body text

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>
P粉514458863P粉514458863506 days ago615

reply all(1)I'll reply

  • P粉618358260

    P粉6183582602023-07-25 10:52:49

    I haven't tested this but I will try something similar

    Shop::whereIn('owner_id', [123])
                ->withCount(['products' => fn($query) => $query->select(['id','type_id'])->whereColumn('products.type_id', '=', 'shops.type_id')])
                ->get()

    So I just added some fields (the ones you need and the ones the app needs to identify the product), but if only the count is needed, I'd try without the ID.

    I assume when you get "products" it will pull all the data and if there are "text" type fields like body/description etc. it will be slow.

    Also, not sure, but you could try using type_id instead of products.type_id since you're already in the products relationship. Also check out optimizing the way you pull your store.

    reply
    0
  • Cancelreply