Joins with :rails

joins.rb

11.2.1 Joining a Single Association

Category.joins(:posts)
This produces:

SELECT categories.* FROM categories
  INNER JOIN posts ON posts.category_id = categories.id
Or, in English: “return a Category object for all categories with posts”. Note that you will see duplicate categories if more than one post has the same category. If you want unique categories, you can use Category.joins(:post).select(“distinct(categories.id)”).

11.2.2 Joining Multiple Associations

Post.joins(:category, :comments)
This produces:

SELECT posts.* FROM posts
  INNER JOIN categories ON posts.category_id = categories.id
  INNER JOIN comments ON comments.post_id = posts.id
Or, in English: “return all posts that have a category and at least one comment”. Note again that posts with multiple comments will show up multiple times.

11.2.3 Joining Nested Associations (Single Level)

Post.joins(:comments => :guest)
This produces:

SELECT posts.* FROM posts
  INNER JOIN comments ON comments.post_id = posts.id
  INNER JOIN guests ON guests.comment_id = comments.id
Or, in English: “return all posts that have a comment made by a guest.”

11.2.4 Joining Nested Associations (Multiple Level)

Category.joins(:posts => [{:comments => :guest}, :tags])
This produces:

SELECT categories.* FROM categories
  INNER JOIN posts ON posts.category_id = categories.id
  INNER JOIN comments ON comments.post_id = posts.id
  INNER JOIN guests ON guests.comment_id = comments.id
  INNER JOIN tags ON tags.post_id = posts.id
11.3 Specifying Conditions on the Joined Tables
You can specify conditions on the joined tables using the regular Array and String conditions. Hash conditions provides a special syntax for specifying conditions for the joined tables:

time_range = (Time.now.midnight - 1.day)..Time.now.midnight
Client.joins(:orders).where('orders.created_at' => time_range)
An alternative and cleaner syntax is to nest the hash conditions:

time_range = (Time.now.midnight - 1.day)..Time.now.midnight
Client.joins(:orders).where(:orders => {:created_at => time_range})
This will find all clients who have orders that were created yesterday, again using a BETWEEN SQL expression.


fonte: http://guides.rubyonrails.org/active_record_querying.html