Complex ARel query

I've got a complicated query that I can't wrap my head around (using either sql or ActiveRecord) Here are my models:

class Contact
  has_many :profile_answers
end

class ProfileAnswer
  belongs_to :contact
  belongs_to :profile_question
end

class ProfileQuestion
  has_many :profile_answers
end

I'm 开发者_如何学Ctrying to find the number of ProfileAnswers for two contacts that have the same value for a particular ProfileQuestion. In other words:

Get the total number of profile answers that two contacts have answered with the same value for a particular profile_question

I don't want to make multiple queries and filter as I know this is possible with Sql only, i just don't know how to do it

I had considered a self join of profile_answers on profile_question_id then filtering by value being equal, but i still can't wrap my head around that. Any help is greatly appreciated.

I think this will do:

SELECT COUNT(DISTINCT profile_question_id)
FROM 
  ( SELECT profile_question_id
    FROM ProfileAnswer an
      JOIN ProfileQuestion qu
        ON qu.id = an.profile_question_id
    WHERE contact_id IN ( id1, id2 )
    GROUP BY profile_question_id
           , value
    HAVING COUNT(*) = 2
  ) AS grp

And the JOIN seems not be used. So, if ProfileAnswer.profile_question_id is NOT NULL, this will suffice:

SELECT COUNT(*)
FROM 
  ( SELECT profile_question_id
    FROM ProfileAnswer
    WHERE contact_id IN ( id1, id2 )
    GROUP BY profile_question_id
           , value
    HAVING COUNT(*) = 2
  ) AS grp

EDITED for two specific contacts (with ids id1 and id2).

Added the WHERE and changed the COUNT (DINSTINCT ) to COUNT(*).

Perhaps this version with JOIN can be more easily adapted to ActiveRecord.

Using JOIN

SELECT COUNT(*)
FROM ProfileAnswer a
  JOIN ProfileAnswer b
    ON a.profile_question_id = b.profile_question_id
    AND a.value = b.value
WHERE a.contact_id = id1
  AND b.contact_id = id2 

Here's how I ended up doing it, thanks again @ypercube:

class ProfileAnswer < ActiveRecord::Base

  def self.for_contacts(*contacts)
   where :contact_id => contacts.collect(&:id)
  end

  def self.common_for_contacts(*contacts)
    select(:profile_question_id).for_contacts(*contacts).group(:profile_question_id, :value).having("count(*) = #{contacts.length}")
  end

  def self.common_count_for_contacts(*contacts)
    find_by_sql("select count(*) as answer_count from (#{common_for_contacts(*contacts).to_sql})").first.answer_count
  end
end

# Usage
ProfileAnswer.common_count_for_contacts(contact1, contact2[, contact3...])

Still had to use a find_by_sql in the end for the nested select... not sure if there's any way around that ??

Also annoying that find_by_sql returns an array, so I had to use .first which then gives me the object that has my answer_count property on it.