CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3
snorkpete
snorkpete

Reputation: 14564

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

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)

Upvotes: 3

Views: 1832

Answers (3)

Ernie Miller
Ernie Miller

Reputation: 9

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))

Upvotes: 0

Vlad Zloteanu
Vlad Zloteanu

Reputation: 8512

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

Upvotes: 0

Peter Brown
Peter Brown

Reputation: 51697

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

Upvotes: 3

Related Questions