Need help with SQL

rubyonrails-talk
#1

I am having problems writing conditions for a find request. Here is my
situation:

Product has and belongs to many Categories

So I have join table: categories_products

I want to find all products that are not in category.id = 1. If they
belong to category.id = 1 then I don't want them returned.

I have tried the following and non of them worked:

Product.find(:all, :conditions => "categories.id != 1", :include =>
:categories)
Product.find(:all, :conditions => "categories_products.category_id !=
1", :include => :categories)

Each of those still returned products in the category.id = 1

Any idea how I can accomplish this?

@products_not_in_category_1 = Product.find(:all, :include => :categories).map {|p| p.categories.include?(1) ? nil : p}.compact

Not very efficient if you have a lot of products/categories, but that should work I think... or something along those lines.

-philip

#2

Philip Hallstrom wrote:

I have tried the following and non of them worked:

Product.find(:all, :conditions => "categories.id != 1", :include =>
:categories)
Product.find(:all, :conditions => "categories_products.category_id !=
1", :include => :categories)

Each of those still returned products in the category.id = 1

Any idea how I can accomplish this?

@products_not_in_category_1 = Product.find(:all, :include =>
:categories).map {|p| p.categories.include?(1) ? nil : p}.compact

Not very efficient if you have a lot of products/categories, but that
should work I think... or something along those lines.

-philip

Yeah I can't do that because of the way I search models. It's
complicated, but it needs to be SQL.

Then something like...

select * from products where id not in (select product_id from categories_products where category_id = 1)

-philip