CODEWITHSUNDEEP
activerecordruby-on-rails-4
Maninder Reddy
Maninder Reddy

Reputation: 107

Rails join on 2 tables

I have 3 tables which are

class User < ActiveRecord::Base
   has_many :posts
end

class Post < ActiveRecord::Base
   belongs_to :user   
   has_many :comments
end

class Comment < ActiveRecord::Base
   belongs_to :post
end

Now I want to retrieve all the comments of a particular user. I have tried it using find_by_sql like

Comment.find_by_sql "SELECT c.* FROM comments c, posts p, users u WHERE c.post_id = p.id and p.user_id = u.id and u.id=6"

This works fine. But I want few details from "posts" table along with comments.

Does anyone have idea how to do that?

Upvotes: 0

Views: 41

Answers (1)

Paul Richter
Paul Richter

Reputation: 11072

Corrected answer..

Now that I've properly understood the scenario, a portion of my previous answer still makes sense with one addition:

Alter the User model like so:

class User < ActiveRecord::Base
  has_many :posts
  has_many :comments, through: :posts
end

This type of relationship will perform a join between comments a posts, where the user_id of posts is the current user. Quite literally, like you said in the comments, "the connection between User and Comment [is] through Post".

Similar to what I said before, the center of the universe in this scenario is the user object. With this addition, to grab all the user's comments would simply be:

user.comments

If you look at the log, the SQL output is:

SELECT `comments`.* FROM `comments` INNER JOIN `posts` ON `comments`.`post_id` = `posts`.`id` WHERE `posts`.`user_id` = 1

Which is similar to your original query.

Now that you retrieved the comments for this user, you can simply access the Post model data through the belongs_to :post relationship, as normal.

In the end, unless you have some special reason for trying to do everything in pure SQL, this approach is more readable, maintainable, and the "Rails way". Plus it saves yourself a lot of hassle and typing.

More information on has_many ... through.

Upvotes: 1

Related Questions