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!
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.
On 22 Sep 2008, at 19:43, Ben J. wrote:
=> 934
=> :user).size
=> 172
I’m getting the same result. What is the general rule of thumb on when
to use joins or includes?
See
.
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. 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.
On Sep 22, 9:16 pm, Ben J. [email protected]
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
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.
Frederick C. wrote:
On Sep 22, 9:16�pm, Ben J. [email protected]
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
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.
Thanks.
On Sep 22, 9:35 pm, Ben J. [email protected]
wrote:
Frederick C. wrote:
On Sep 22, 9:16 pm, Ben J. [email protected]
wrote:
=> 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