Can I improve this Query? (USER, Album, Pictures, TAGS)

anyone?

Three nested wherehas cant be good. Cant you just go with joins ?

$query = User::select('users.*')
  ->join('albums', ...)
  ->join('pictures', ...)
  ->join('tags', ...);

foreach($tag_ids as $tag_id) $query->where('tag_id', '=', $tag_id);

$users = $query->get();

@luddinus Yes, you can.

I suppose MySQL?

// First add this relation to the  User model
public function pictures()
{
  return $this->hasManyThrough('Picture', 'Album');
}

// then use this:
$tags = [1,2,3];

User::whereHas('pictures.tags', function ($q) use ($tags) {
  $q->wherein('tags.id', $tags)->selectRaw('count(distinct tags.id)');
}, '>=', count($tags) )->get();

@JarekTkaczyk That won't work. In addition, I want to get those users with pictures which have ALL the tags requested, not one of them.

@pmall I haven't try yet. The thing is I don't know which SQL I have to create (Eloquent really does a lot of magic...)

$query = User::select('users.*')
  ->join('albums', 'users.id', '=', 'albums.user_id')
  ->join('pictures', 'albums.id', '=', 'pictures.album_id')
  ->join('tags', 'pictures.id', '=', 'picture_tag.picture_id');

foreach($tag_ids as $tag_id) $query->where('picture_tag.tag_id', '=', $tag_id);

$users = $query->get();

Here I just assumed you have conventional foreign keys and you have a many to many relationship between pictures and tags with a conventional pivot table. Rename the tables / keys according to your schema.

@luddinus You haven't tried that, have you?

Yes it will work, and yes it will search for all the tags not just any of them. That was not not-tested-but-you-can-try- like suggestion.

@JarekTkacyk I'm getting this error

Column not found: 1054 Unknown column 'tags.id' in 'field list' (SQL: select * from users where (select count(distinct tags.id) from pictures inner join albums on albums.id = pictures.album_id where users.id = albums.user_id and tags.id in (1, 2, 3)) >= 3)

If I get this working, the users would have "eager loaded" the pictures with ONLY those pictures with those tags, or I would have those users but loaded with ALL the pictures they have?

@luddinus whereHas('pictures.tags') check the edit - my bad, sorry for that.

If you want to load only matching pictures, then you need to duplicate that last condition:

with(['pictures' => function ($q) use ($tags) {
  $q->whereHas('tags', function ($q) use ($tags) {
    $q->wherein('tags.id', $tags)->selectRaw('count(distinct tags.id)');
  }, '>=', count($tags));
}]);

So depending on what you want to achieve, it might be easier to query Picture model instead.

@JarekTkaczyk thx, finally this is my code:

$tags_ids = [1, 10];
$filter = function($pictures) use($tags_ids) {
    $pictures->whereHas('tags', function($tags) use($tags_ids) {
        $tags->whereIn('tags.id', $tags_ids)->selectRaw('count(distinct tags.id)'); 
    }, '>=', count($tags_ids));
};
$users= User::with(['pictures' => $filter])
    ->whereHas('pictures', $filter)
    ->get();