Rails active record query: Find records on one side of a has_many :through that haven't been used with a particular record on the other side

Question

I have a has_many :through relationship set up like so

class Situation < ActiveRecord::Base
  has_many :notifications
  has_many :notiftypes, through: :notifications
end

class Notification < ActiveRecord::Base
  belongs_to :situation
  belongs_to :notiftype
end

class Notiftype < ActiveRecord::Base
  has_many :notifications
  has_many :situations, through: :notifications
end

So, a Situation has many Notifications, which can be of many types (Notiftype).

My problem is trying to query for the notiftypes that have not been set for a particular situation.

Want to find records with no associated records in Rails 3

The answers in that question get me close, but only to the point of finding Notiftypes that have not been set AT ALL.

If this were the standard :situation has_many :notiftypes I could just do a Left Outer Join like so

myquery = Notiftype.joins('LEFT OUTER JOIN situations ON situations.notiftype_id = notiftype.id').where('notiftype_id IS NULL')

but I'm really not sure how to do this with the intermediate table between them.

I have been trying consecutive joins but it's not working. I'm not sure how to join the two separated tables.

Can anyone explain the right way to query the db? I am using SQLite, Rails 3.1, Ruby 1.9.2 right now, but likely Postgresql in the future.

Harish Shetty · Accepted Answer · 2012-02-29T05:28:48.010

0

Try this:

class Situation < ActiveRecord::Base
  # ...
  has_many :notiftypes, through: :notifications do

    def missing(reload=false)
      @missing_notiftypes = nil if reload
      @missing_notiftypes ||= proxy_owner.notiftype_ids.empty? ?
        Notiftype.all : 
        Notiftype.where("id NOT IN (?)", proxy_owner.notiftype_ids)
    end
  end
end

Now to get the missing Notiftype

situation.notiftypes.missing

If you want to further optimize this to use one SQL rather than two you can do the following:

class Situation < ActiveRecord::Base
  # ...
  has_many :notiftypes, through: :notifications do

    def missing(reload=false)
      @missing_notiftypes = nil if reload
      @missing_notiftypes ||= Notiftype.joins("
          LEFT OUTER JOIN (#{proxy_owner.notiftypes.to_sql}) A
          ON A.id = notiftypes.id").
        where("A.id IS NULL")
    end
  end
end

You can access the missing Notifytypes as:

situation.notiftypes.missing

edited Feb 29 '12 at 05:28

answered Feb 28 '12 at 07:29

Harish Shetty

64,083
21
152
198

Updated the answer with one SQL solution, take a look. – Harish Shetty Feb 28 '12 at 18:24
It works! I replaced proxy_owner with http://stackoverflow.com/questions/7001810/alternative-method-for-proxy-owner-in-activerecord – bknoles Feb 28 '12 at 20:05
Sorry... I hit enter early. I replaced proxy_owner with proxy_association.owner based on this thread http://stackoverflow.com/questions/7001810/alternative-method-for-proxy-owner-in-activerecord Is that necessary? I wasn't getting any errors with the proxy_owner method, and they both work correctly. – bknoles Feb 28 '12 at 20:08
My code is based on Rails 2.3.x, it looks like `proxy_owner` is deprecated. – Harish Shetty Feb 28 '12 at 20:39
gotcha. As I said, proxy_assocation.owner works for me on 3.1.1 – bknoles Feb 28 '12 at 21:06
The method wasn't working when a situation had no notiftypes. I added an if statement, and it's working. Can you review it in the answer below? I'm not sure if there is a more efficient way to do this. – bknoles Feb 29 '12 at 03:42
I have updated the answer to address the empty list scenario. If you use my 2nd solution this problem will not arise. – Harish Shetty Feb 29 '12 at 05:29

Rails active record query: Find records on one side of a has_many :through that haven't been used with a particular record on the other side

1 Answers1