Is there an ActiveRecord equivalent to using a nested subquery i.e. ... where NOT IN(select...) ?

Question

I have 3 models: Category, Account, and SubAccount
The relations are:
Accounts has_many :sub_accounts
Categories has_many :sub_accounts

I wanted to get a list of all Categories that are not used by a given account. My method in the Category model currently looks like:

class Category < ActiveRecord::Base  
  def self.not_used_by(account)
      Category.find_by_sql("select * from categories where id not in(select category_id from sub_accounts where account_id = #{account.id})")
  end
end

My question is, is there a cleaner alternative than using SQL?

NB. I am currently using Rails 3(beta)

Answer 1

You could move the method to the account model and use more of ActiveRecord by doing something like:

class Account < ActiveRecord::Base  
  def unused_categories
    Category.where("id NOT IN (?)", sub_accounts.map(&:category_id))
  end
end

Then you could do something like:

Account.first.unused_categories

Answer 2

AR does not do this out of the box. You may also want to check the excellent SearchLogic gem for a programatic approach.

search = Category.search
search.id_not_in sub_accounts.map(&:category_id)
search.name_equals "auto"
search. ..# other conditions
search.all

Answer 3

Try MetaWhere. http://metautonomo.us/projects/metawhere

You'll need my fork of Arel installed until the changes get merged (soon!) but with it installed you can do something like:

Category.where(:id.not_in => sub_accounts.map(&:category_id))