Rails extract values with join associations

Question

I have a rails join table between 2 models superhero and superpower. Now I have 3 different superpower id and I want all the superheroes which have all the selected superpowers

To do that I'm trying to do the following:

matches = Superhero.all
matches = matches.joins(:superpowers).where('superpowers.id = ?', 17).where('superpowers.id = ?', 12).where('superpowers.id = ?', 6)

But this gives me an empty object even though I have superheroes which have all the given superpowers in my join table

The query generated from the above is:

SELECT "superheroes".* FROM "superheroes" INNER JOIN "superheroes_superpowers" ON "superheroes_superpowers"."superhero_id" = "superheroes"."id" INNER JOIN "superpowers" ON "superpowers"."id" = "superheroes_superpowers"."superpower_id" WHERE (superpowers.id = 17) AND (superpowers.id = 17) AND (superpowers.id = 12) AND (superpowers.id = 6)

So weirdly it tries to check for the superpower with id 17 twice (but it shouldn't affect the result I think) and the rest of the query seems to be correct.

Answer 1

try using an in clause

superpowers_ids = [17,12,6]
matches = Superhero.all
matches = matches.joins(:superpowers).where('superpowers.id in (?)', superpowers_ids)