#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.
Download (22.8 MB, 11:37)
alternative download for iPod & Apple TV (16.2 MB, 11:37)

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" %>

RSS Feed for Episode Comments 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 ^^


2. daG Sep 28, 2009 at 00:46

You can add this 2 lines

require 'hirb'
Hirb::View.enable

to your environment.rb :)


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 !


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. :)


5. benvds Sep 28, 2009 at 02:04

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


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 ....


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.


8. UVSoft Sep 28, 2009 at 05:06

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


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/


10. Heli Sep 28, 2009 at 07:16

Great episode like always.

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


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

Another great screen cast, hope you feel better soon!


12. Artemich Sep 28, 2009 at 12:21

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


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 ??


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.


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.


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

Why is the scoped method not documented?


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.


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!


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.


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.


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..)


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....


23. Vasyak Nov 26, 2009 at 05:15

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


24. Cagan Dec 21, 2009 at 09:56

very useful screencast. thanks.


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.


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.


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/"><strong>Nike Free Run</strong></a><p>
<a href="http://www.airmaxgoshopping.com/nike-free-run-c-44.html"><strong>Nike Free Run+Running</strong></a>
<a href="http://www.airmaxgoshopping.com/"><strong>Nike Lunar Glide</strong></a><p>
 are lightweight. The style is a fan of sports and athletes governors. The word is not enough, we enjoy your column shoes air max.


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>


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

this is a tahnk you


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


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.


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.


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.


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

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


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.


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

your post are very helpful for me
look forward more posts


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.


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


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

thane you are


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

your post are very helpful for me
look forward more posts


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

good job,good article


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!


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

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


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


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


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.


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

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


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.

Add your comment:

(SKIP THIS ONE)

(required)

(not shown)


(use pastie or gist for code)

sponsored by:
if you want to help:
required:
Get Quicktime Player
Give Back to Open Source