Help Optimising Query

Remove the () from categories here in your view

$product->categories->first()->slug

You want to work on the categories property (which is a collection) that was already retrieved, not the relationship method which will execute a new query.

presumably it is this line. $categories->with('media')->enabled()->first();

try this:

$categories->with('media.enabled')->enabled->first();

@Cronix your solution worked to fix the blade stuff, thanks for that.

I am so lost on this query though.. I'm wondering if I need the closure at all now...

Let me explain what I need, maybe it will help formulate a query.

Product model using the following query scopes: scopeEnabled and scopePromote

with Media (on the Product model) for reference it's spatie/laravel-medialibrary

with Categories modal using the following query scopes scopeEnabled but only the first() Category that the product falls in.

with Media (on the Category model)

and then only for 6 random products (currently) using take(6)

I'm wondering if I need the closure at all now...

You do, because of the constraints you want to use on the categories

with Categories modal using the following query scopes scopeEnabled but only the first() Category that the product falls in.

Ok that makes sense.

So to only return one Category per Product where should the first() be?

When i put it inside the closure, only 1 of the 6 returned Products has a Category, the other 5 are empty. If I remove it, all 6 Products return 2 Categories each (which would be correct).

        $products = Product::enabled()->promote()->with('media')
                            ->with(['categories' => function ($categories) {
                                 $categories->enabled()->first()->with('media');
                            }])->inRandomOrder()->take(6)->get();

I could leave it without the first() and do that in blade, as I will cache the results anyway, but would be nice to bottom it out.

Do it in the view. It would be simplest. You can't really limit the number of results the relationship returns due to the way eager loading in laravel works. The only other way that I can think of would be to perform a query for each category individually, which would be inefficient. It can be done with some workarounds, but they're ugly. https://github.com/laravel/framework/issues/18014

Take a simple eloquent relationship query.

User::with('profile')->where('active', 1)->get();

That performs 2 queries.

// get the user
select * from users where active = 1

// it then gathers the id's that were returned from the first query.
// Let's say it returned rows with id's of 1, 2 and 3.

// get the profile for the retrieved users
select * from profiles where user_id in (1,2,3);

if you were to add a constraint to the profile relationship to limit the number of results,

User::with(['profile' => function($query) {
    $query->limit(1);
}])
->where('active', 1)
->get();

it would change the 2nd query to

select * from profiles where user_id in (1,2,3) limit 1

that limit 1 would only return a single result for all users, even though there are 3 users. Does that make sense?

That makes sense, and the issue at git makes for an interesting read too.

I'm going to take your suggestion and do it in blade, since i'll be caching anyway.

The fact that my code creates 4 sql queries seems fine in this case too.

Thanks for all your help!