Complex "with" joining and column selection

@piljac1 hmm, ok. Well, using your suggestion, this is what I came up with

User::with(['profile' => function (Builder $query) {
  $query->select('user_profiles.id', 'user_profiles.name', 'user_profiles.avatar');
}])->with('collections.game')
  ->whereHas('collections', function (Builder $query) {
    return $query->whereHas('game', function (Builder $query) {
      return $query->where('status', '<>', 'denied');
    });
  })
  ->select('users.id', 'users.name')
  ->get();

Running that in tinker gives me the following error TypeError: {closure}(): Argument #1 ($query) must be of type Illuminate\Database\Eloquent\Builder, Illuminate\Database\Eloquent\Relations\HasOne given, called in /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php on line 666

I've tried adding the profile columns to that last select (right before the ::get()) and that threw a different error. Full disclosure: I didn't expect that to work but at this point, I'm throwing stuff at the wall and seeing what sticks.

One thing I noticed is that when I user ::toSql() instead of ::get(), I'm noticing that it's building all of this as subqueries and not as a join. It seems to me that would make it a lot more difficult to get it to pull out specific columns in the related tables and return only those. Well, unless there's a "N + 1" thing going on with lazy loading on that account. And if lazy loading is occurring then I would think that you wouldn't be able to tell the associated models which columns to include in the above statement...?

@_christoph You have two errors. One of which is because I forgot to specify something important.

1. The $query instance of an eager load closure is not an Illuminate\Database\Eloquent\Builder instance (that's why they don't explicitly type it in the docs).
2. When selecting in an eager load closure, you need to select the id column and the foreign column related to the given relation you're trying to eager load, so the framework can properly match the data. So in your case, you probably have to select user_id as well as the columns you want.

As for the toSql() part where you're saying it's building subqueries, it's because of the whereHas calls (not the eager loading which is treated separately in other queries). It uses subqueries to avoid multiple issues that would affect the developers "quality of life", such as column names ambiguity and "SELECT list is not in GROUP BY clause" errors or duplicate entries returned that would happen if they would use joins.

@_christoph Also, if you don't need to specify anything else other than limiting the selected columns, you can use the short syntax.

->with('profile:id,user_id,name,avatar')

@piljac1 Thank you very much for your help and insight. I'll definitely have to read up on the short syntax in the docs. I guess I missed it the first time around...? It definitely helps a lot!

I do have a question about point 1 above, however. If it's not an instance of Builder then what is it?

@_christoph Yeah you probably missed it, because it is there :) As for the instance, it is an instance of a subclass of the Relation class. So if your relation is hasMany, you will get an instance of HasMany (which extends Relation), which has an underlying query builder.

@piljac1 I looked again and I still couldn't find it. Do you have a link to the docs page where that is discussed?

Noted. I'll have to dig into that a bit as well. Thanks again for all your help!

@_christoph Here's the docs section talking about the short syntax.

@piljac1 Here's another, related, question for you. So with your help, this is the code that I'm using to get the information I'm looking for

User::query()
->with('profile:id,user_id,name,avatar')
->with('collections.game')
->whereHas('collections', function (Builder $query) {
  return $query->whereHas('game', function (Builder $query) {
    return $query->where('status', '<>', 'denied');
  });
})
->select(['id', 'name'])
->get()

And that works great. So collection has a relationship with another model, Token. That relationship is set up as such:

  public function tokens(): BelongsToMany
  {
    return $this->belongsToMany(Token::class)
      ->withPivot(['owned', 'wanted']);
  }

which goes through a pivot table with collection_id and token_id. So what I'm trying to do is make it so that only those collections are returned which actually have tokens. I tried variations of the following:

User::query()
->with('profile:id,user_id,name,avatar')
->with('collections.game')
->whereHas('collections', function (Builder $query) {
  return $query->whereHas('game', function (Builder $query) {
    return $query->where('status', '<>', 'denied');
  });
})
->withCount('collections.tokens')
->having('tokens_count', '>', '0')
->select(['id', 'name'])
->get()

But every variation I try just ends up giving me an error. Is something like the above possible?

@_christoph Do you still want all users, but only load collections that have a token? Or do you want to exclude users that don't have at least a collection with a token? Also, what is the error you're getting? Also, do you absolutely need the count or no?

If you want to reject users that don't have at least a collection with a token, and you don't need the count, the following should work:

User::query()
    ->with('profile:id,user_id,name,avatar')
    ->with('collections.game')
    ->has('collections.tokens')
    ->whereHas('collections', function (Builder $query) {
        return $query->whereHas('game', function (Builder $query) {
            return $query->where('status', '<>', 'denied');
        });
    })
    ->select(['id', 'name'])
    ->get();

@piljac1 I tried so many different things I can't remember all the errors, I'm sorry.

"Do you still want all users, but only load collections that have a token?"

