I know this is probably a noob question, but I needed a little clarification. I did the following in the console of my app:>> User.find(:all, :conditions => "orders.id > 1", :joins => :orders).size=> 1254>> User.find(:all, :conditions => "orders.id > 1", :include => :orders).size=> 934 A user has many orders. Why am I getting different results? When using fields in a has_many relationship should I ALWAYS use the :include option. I did the reverse too:>> Order.find(:all, :conditions => "users.id < 100", :joins => :user).size=> 172>> Order.find(:all, :conditions => "users.id < 100", :include => :user).size=> 172 I''m getting the same result. What is the general rule of thumb on when to use joins or includes? Thanks for your help! -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
How did you populate your tables? I am sure it is an issue about LEFT JOIN, i.e: all records from left table will appear in the result even if they don''t have matches on the right table. As an order always has a user, :joins and :include return the same number of rows. Whereas, a user doesn''t necessarily have an order, but he will still appear in the result with a left join. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 22 Sep 2008, at 19:43, Ben Johnson wrote:> > I know this is probably a noob question, but I needed a little > clarification. I did the following in the console of my app: > >>> User.find(:all, :conditions => "orders.id > 1", :joins >>> => :orders).size > => 1254 > >>> User.find(:all, :conditions => "orders.id > 1", :include >>> => :orders).size > => 934 > > A user has many orders. Why am I getting different results? When using > fields in a has_many relationship should I ALWAYS use the :include > option. I did the reverse too: > >>> Order.find(:all, :conditions => "users.id < 100", :joins >>> => :user).size > => 172 > >>> Order.find(:all, :conditions => "users.id < 100", :include >>> => :user).size > => 172 > > I''m getting the same result. What is the general rule of thumb on when > to use joins or includes? >See http://www.spacevatican.org/2008/6/22/the-difference-between-include-and-joins . The difference between your two cases is that a user can have many orders: for each user you''ll get a result for each object, whereas an order can only ever have one user. Fred> Thanks for your help! > -- > Posted via http://www.ruby-forum.com/. > > >--~--~---------~--~----~------------~-------~--~----~ 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 http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Thanks for your help. I am still somewhat confused though. So is the general rule of thumb to use joins on belongs to relationships and include on has_many. It looks as if joins returns flawed results is used on a has_many relationship. Is this correct? I would prefer to avoid include because it is so much slower, but joins doesnt seem to return the correct records. Thanks. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Sep 22, 9:16 pm, Ben Johnson <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Thanks for your help. I am still somewhat confused though. > > So is the general rule of thumb to use joins on belongs to relationships > and include on has_many. It looks as if joins returns flawed results is > used on a has_many relationship. Is this correct? >No. Use :joins when you want a join. Use include when you want to avoid the n+1 problem (ie you want rails to load and populate the associations). Fred> I would prefer to avoid include because it is so much slower, but joins > doesnt seem to return the correct records. > > Thanks. > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ 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 http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung wrote:> On Sep 22, 9:16�pm, Ben Johnson <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> > wrote: >> Thanks for your help. I am still somewhat confused though. >> >> So is the general rule of thumb to use joins on belongs to relationships >> and include on has_many. It looks as if joins returns flawed results is >> used on a has_many relationship. Is this correct? >> > No. Use :joins when you want a join. Use include when you want to > avoid the n+1 problem (ie you want rails to load and populate the > associations). > > FredThanks for your help. I agree, I would prefer to use joins, but they do not return the proper results. It seems that joins return duplicate results:>> User.find(:all, :conditions => "orders.id > 1", :joins => :orders).size=> 1254>> User.find(:all, :conditions => "orders.id > 1", :include => :orders).size=> 934 In the above example the first query is returning duplicate results. I am getting users with the same ID. I''m confused why it would return duplicate results. Any idea why this is? Seeing as this is a problem it seems that :include is my only option. Thanks. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
It also seems that the following works just fine: User.find(:all, :conditions => "orders.id > 1", :joins => :orders, :group => "users.id").size => 934 I apologize for my ignorance in this situation, but is the above search "improper"? Is there a better way to do this? Thanks. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Sep 22, 9:35 pm, Ben Johnson <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Frederick Cheung wrote: > > On Sep 22, 9:16 pm, Ben Johnson <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> > > wrote:> Thanks for your help. I agree, I would prefer to use joins, but they do > not return the proper results. It seems that joins return duplicate > results: > > >> User.find(:all, :conditions => "orders.id > 1", :joins => :orders).size > > => 1254 > > >> User.find(:all, :conditions => "orders.id > 1", :include => :orders).size > > => 934 > > In the above example the first query is returning duplicate results. I > am getting users with the same ID. I''m confused why it would return > duplicate results. Any idea why this is? Seeing as this is a problem it > seems that :include is my only option.If your users table had exactly one user, and that user had 2 orders, then select * from users inner join orders on orders.user_id = users.id would return the result set (i''ve made up some column names) users.id | users.name | orders.id | orders.name| 1 | bob | 1 | a tv 1 | bob | 2 | a dvd This is just how joins are supposed to work. I''m still not sure what you''re trying to achieve in the first place (but grouping or using select distinct may be options) :include is different (and doesn''t even use joins half the time). It''s doing a lot of work behind the scenes wiring up associations. Fred --~--~---------~--~----~------------~-------~--~----~ 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 http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---