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

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

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

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

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

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

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

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      gt   lt ActiveRecord  Relation    lt 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  gt           lt 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  gt              lt 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  gt   gt    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       gt   lt ActiveRecord  Relation    gt    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    gt   1m  36mUser Load  0 0ms   0m    1mSELECT  users    FROM  users  WHERE  users   id      ORDER BY  users   id  ASC LIMIT 1  0m     id   1      gt  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      gt  lt ActiveRecord  Relation    lt 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  gt        lt 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  gt        lt 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  gt            lt 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  gt   gt    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      gt    lt ActiveRecord  Relation    lt 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  gt   gt    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    gt  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

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

Examples related to ruby-on-rails

Examples related to ruby

Examples related to database

Examples related to join

Examples related to rails-activerecord