Yes, this. I do want all users but I want to exclude any related collections if they don't have at least 1 token. E.g., I don't include my son's "Pokemon" game box as part of his collection if he doesn't own any cards. But I would include his "Yu Gi Oh" collection because he does own cards. And no, I don't need a count. At this point, I don't care about the individual tokens.

You've been incredibly helpful and I'm very appreciative of that.

@_christoph How about:

User::query()
    ->with([
        'profile:id,user_id,name,avatar',
        'collections' => function ($query) {
            $query->with(['game', 'tokens'])
                ->has('tokens');
        },
    ])
    ->with('collections.game')
    ->has('collections.tokens')
    ->whereHas('collections', function (Builder $query) {
        return $query->whereHas('game', function (Builder $query) {
            return $query->where('status', '<>', 'denied');
        });
    })
    ->select(['id', 'name'])
    ->get();

@piljac1 What's the difference between this part of the code

        'collections' => function ($query) {
            $query->with(['game', 'tokens'])
                ->has('tokens');
        },

and this part

->has('collections.tokens')

They seem to be saying the same thing...?

The above didn't seem to work in that it was still returning a collection for a user that did not have tokens. But when I dumped out the ::toSql() the query that it dumped out looked correct, so I'm going to have to do a bit of digging to see what might be going on

"select `id`, `name` from `users` where exists (select * from `collections` where `users`.`id` = `collections`.`user_id` and exists (select * from `tokens` inner join `collection_token` on `tokens`.`id` = `collection_token`.`token_id` where `collections`.`id` = `collection_token`.`collection_id`)) and exists (select * from `collections` where `users`.`id` = `collections`.`user_id` and exists (select * from `games` where `collections`.`game_id` = `games`.`id` and `status` <> ?))"

@_christoph Oops! I was in my bed on my cellphone and very sleepy when I posted my last reply. I forgot to take out these two lines:

->with('collections.game')
->has('collections.tokens')

So now it should be:

User::query()
    ->with([
        'profile:id,user_id,name,avatar',
        'collections' => function ($query) {
            $query->with(['game', 'tokens'])
                ->has('tokens');
        },
    ])
    ->whereHas('collections', function (Builder $query) {
        return $query->whereHas('game', function (Builder $query) {
            return $query->where('status', '<>', 'denied');
        });
    })
    ->select(['id', 'name'])
    ->get();

However, even with the last query, I don't know how you could get users that don't have tokens, because of the ->has('collections.tokens').

P.S. ->has('collections.tokens') and the ->has('tokens') in the with are not the same. The first one excludes users that don't have collections with tokens. The second one only eager loads collections that have related tokens.

@piljac1 Ok, it looks like that works but I'm kind of confused now exactly how it's working. Running ::toSql() on that yields

=> "select `id`, `name` from `users` where exists (select * from `collections` where `users`.`id` = `collections`.`user_id` and exists (select * from `games` where `collections`.`game_id` = `games`.`id` and `status` <> ?))"

Is the ::with() and ::whereHas() a case of lazy loading? I don't see any conditions in the SQL that sets up or use those constraints. Actually, combing through the result set a bit more slowly a second time I am seeing games in there having a status of denied.

@piljac1 Ok, so going back to review the SQL from my previous message, I think I figured out what's going on there. It's doing 3 things

1. It's selecting all the collections that have tokens
2. It's selecting all the games that do not have a denied status
3. It's selecting all the users that have IDs contained within each of those sub-selects

Because steps 1 and 2 are distinct, 1 can have games that have been denied and 2 can have collections with no tokens.

The only inner join I see there in the query is where it's joining collections, tokens, and collection_token (the pivot table). That makes sense. But I guess the ::with and the ::has are always just setting up the sub-selects. So given the complexity of what I'm trying to do it seems like I may just have to use raw SQL. Is that the sense you are getting as well?

@_christoph It's not exactly that.

Basically your current eloquent builder only returns users that have at least one collection having a game with a status other than denied. As for the eager loaded models, it will load every collections that have tokens (even if their game is denied).

If I'm guessing right, what you actually want is all users (including the ones that don't have any collections), but only load collections that have tokens and a game that isn't denied. If that's what you want, use this query:

User::query()
    ->with([
        'profile:id,user_id,name,avatar',
        'collections' => function ($query) {
            $query->with(['game', 'tokens'])
                ->whereHas('game', function (Builder $query) {
                    $query->where('status', '<>', 'denied');
                })
                ->has('tokens');
        },
    ])
    ->select(['id', 'name'])
    ->get();

Everything that is eager loaded won't show in your toSql() because it does separate queries.

@piljac1 that appears to be working. Thank you very much.

I've learned a great deal from you throughout this thread. You've helped me understand Eloquent a bit more (I still need to do a bit more reading about the callback function that is the value of the with key, however) and I've been able to apply some of the things I've learned here elsewhere in my code.

Thank you again!