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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Feb 15, 10:36 am, fRAnKEnSTEin <shirkav...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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> > 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--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 Cheung
<frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:>
>
>
> On Feb 15, 10:36 am, fRAnKEnSTEin
<shirkav...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>> 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
>>
>> 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
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
On 15 feb, 13:18, Chris Warren <csw11...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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)") > > -ChrisHi there, Thank you very much for your help, it just worked perfectly!!!!!. Regards --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Hi there, The query works without the ":conditions => "IFNULL(pictures.id,0) =IFNULL(pictures.id,0)")" part. Why you put it? Regards --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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
On Mon, Feb 16, 2009 at 3:23 AM, fRAnKEnSTEin
<shirkavand-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:>
> Hi there,
>
> The query works without the ":conditions =>
"IFNULL(pictures.id,0)
> =IFNULL(pictures.id,0)")" part. Why you put it?
>
> Regards
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---