Need help with SQL

Rails
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?

Thanks for your help.

2

Ben J. wrote:

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?

Thanks for your help.

Also, the problem is that if a product belongs to ANY other category it
is returned. I want it so that if that product belongs to that category,
do not return it, regardless if it’s in other categories.

Thanks for your help.

3

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

4

Any idea how I can accomplish this?
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

5

Philip H. wrote:

Any idea how I can accomplish this?
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

thanks a lot, that worked great!

6

Philip H. 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.