Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

AR's avatar
AR
OP
8 years ago
Level 7

Eager loading still has 11 queries

I have a page that shows a shop with all its relations that are:

  1. completed and incomplete products (status 1 means completed)
  2. products photos (product_id in products_photos table)
  3. likes count for each product (morphmany)
  4. followers count and button for the shop (many to many with dedicated model ShopFollower )

with that in mind I have the following query in my ShopsController:

public function show($shop)
{
    $shop = Shop::findByUrl($shop);

    $shop->load(['products' => function ($query) {
        $query->orderBy('created_at', 'desc');
    }, 'followers', 'products.photos', 'products.likes']);


    $completeProducts = $shop->products->filter(function ($product) {
        return $product->status == 1;
        });
    $incompleteProducts = $shop->products->filter(function ($product) {
        return $product->status == 0;
    });

    return view('shops.show', compact('shop', 'completeProducts', 'incompleteProducts'));
}

and here is my view that I show the shop and its list of products (I removed most of the html and just copied the calls and echos):

@section('content')


<!-- Here is the shop info-->

<img src="{{ $shop->logo }}" style="
width:128px;
height:128px;
border-radius:50%;">


{{ $shop->name }}


{{ '@' . $shop->url }}


<follow-button
:is-followed="{{ json_encode($shop->isFollowed) }}"
shop-url="{{ $shop->url }}"
hide="{{ Auth::check() && Auth::user()->is($shop->user) }}"
logged-in="{{ Auth::check() }}"
></follow-button>

<shop-stats
:products-count-prop="{{ $shop->products->count() }}"
:followers-count-prop="{{ $shop->followers->count() }}"
></shop-stats>

<span class="">
    {{ App::isLocale('en') ? $shop->location_en : $shop->location_ar }}
 </span>


{{ $shop->description }}

<a href="{{ $shop->website }}" target="_blank">Website</a>

<a href="{{ 'https://www.instagram.com/' . $shop->instagram }}" target="_blank">
    Instagram
</a>
                  

@can ('createProduct', $shop)
     <addproduct
    url="{{ $shop->path() }}/products"
    lang="{{ App::getLocale() }}">
        {{ csrf_field() }}
    </addproduct>
@endcan


</section>


<!-- Here is the list of products-->


@foreach ($completeProducts as $product)
    

    <h3 class="marginless">
        {{ App::isLocale('en') ? $product->name_en : $product->name_ar }}
    </h3>

   
    <productlink
    url="{{ $product->path }}"
     product-id="{{ $product->hashedid }}"
    ></productlink>



    @if ($product->likescount)
        <i class="fa fa-1x fa-heart" aria-hidden="true"></i>
        {{ $product->likescount }}
    @endif



    {{ App::isLocale('en') ? $shop->currency->code_en : $shop->currency->code_ar }}
    {{ $product->formatted_price }}


    @if ($product->photos->first()['tn_url'])
        <img src="{{ $product->photos->first()['tn_url'] }}" class="img-responsive">
    @endif


    
@endforeach



@endsection

With this code I have 11 queries running As the following:

select * from `shops` where `url` = 'kris.brook' limit 1
select * from `products` where `products`.`shop_id` in ('1') order by `created_at` desc
select * from `product_photos` where `product_photos`.`product_id` in ('1', '2', '3', '5', '6', '8')
select * from `likes` where `likes`.`liked_id` in ('1', '2', '3', '5', '6', '8') and `likes`.`liked_type` = 'App\Product'
select * from `shop_followers` where `shop_followers`.`shop_id` in ('1')
select * from `users` where `id` = '2' limit 1
select exists(select * from `shop_followers` where `shop_followers`.`shop_id` = '1' and `shop_followers`.`shop_id` is not null and `user_id` = '2') as `exists`
select * from `users` where `users`.`id` = '1' limit 1
select * from `cities` where `cities`.`id` = '1' limit 1
select * from `countries` where `countries`.`id` = '1' limit 1
select * from `currencies` where `currencies`.`id` = '1' limit 1

Is that Ok or I need to use other methods like the query builder and join the tables?

Thanks in advance

0 likes
8 replies
rumm.an's avatar

You are lazy eager loading them, what you can do is eager load the models using with() method with a where query, like so.

public function show($shop)
{
    $shop = Shop::where('url', $shop)->with(['products' => function ($query) {
        $query->orderBy('created_at', 'desc');
    }, 'followers', 'products.photos', 'products.likes'])->first();


    $completeProducts = $shop->products->filter(function ($product) {
        return $product->status == 1;
        });
    $incompleteProducts = $shop->products->filter(function ($product) {
        return $product->status == 0;
    });

    return view('shops.show', compact('shop', 'completeProducts', 'incompleteProducts'));
}

I hope this would reduce the number of queries.

1 like
tykus's avatar

Whether you are eager-loading / lazy-loading makes no difference in this context. You have that many queries because you need that many queries - look at the queries you have... they are almost all different tables.

The only apparent improvement you could make (because there are two almost identical queries) is to eager load the followers.user assuming that is the relationship, otherwise you've already optimised your querying.

tykus's avatar

One other thing, if you have only two states for product status, then you can use the Collection partition method to divide your shop products into complete and incomplete collections:

list($completeProducts, $incompleteProducts) = $shop->products
    ->partition(function ($product) {
        return $product->status == 1;
    });

If status is boolean, then return $product->status

1 like
rumm.an's avatar

I thought eager loading uses single query with SQL joins?

AR's avatar
AR
OP
8 years ago
Level 7

@rumm.an Thanks for your reply. I have used your shorter with method and the code is more clean. AFAIK The eager loading does not "sql join" the queries. It will do a call for each model but it will prevent running additional queries after you eager load a model.

@tykus Thanks for the reply. I have used your suggestion with partition as I just have two status to work with (for now). For that extra user calls I could not reduce it to one query. the two queries are:

select * from `users` where `id` = '2' limit 1
select * from `users` where `users`.`id` = '1' limit 1

The first one is where I call Auth::check() and the second one is where I check if the user is the shop owner with Auth::user()->is($shop->user) to show the "add product" button and hide the "follow button".

AR's avatar
AR
OP
8 years ago
Level 7

@tykus I can change

Auth::user()->is($shop->user)

to

Auth::id() == $shop->user_id

I will reduce the extra user query call but is that a safe/OK?

tykus's avatar
tykus
8 years ago
Best Answer
Level 104

Eager-loading does not result in joins, Laravel does the work to put the relations with the correct parent.

The is method compares the model keys (usually you will use the primary key, but not always), the model's database table and the model's database connection; but in many circumstances, this Auth::id() == $shop->user_id is perfectly okay

rumm.an's avatar

Hey @tykus, How Eager laoding is benefecial? or is it really benefecial at all? Since, Queries are same then what is the point of eager loading relations?

Please or to participate in this conversation.