joins + conditions problem

rubyonrails-talk
1

hi,

I have a table for example Product, and another table called Picture (Product has many pictures, Picture belongs to Product). Now let's say that i want to retrieve all the Product and its related pictures, i do this with:

@products = Product.find(:all, :include => :pictures)

But in my case, i need to add a condition to the above code, like so:

@products = Product.find(:all, :include => :pictures, :conditions => "pictures.main = 1")

So, i will retrive all the products and its related MAIN pictures, but.....if one product does not have any picture, @products will not have that product, so i am unable to show the product, because it does not have any picture. Now Rails generate this sql for me

SELECT `products`.`id` AS t0_r0, `products`.`name` , `product_pictures`.`id` AS t1_r0, `product_pictures`.`parent_id` AS t1_r1 FROM `products` LEFT OUTER JOIN `product_pictures` ON product_pictures.product_id = products.id WHERE (product_pictures.principal = 1)

But why i really need is something like:

SELECT `products`.`id` AS t0_r0, `products`.`name` , `product_pictures`.`id` AS t1_r0, `product_pictures`.`parent_id` AS t1_r1 FROM `products` LEFT OUTER JOIN `product_pictures` ON product_pictures.product_id = products.id AND product_pictures.principal = 1

Any idea of how can i do this?

Regards

2

hi,

I have a table for example Product, and another table called Picture (Product has many pictures, Picture belongs to Product). Now let's say that i want to retrieve all the Product and its related pictures, i do this with:

@products = Product.find(:all, :include => :pictures)

But in my case, i need to add a condition to the above code, like so:

@products = Product.find(:all, :include => :pictures, :conditions => "pictures.main = 1")

If you create an association called main_pictures with the condition main=1 then Product.find :all, :include => :main_pictures should work (but only if you don't cause activerecord to fall back to the old joins based :include).

Fred

3

This is almost exactly the same problem I was tackling last night. The workaround I found was a) specify the include in the find b) add conditions mentioning the included tables so the fetching is done in a single SQL statement instead of a separate statement for each association. As a part of this, make sure the condition doesn't negate the point of your outer join c) use a bit of a hack to append your secondary condition to the join

I think something like this might do the trick:

@products = Product.find(:all,                                       :include => :pictures,                                       :joins => " AND pictures.main = "+whatevervalue.to_s,                                       :conditions "IFNULL(pictures.id,0)=IFNULL(pictures.id,0)")

  -Chris

4

Hi there,

Thank you very much for your help, it just worked perfectly!!!!!.

Regards

5

Hi there,

The query works without the ":conditions => "IFNULL(pictures.id,0) =IFNULL(pictures.id,0)")" part. Why you put it?

Regards

6

Hi,

You situation may differ, but I found that the presence of a table in the conditions determined whether associations were loaded in a single query or multiple ones.     1. SELECT * FROM products         SELECT * FROM pictures WHERE pictures.product_id IN (...) vs     2. SELECT * FROM products OUTER JOIN pictures ON ...

I found (by looking at the log file) that without the conditions the pictures would be loaded in a separate query than the products(version 1), whereas tables mentioned in a condition would be included as a part of a single query (version 2 - Fredrick put me on that track).

I didn't do especially extensive testing. In my case I was doing Find(id) (using 2.2.2 on Windows) rather than Find(:all), and it's entirely possible that :all uses a single query regardless of conditions or lack thereof.

If it works for you without the :conditions, then I'd certainly vote for cutting them.

  -Chris