How do I write a query in Active record for a model that has whos has_many is empty?

Question

I have model Member which has many DuesPayments.

I want a query for Members who didn't make their dues_payment in 2012. The field on DuesPayment is :for_year. Or in English - I want all members who don't have a record in the dues_payments table with a year of 2012.

#<DuesPayment:0x00000107b3cdd0> {
  :id                      => 1209,
  :member_id               => 446,
  :amount                  => 25.0,
  :created_at              => Thu, 07 Feb 2013 17:01:15 EST -05:00,
  :updated_at              => Thu, 07 Feb 2013 17:01:15 EST -05:00,
  :for_year                => 2013,
  :payment_notification_id => 300
}

Seems like I need an outer join? The problem is that there is no record there to 'select' if a payment hasn't been made.

This is what I'm doing now, which is basically a subquery and an inversion. The idea here is to get the ids of all members that did pay their dues, and then select all members who aren't THOSE members:

scope :dues_not_paid_in, lambda {|year|
  Member.
    where("id not in (?)", 
      DuesPayment.
        select(:member_id).
        where(:for_year => year).
        collect(&:member_id).uniq
  )
}

My guess is there's a more elegant way to do this.

Answer 1

Do you have some kind of paid / not paid column on your dues_payments model? If so you can do:

Member.joins(:dues_payments).where("for_year = ? AND paid = ?", 2012, false)

Answer 2

Without understanding a little bit more about your models something like this should work, if you require more help can you post your models code and how we know if a payment is due or not.

unpaid_members = Member.DuesPayments.where(for_year: 2012)

Answer 3

Members who didn't make their dues_payment in 2012

Member.where("id NOT IN (?)", DuesPayment.select(:member_id).where(:for_year => 2012).map(&:member_id))