Forum: Ruby on Rails joins + conditions problem

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
fRAnKEnSTEin (Guest)
on 2009-02-15 12:37
(Received via mailing list)
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
Frederick C. (Guest)
on 2009-02-15 14:36
(Received via mailing list)
On Feb 15, 10:36 am, fRAnKEnSTEin <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
Chris W. (Guest)
on 2009-02-15 21:19
(Received via mailing list)
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.
fRAnKEnSTEin (Guest)
on 2009-02-15 23:30
(Received via mailing list)
On 15 feb, 13:18, Chris W. <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
fRAnKEnSTEin (Guest)
on 2009-02-16 10:25
(Received via mailing list)
Hi there,

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

Regards
Chris W. (Guest)
on 2009-02-16 20:09
(Received via mailing list)
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
This topic is locked and can not be replied to.