Joins + conditions problem

Rails
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

On Feb 15, 10:36 am, fRAnKEnSTEin [email protected] 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

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

On Sun, Feb 15, 2009 at 7:36 AM, Frederick C.

4

On 15 feb, 13:18, Chris W. [email protected] 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

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