# Joins + conditions problem **URL:** \ **Category:** Rails\ **Created:** [February 15, 2009, 11:37am UTC](https://www.ruby-forum.com/t/joins-conditions-problem/160359 "2009-02-15T11:37:01Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![fRAnKEnSTEin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/0ea827/32.png) [@fRAnKEnSTEin](https://www.ruby-forum.com/u/fRAnKEnSTEin)\ **Post date:** [February 15, 2009, 11:37am UTC](https://www.ruby-forum.com/t/joins-conditions-problem/160359/1 "2009-02-15T11:37:01Z")
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 ---
**Author:** ![fRAnKEnSTEin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/0ea827/32.png) [@fRAnKEnSTEin](https://www.ruby-forum.com/u/fRAnKEnSTEin)\ **Post date:** [February 15, 2009, 1:36pm UTC](https://www.ruby-forum.com/t/joins-conditions-problem/160359/2 "2009-02-15T13:36:52Z")
On Feb 15, 10:36 am, fRAnKEnSTEin [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > @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 ---
**Author:** ![fRAnKEnSTEin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/0ea827/32.png) [@fRAnKEnSTEin](https://www.ruby-forum.com/u/fRAnKEnSTEin)\ **Post date:** [February 15, 2009, 8:19pm UTC](https://www.ruby-forum.com/t/joins-conditions-problem/160359/3 "2009-02-15T20:19:43Z")
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 On Sun, Feb 15, 2009 at 7:36 AM, Frederick C. ---
**Author:** ![fRAnKEnSTEin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/0ea827/32.png) [@fRAnKEnSTEin](https://www.ruby-forum.com/u/fRAnKEnSTEin)\ **Post date:** [February 15, 2009, 10:30pm UTC](https://www.ruby-forum.com/t/joins-conditions-problem/160359/4 "2009-02-15T22:30:49Z")
On 15 feb, 13:18, Chris W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > @products = Product.find(:all, > :include =\> :pictures, > :joins =\> " AND pictures.main = > "+whatevervalue.to\_s, > :conditions > “IFNULL(pictures.id,0)=IFNULL(pictures.id,0)”) > > -Chris Hi there, Thank you very much for your help, it just worked perfectly!!!. Regards ---
**Author:** ![fRAnKEnSTEin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/0ea827/32.png) [@fRAnKEnSTEin](https://www.ruby-forum.com/u/fRAnKEnSTEin)\ **Post date:** [February 16, 2009, 9:25am UTC](https://www.ruby-forum.com/t/joins-conditions-problem/160359/5 "2009-02-16T09:25:01Z")
Hi there, The query works without the “:conditions =\> “IFNULL(pictures.id,0) =IFNULL(pictures.id,0)”)” part. Why you put it? Regards ---
**Author:** ![fRAnKEnSTEin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/0ea827/32.png) [@fRAnKEnSTEin](https://www.ruby-forum.com/u/fRAnKEnSTEin)\ **Post date:** [February 16, 2009, 7:09pm UTC](https://www.ruby-forum.com/t/joins-conditions-problem/160359/6 "2009-02-16T19:09:54Z")
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