Rails include vs joins

Question

This is more of a  why do things work this way  question rather than a  I don t know how to do this  question     So the gospel on pulling associated records that you know you re going to use is to use  include because you ll get a join and avoid a whole bunch of extra queries   Post all  include   gt   comments    However when you look at the logs  there s no join happening   Post Load  3 7ms    SELECT   FROM  posts  Comment Load  0 2ms    SELECT  comments    FROM  comments                          WHERE   comments  post id IN  1 2 3 4                           ORDER BY created at asc     It is taking a shortcut because it pulls all of the comments at once  but it s still not a join  which is what all the documentation seems to say   The only way I can get a join is to use  joins instead of  include   Post all  joins   gt   comments    And the logs show   Post Load  6 0ms   SELECT  posts    FROM  posts                      INNER JOIN  comments  ON  posts  id    comments  post id   Am I missing something  I have an app with half a dozen associations and on one screen I display data from all of them  Seems like it would be better to have one join-ed query instead of 6 individuals  I know that performance-wise it s not always better to do a join rather than individual queries  in fact if you re going by time spent  it looks like the two individual queries above are faster than the join   but after all the docs I ve been reading I m surprised to see  include not working as advertised   Maybe Rails is cognizant of the performance issue and doesn t join except in certain cases

User · Answer

I was recently reading more on difference between :joins and :includes in rails. Here is an explaination of what I understood (with examples :))

Consider this scenario:

A User has_many comments and a comment belongs_to a User.
The User model has the following attributes: Name(string), Age(integer). The Comment model has the following attributes:Content, user_id. For a comment a user_id can be null.

Joins:

:joins performs a inner join between two tables. Thus

Comment.joins(:user)

#=> <ActiveRecord::Relation [#<Comment id: 1, content: "Hi I am Aaditi.This is my first   comment!", user_id: 1, created_at: "2014-11-12 18:29:24", updated_at: "2014-11-12 18:29:24">, 
     #<Comment id: 2, content: "Hi I am Ankita.This is my first comment!", user_id: 2, created_at: "2014-11-12 18:29:29", updated_at: "2014-11-12 18:29:29">,    
     #<Comment id: 3, content: "Hi I am John.This is my first comment!", user_id: 3, created_at: "2014-11-12 18:30:25", updated_at: "2014-11-12 18:30:25">]>

will fetch all records where user_id (of comments table) is equal to user.id (users table). Thus if you do

Comment.joins(:user).where("comments.user_id is null")

#=> <ActiveRecord::Relation []>

You will get a empty array as shown.

Moreover joins does not load the joined table in memory. Thus if you do

comment_1 = Comment.joins(:user).first

comment_1.user.age
#=>?[1m?[36mUser Load (0.0ms)?[0m  ?[1mSELECT "users".* FROM "users" WHERE "users"."id" = ? ORDER BY "users"."id" ASC LIMIT 1?[0m  [["id", 1]]
#=> 24

As you see, comment_1.user.age will fire a database query again in the background to get the results

Includes:

:includes performs a left outer join between the two tables. Thus

Comment.includes(:user)

#=><ActiveRecord::Relation [#<Comment id: 1, content: "Hi I am Aaditi.This is my first comment!", user_id: 1, created_at: "2014-11-12 18:29:24", updated_at: "2014-11-12 18:29:24">,
   #<Comment id: 2, content: "Hi I am Ankita.This is my first comment!", user_id: 2, created_at: "2014-11-12 18:29:29", updated_at: "2014-11-12 18:29:29">,
   #<Comment id: 3, content: "Hi I am John.This is my first comment!", user_id: 3, created_at: "2014-11-12 18:30:25", updated_at: "2014-11-12 18:30:25">,    
   #<Comment id: 4, content: "Hi This is an anonymous comment!", user_id: nil, created_at: "2014-11-12 18:31:02", updated_at: "2014-11-12 18:31:02">]>

will result in a joined table with all the records from comments table. Thus if you do

