Railscasts - Include vs Joins

#181

Sep 28, 2009

Include vs Joins

The :include and :joins options for the find method can be a little confusing because they are so similar. In this episode I show specifically when to use which option.

Tags: active-record

Download (22.8 MB, 11:37)

alternative download for iPod & Apple TV (16.2 MB, 11:37)

Read it on ASCIIcasts

Resources

# script/console
ActiveRecord::Base.logger = Logger.new(STDOUT) # to show logs
c = Comment.all(:joins => :user, :conditions => { :users => { :admin => true } })
c.first.users
c = Comment.all(:include => :user, :conditions => { :users => { :admin => true } })
c.first.users
User.all(:joins => :comments, :select => "users.*, count(comments.id) as comments_count", :group => "users.id")
g = Group.first
Comment.all(:joins => {:user => :memberships}, :conditions => { :memberships => { :group_id => g.id } })

# models/comment.rb
class Comment < ActiveRecord::Base
  belongs_to :user
end

# models/user.rb
class User < ActiveRecord::Base
  has_many :memberships
  has_many :groups, :through => :memberships
  has_many :comments
end

# models/membership.rb
class Membership < ActiveRecord::Base
  belongs_to :user
  belongs_to :group
end

# models/group.rb
class Group < ActiveRecord::Base
  has_many :memberships
  has_many :users, :through => :memberships
  
  def comments
    Comment.scoped(:joins => {:user => :memberships}, :conditions => { :memberships => { :group_id => id } })
  end
end

# comments_controller.rb
def index
  @comments = Comment.all(:joins => :user, :conditions => { :users => { :admin => true } }, :order => "comments.created_at desc")
end

# users_controller.rb
def index
  @users = User.all(:joins => :comments, :select => "users.*, count(comments.id) as comments_count", :group => "users.id")
end

<!-- views/groups/show.html.erb -->
<%= render @group.comments %>

<!-- views/users/index.html.erb -->
<%= pluralize user.comments_count, "comment" %>

< Previous Episode

Next Episode >

28 comments

1. QuBiT Sep 28, 2009 at 00:44

Thanks for this one, as optimizing DB queries is always useful.

AND

Thanks for the funny (but true) "comments" in the screencast itself, you just made my day ^^

Report as Spam

2. daG Sep 28, 2009 at 00:46

You can add this 2 lines

require 'hirb'
Hirb::View.enable

to your environment.rb :)

Report as Spam

3. Jeremy Sep 28, 2009 at 00:53

Great screencast as usual !

Though I have one question about your groups show view. How does this work ?

<%= render @group.comments %>

I never saw anything like this and the rails doc about render don't mention it.

Thanks btw !

Report as Spam

4. Michael Sep 28, 2009 at 00:54

Thanks for the screencast! I've been wondering when to use either :join or :include. Good stuff. :)

Report as Spam

5. benvds Sep 28, 2009 at 02:04

Really great screencast. Haven't really thought about the consequences of include and joins before.

Report as Spam

6. kiko Sep 28, 2009 at 03:34

Sorry for asking such a non related question , but how could u show the results in console in columns and showing queries too ? i was running the application and showing results after hitting console b4 , any help would b highly appreciated ....

Report as Spam

7. EppO Sep 28, 2009 at 04:59

got a cold Ryan ?

thanks for your useful tips, I really like your "scoped" trick for chaining custom methods.

Report as Spam

8. UVSoft Sep 28, 2009 at 05:06

What is that scoped method? Cant find it in the api

Report as Spam

9. Hakan Ensari Sep 28, 2009 at 05:44

To nest has_many through relationships, try passing the distinct keyword to the select query:

http://ultra.bohe.me/post/199125574/

Report as Spam

10. Heli Sep 28, 2009 at 07:16

Great episode like always.

Haven't heard about the about the 'scoped' method before. Thanks

Report as Spam

11. Noah Hendrix Sep 28, 2009 at 10:16

Another great screen cast, hope you feel better soon!

Report as Spam

12. Artemich Sep 28, 2009 at 12:21

Thank you for the code, it's nice and useful

Report as Spam

13. thinkbohemian Sep 28, 2009 at 16:48

Going back to basics, how did you get your user model into irb? I can do this easily with script/console but if I try User.all i get an uninitialized constant "User" error. Is there a way to have IRB auto load your models like script/console ??

Report as Spam

14. Scott Sep 28, 2009 at 17:31

@kiko The very top of the code Ryan posted in the show notes should cause the DB queries to show... I think this is the line you need:

ActiveRecord::Base.logger = Logger.new(STDOUT) # to show logs

Take a look at the link to Hirb in the show notes or at daG's comment to show the DB query results as a table.

Report as Spam

15. Francois Sep 28, 2009 at 20:46

Thanks for another great screencast Ryan. You know, I actually look forward to Mondays because of you.

Report as Spam

16. Justin Ko Sep 28, 2009 at 21:53

Why is the scoped method not documented?

Report as Spam

17. Georg Ledermann Sep 29, 2009 at 08:50

Ryan, there is an important difference between :include and :joins that you didn't mentioned:

:include does a LEFT JOIN, whereas :joins does an INNER JOIN. Because an INNER JOIN requires matches in both tables, it can NOT be used if the second table has no entries for some records of the first table - because they will be filtered out.

So, IMHO your example "User.all(:joins => :comments, ..." is not a good one. The result will hide users which have no comments at all, so it doesn't result a full user list. In this example you should use :include instead of :joins.

Report as Spam

18. Jose Cortinas Sep 29, 2009 at 12:16

Georg(e)? above is absolutely correct. Thank you by the way Ryan. This was exactly what I was looking for at this exact time, your screencasts are always great! Keep it up!

Report as Spam

19. Pulkit Sep 30, 2009 at 06:14

Hi Ryan,

Again nice screencast!!!

BTW I totally agree with @John.

Please add some captcha like things to stop spamming.

one personal question I want to ask you that have you ever think to change CSS of railscasts.com?

Though it is nice but yet it is same as when it launched.

Report as Spam

20. Gerbert Sep 30, 2009 at 23:55

@Georg
Joins allows a SQL fragment, so you could write :joins => "LEFT JOIN comments ON ...", according to the Rails documentation.

Report as Spam

21. jeremyvdw Oct 09, 2009 at 17:00

User.all(:joins => :comments, :select => "users.*, count(comments.id) as comments_count", :group => "users.id")

Ouch, don't do that or your code will not work on Oracle for example, in proper SQL, "group by" expression must contain ALL columns not in computed value (SUM, COUNT, etc..)

Report as Spam

22. Casen Oct 20, 2009 at 11:28

I noticed that if you use a select that joins the users table and the comments table, and create a comments_count attribute for accessing the number of comments for each user, there is a problem:

If that user has no comments, they will not be selected. Is there an obvious way around this? I imagine the select method just shouldn't be used if you want to find users who have not commented....

Report as Spam

23. Vasyak Nov 26, 2009 at 05:15

Я не понял что это за язык??
Хотя я знаю много языков программирования

Report as Spam

24. Alexsander Dec 12, 2009 at 14:30

Very useful - I agree 100%!

Report as Spam

25. Cagan Dec 21, 2009 at 09:56

very useful screencast. thanks.

Report as Spam

26. yeni müzik Dec 29, 2009 at 15:49

This is one of those "clever" solutions that will only cause