Joining Tables

# Join Through Defined Associations (Inner Join):
Category.joins(:articles)
Article.joins(:category, :comments)

   SELECT articles.* FROM articles   INNER JOIN categories ON articles.category_id = categories.id   INNER JOIN comments ON comments.article_id = articles.id   

# Outer Joins:
Author.left_outer_joins(:posts).distinct.select('authors., COUNT(posts.) AS posts_count').group('authors.id')

   SELECT DISTINCT authors., COUNT(posts.) AS posts_count FROM "authors" LEFT OUTER JOIN posts ON posts.author_id = authors.id GROUP BY authors.id   

# N + 1 queries problem: Always use .includes()
Article.includes(:category, :comments).where(comments: { visible: true })

   Includes will decide between INNER JOIN eager_load (LOJ) or  Seperate Queries 

# Join Using Raw SQL:
Author.joins("INNER JOIN posts ON posts.author_id = authors.id AND posts.published = 't'")

   SELECT authors.* FROM authors INNER JOIN posts ON posts.author_id = authors.id AND posts.published = 't'   

# Retrieving filtered data from multiple tables: If you want to call order multiple times, subsequent orders will be appended to the first.
Person .select('people.id, people.name, comments.text') .joins(:comments) .where('comments.created_at > ?', 1.week.ago)

   SELECT people.id, people.name, comments.text FROM people INNER JOIN comments   ON comments.person_id = people.id WHERE comments.created_at > '2015-01-01' 

# Retrieving specific data from multiple tables:
Person .select('people.id, people.name, companies.name') .joins(:company) .find_by('people.name' => 'John') # this should be the last

   SELECT people.id, people.name, companies.name FROM people INNER JOIN companies   ON companies.person_id = people.id WHERE people.name = 'John' LIMIT 1