Comment.includes(:user).where("comment.user_id is null")
#=> #<ActiveRecord::Relation [#<Comment id: 4, content: "Hi This is an anonymous comment!", user_id: nil, created_at: "2014-11-12 18:31:02", updated_at: "2014-11-12 18:31:02">]>

it will fetch records where comments.user_id is nil as shown.

Moreover includes loads both the tables in the memory. Thus if you do

comment_1 = Comment.includes(:user).first

comment_1.user.age
#=> 24

As you can notice comment_1.user.age simply loads the result from memory without firing a database query in the background.

User · Answer

joins  just used to join tables and when you called associations on joins then it will again fire query  it mean many query will fire   lets suppose you have tow model  User and Organisation User has many organisations suppose you have 10 organisation for a user   records  User joins  organisations  where  organisations user id   1   QUERY will be   select   from users INNER JOIN organisations ON organisations user id   users id where organisations user id   1  it will return all records of organisation related to user and  records map  u u organisation name  it run QUERY like  select   from organisations where organisations id   x then time hwo many organisation you have    total number of SQL is 11 in this case  But with  includes  will eager load the included associations and add them in memory load all associations on first load  and not fire query again  when you get records with includes like   records  User includes  organisations  where  organisations user id   1   then query will be   select   from users INNER JOIN organisations ON organisations user id   users id where organisations user id   1 and     select   from organisations where organisations id IN IDS of organisation 1  to 10   if 10 organisation and when you run this     records map  u u organisation name      no query will fire

User · Answer

joins works as database join and it joins two or more table and fetch selected data from backend database     includes work as left join of database  It loaded all the records of left side  does not have relevance of right hand side model  It is used to eager loading because it load all associated object in memory  If we call associations on include query result then it does not fire a query on database  It simply return data from memory because it have already loaded data in memory

User · Answer

It appears that the  include functionality was changed with Rails 2 1   Rails used to do the join in all cases  but for performance reasons it was changed to use multiple queries in some circumstances   This blog post by Fabio Akita has some good information on the change  see the section entitled  Optimized Eager Loading

User · Answer

In addition to a performance considerations  there s a functional difference too  When you join comments  you are asking for posts that have comments- an inner join by default  When you include comments  you are asking for all posts- an outer join

User · Answer

joins will just joins the tables and brings selected fields in return  if you call associations on joins query result  it will fire database queries again   includes will eager load the included associations and add them in memory   includes loads all the included tables attributes  If you call associations on include query result  it will not fire any queries

User · Answer

tl dr  I contrast them in two ways   joins - For conditional selection of records   includes - When using an association on each member of a result set   Longer version  Joins is meant to filter the result set coming from the database  You use it to do set operations on your table  Think of this as a where clause that performs set theory   Post joins  comments   is the same as  Post where  id in  select post id from comments     Except that if there are more than one comment you will get duplicate posts back with the joins  But every post will be a post that has comments  You can correct this with distinct   Post joins  comments  count   gt  10 Post joins  comments  distinct count   gt  2   In contract  the includes method will simply make sure that there are no additional database queries when referencing the relation  so that we don t make n   1 queries   Post includes  comments  count   gt  4   includes posts without comments so the count might be higher    The moral is  use joins when you want to do conditional set operations and use includes when you are going to be using a relation on each member of a collection

User · Answer

The difference between joins and include is that using the include statement generates a much larger SQL query loading into memory all the attributes from the other table s    For example  if you have a table full of comments and you use a  joins    users to pull in all the user information for sorting purposes  etc it will work fine and take less time than  include  but say you want to display the comment along with the users name  email  etc   To get the information using  joins  it will have to make separate SQL queries for each user it fetches  whereas if you used  include this information is ready for use   Great example   http   railscasts com episodes 181-include-vs-joins

[ruby-on-rails] Rails :include vs. :joins

Joins:

Includes:

Examples related to ruby-on-rails

Examples related to ruby

Examples related to database

Examples related to join

Examples related to rails-activerecord