Querying a has_many association's count in Rails 3

Question

I've been going over a tonne of StackOverflow articles trying to work out a particularly tricky Rails 3 join query to no avail - so I'm asking a new question!

I have a model called "User" which has_many "Checks" via a polymorphic association (the actual columns on Check are "target_type" and "target_id"). The Check has a string column called "type", which denotes the reason for the check. Something like this:

-----------------------------------------------------
id   |  target_type  |  target_id  |      type     |
-----------------------------------------------------
1        User          1          type_1
2        User          2          type_2

I want to find all users who don't have a check associated with them with a set type - so it's a join and a count, I think.

So for instance, I want to be able to make a query to find all users who have no "type_1" checks, and it should return the user with id #2.

How would I go about doing this?

(I've been looking at all the stuff around counts and grouping (e.g. Rails has_many association count child rows) but nothing seems to quite match.)

Thanks!

Answer 1

You can try sth like:

class Merchant < ActiveRecord::Base
  ...
  def self.non_fraudulent
    includes(:fraud_checks).group("#{self.table_name}.id").having("SUM(CASE WHEN fraud_checks.type = 'fraudulent_ip' THEN 1 ELSE 0 END) = 0")
  end
end