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 >

27 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. Cagan Dec 21, 2009 at 09:56

very useful screencast. thanks.

Report as Spam

25. <a href=http://www.4unfl.com> Indianapolis Colts Jerseys </a> Jun 27, 2010 at 05:35

Take advantage of 4unfl.com's exclusive online discounts and promotions on all of our men's and women's nfl jerseys, kids' and youth jerseys, and classic throwback football jerseys.

Report as Spam

26. Gary Jun 27, 2010 at 19:17

Three blonde women were stranded on an island. While trying to dig their way out, one of them came across a buried lamp. Suddenly a genie appears and offers to grant each one of them one wish, in return for saving him.

Report as Spam

27. shell Jul 10, 2010 at 02:07

We’re big fans and Nike Air Max 2010 shoes is the best. The response of cushioning and flexibility, while the <a href="http://www.airmaxgoshopping.com/">Nike Free Run</a>
<a href="http://www.airmaxgoshopping.com/nike-free-run-c-44.html">Nike Free Run+Running</a>
<a href="http://www.airmaxgoshopping.com/">Nike Lunar Glide</a>
are lightweight. The style is a fan of sports and athletes governors. The word is not enough, we enjoy your column shoes air max.

Report as Spam

28. webtasarim Jul 15, 2010 at 09:05

web tasarımı, kurumsal site tasarımı, profesyonel web sitesi tasarımı, profesyonel web tasarımı

<a href="http://www.webtasarimturk.net" title="web tasarımı">web tasarımı</a>

Report as Spam

29. iPhone Ringtone Maker for mac Jul 20, 2010 at 18:48

this is a tahnk you

Report as Spam

31. UGG Classic Argyle Knit Jul 29, 2010 at 03:17

　Great!This article is creative，there are a lot of new idea，it gives me inspiration.I think I will also inspired by you and think about more new ideas

Report as Spam

32. Classic Crochet Uggs 5833 Jul 29, 2010 at 03:18

　This is a good,common sense article.Very helpful to one who is just finding the resouces about this part.It will certainly help educate me.

Report as Spam

32. optical fiber Aug 01, 2010 at 23:51

What youre saying is completely true. I know that everybody must say the same thing, but I just think that you put it in a way that everyone can understand. I also love the images you put in here. They fit so well with what youre trying to say. Im sure youll reach so many people with what youve got to say.

Report as Spam

33. solar collector solar water heater Aug 01, 2010 at 23:52

Any member of your group can post to your trip blog. This is a great way to share information with your team and your<a rel="follow" href="http://www.ejaisolar.com/" rel="nofollow">solar water heater</a> supporters.

Report as Spam

34. solar collector solar water heater Aug 02, 2010 at 00:02

It's one of the most important facto to fix this time.

Report as Spam

35. cheap nike air max Aug 05, 2010 at 20:39

Thanks a lot for sharing. You have done a brilliant job. Your article is truly relevant to my study at this moment, and I am really happy I discovered your website. However, I would like to see more details about this topic. I'm going to keep coming back here.

Report as Spam

36. Jordan kicks Aug 11, 2010 at 19:09

your post are very helpful for me
look forward more posts

Report as Spam

37. 90x workout Aug 12, 2010 at 09:26

wow..its a great fun here....which i have done today through pdf format.Your blog is very much useful and rocking dear. you have provided detailed knowledge and information about today's latest technologies, online communications and other businesses.

Report as Spam

38. Jordan Air Retro Aug 14, 2010 at 02:13

Thanks a lot for sharing. You have done a brilliant job. Your article is truly relevant to my study at this moment, and I am really happy I discovered your website. your post are very helpful for me
look forward more posts

Report as Spam

39. jordan 6 rings Aug 16, 2010 at 20:46

thane you are

Report as Spam

40. replica jerseys Aug 17, 2010 at 19:51

your post are very helpful for me
look forward more posts

Report as Spam

41. supra tk society Aug 18, 2010 at 18:53

good job,good article

Report as Spam

42. wholesale new era caps Aug 20, 2010 at 20:23

Perhaps this is one of the most interesting blogs that I have ever seen. Interesting article, Funny comment. Keep it up!

Report as Spam

43. Wholesale Electronics Aug 25, 2010 at 01:36

Discount Wholesale Electronics, Wholesale Cell Phones, Electronic Gadgets and More from the Best Dropship Wholesaler

Report as Spam

44. cheap jersey boys tickets Aug 25, 2010 at 01:58

It’s hard to find knowledgeable people on this topic, but you sound like you know what you’re talking about! Thanks

Report as Spam

45. louis vuitton shoes Aug 26, 2010 at 20:57

Thanks for sharing your article. I really enjoyed it. I put a link to my site to here so other people can read it. My readers have about the same interets

Report as Spam

46. replicahandbags Aug 30, 2010 at 19:16

I have always liked Outdoor movies, a child standing at the window, looked out from home

to the following. Will be able to see the staff busy figure, a huge white cloth has a

child hang up and soon will be able to see the movie.

Report as Spam

47. snow boots Aug 30, 2010 at 20:46

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

Report as Spam

48. louis vuitton sunglasses Sep 01, 2010 at 21:38

I think this is a great post. One thing that I find the most helpful is number five. Sometimes when I write, I just let the flow of the words and information come out so much that I loose the purpose. It’s only after editing when I realize what I’ve done. There’s defiantly a lot of great tips here I’m going to try to be more aware of.

Report as Spam

Include vs Joins

27 comments

Add your comment: