Is there an ActiveRecord equivalent for using the nested subquery ie ... where is NOT IN (select ...)?

Question

I have 3 models: Category, Account and SubAccount
Relations:
accounts has_many: sub_accounts
Categories has_many: sub_accounts

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

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

ActiveRecord, - :

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

- :

Account.first.unused_categories

Answer 2

AR . SearchLogic .

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 will need my Arel plug until the changes are merged (soon!), But with what you installed you can do something like:

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