Rails Query: Filter by properties in another table

Question

I'm seeking a clear Rails 4 example on how to filter records based on data associated to them via another table.

Suppose I have a Users model and a Comments model. A User has_many comments, and a Comment belongs_to a user. A comment also has a score column in its table.

class User < ActiveRecord::Base
  has_many :comments
end

 

 Users
| id  | name    | email               |
|-----|---------|---------------------|
| 1   | "Alice" | "[email protected]" |
| 2   | "Bob"   | "[email protected]"   |
| ...                                 |

 

class Comment < ActiveRecord::Base
  belongs_to :user
end 

 Comments
| id  | score | content          | user_id |
|-----|-------|------------------|---------|
| 1   | 0     | "lol"            | 2       |
| 2   | 2     | "more like love" | 3       |
| ...                                      |

How would I obtain all users that have made a comment with the content "k" that has a score > 0? Note that what I want to return is the Users, not the Comments.

---

Also, consider a more complex example, where a User has_many comments and likes, Comments belong_to a user, and a Comment has_many Likes. Likes belong_to a User and belong_to a Comment. Note that score is no longer a factor in this example.

class User < ActiveRecord::Base
  has_many :comments
  has_many :likes
end

 

 Users
| id  | name    | email               |
|-----|---------|---------------------|
| 1   | "Alice" | "[email protected]" |
| 2   | "Bob"   | "[email protected]"   |
| ...                                 |

 

class Comment < ActiveRecord::Base
  belongs_to :user
  has_many :likes
end

 

 Comments
| id  | content          | user_id |
|-----|------------------|---------|
| 1   | "lol"            | 2       |
| 2   | "more like love" | 3       |
| ...                              |

 

class Like < ActiveRecord::Base
  belongs_to :user
  belongs_to :comment
end

 

 Likes
| id  | user_id | comment_id |
|-----|---------|------------|
| 1   | 1       | 2          |
| 2   | 4       | 3          |
| ...                        |

In this second example, how would I find all the Users who have ever had one of their comments liked by the User named 'Fonzie'?

Answer 1

To answer your first question. You need to create a new tabular structure that references the association between the users and comments table.

This can be achieved by User.joins(:comments). Now you have a table that has all the users with their associated comments. To apply your filters you can simply do:

User.joins(:comments) .where("comments.content = ? AND comments.score > ?", 'some_content', 0)

If you are not familiar with the above, I suggest you read the rails guidelines on queries - search for 'Joining Tables'

Since the second example is a bit more complex, I'd suggest that you familiarise yourself first with the guides above.