Sequel model filtering many-to-many relationships

Question

The question is best demonstrated through an example. I have 2 Sequel models, Artists and Genres, connected via a many-to-many relationship through a joiner table:

class Artist < Sequel::Model
    many_to_many :genres,
    left_key: :artist_id,
    right_key: :genre_id,
    join_table: :artists_genres
end

class Genres < Sequel::Model
    many_to_many :artists,
    left_key: :genre_id,
    right_key: :artist_id,
    join_table: :artists_genres
end

And the joiner table looks very simple:

CREATE TABLE `artists_genres` (`artist_id` integer, `genre_id` integer)

What I want to do is to filter all artists belonging to particular genres. This works well when using a single genre:

some_genre = Genre[1]
Artists.dataset.where(:genres => some_genre).all

My problem is trying to get all artists who belong to a collection of genres:

multiple_genres = Genre.where(:id => [1,2,4])
Artists.dataset.where(:genres => multiple_genres).all

This returns artists who belong to any of the 3 genres (1, 2, or 4), instead of artists belonging to ALL 3 genres.

What is the correct way to query this through the model, or rewrite as a dataset join call to select the right rows?

Answer 1

As @mudasobwa mentioned in comments, you can append multiple where methods

multiple_genres = Genre.where(id: [1, 2, 4])
result = Artists.dataset
multiple_genres.each { |g| result = result.where(genres: g) }
result.all