Can I query a record with multiple associated records that fit certain criteria?

Question

I have two tables, Show, Character. Each Show has_many Characters.

class Show < ActiveRecord::Base
  has_many :characters

class Character < ActiveRecord::Base
  belongs_to :show

What I want to do is return the results of a Show that is associated with multiple Characters that fit certain criteria.

For example, I want to be able to return a list of Shows that have as characters both Batman and Robin. Not Batman OR Robin, Batman AND Robin.

So the query should be something like

Show.includes(:characters).where(characters: {'name = ? AND name = ?', "Batman", "Robin"})

But this returns an error. Is there a syntax for this that would work?

UPDATE

The query

Show.includes(:characters).where('characters.name = ? AND characters.name = ?', "Batman", "Robin")

returns a value of 0, even though there are definitely Shows associated with both Batman and Robin.

Answer 1

Using plain SQL, one solution is :

select s. * 
from shows as s 
join characters as c1 on (s.id=c1.show_id) 
join characters as c2 on (s.id=c2.show_id) 
where c1.name='Batman' 
and   c2.name='Robin';

Using Arel, I would translate as :

Show.joins('join characters as c1 on shows.id=c1.show_id').joins('join
characters as c2 on shows.id=c2.show_id').where('c1.name = "Batman"').where(
'c2.name="Robin"')

Answer 2

So you'll have to get a little fancy with SQL here; especially if you want it to be performant and handle different types of matchers.

select count(distinct(characters.name)) as matching_characters_count, shows.* from shows
  inner join characters on characters.show_id = shows.id and (characters.name = 'Batman' or characters.name = 'Robin')
group by shows.id
having matching_characters_count = 2

To translate into ActiveRecord

Show.select('count(distinct(characters.name)) as matching_characters_count, shows.*').
   joins('inner join characters on characters.show_id = shows.id and (characters.name = "Batman" or characters.name = "Robin")').
   group('shows.id').
   having('matching_characters_count = 2')

Then you'd probably do a pass with interpolation and then AREL it up; so you wouldn't be building string queries.

Answer 3

A plain SQL solution using aggregate functions. The SQL statements returns the ID values of the ´shows´ records you are looking for.

SELECT c.show_id
  FROM characters c
 WHERE c.name IN ('Batman','Robin')
 GROUP BY c.show_id
HAVING COUNT(DISTINCT c.name) = 2

You can put this statement into a select_values() call and then grab the shows with the values of the returned array.