Eloquent get all items where the relation matches alle ids

I have three tables: movies, genres and genre_taggable.

A movie can have one or many genres:

public function genres()
    {
        return $this->morphToMany(Genre::class, 'genre_taggable', 'genre_taggables', 'genre_id', 'genre_taggable_id');
    }

I defined a scope where a user can select multiple genres and get all movies which have one of the selected genres.

public function scopeGenres($query, $genres)
    {
        return $query->whereHas('genres', function($q) use ($genres){
            $q->whereIn('genres.genre_id', $genres);
        });
    }

Now I wanted to extend the functionality and add the option to show only the movies which have exactly the selected genres, so I thought I just need to concat the genre ids with an AND, like

return $query->whereHas('genres', function($q) use ($genres){
                foreach ($genres as $genre) {
                    $q->where('genres.genre_id', $genre);
                }
            });

but this doesn't work. I guess that i'm missing something but what? Appreciate any help

bobbybouwmann

5 years ago

Level 88

What doesn't work for you? You don't get expected results? Are you sure the data is correct?

You can see the query by doing Movie::genres()->toSql(). Then you can check if everything is ok there.

I would personally expect that this would work as well, but it could be that $genres is empty here for some kind of reason

bobbybouwmann

5 years ago

Level 88

I think you have to match on the genre table instead. In your relationship, you named this table genre_taggables

ronon

5 years ago

Level 9

@bobbybouwmann the data is correct. I assume it's since every genre has it's own entry in the genres table and it's own entry in the genre_taggables table. So the AND cannot work since it's checking against data in the same row not in the same table. So I guess I need a query which somehow checks against multiple rows

The query currently looks like this:

SELECT
	*
FROM
	`movies`
WHERE
	EXISTS (
		SELECT
			*
		FROM
			`genres`
			INNER JOIN `genre_taggables` ON `genres`.`genre_id` = `genre_taggables`.`genre_taggable_id`
		WHERE
			`movies`.`movie_id` = `genre_taggables`.`genre_id`
			AND `genres`.`genre_id` in(?, ?))
	ORDER BY
		`title` DESC

Please or to participate in this conversation.