Active Record (AR) is the heart of Ruby on Rails when it comes to database interactions. Writing efficient and readable queries is crucial for application performance and maintainability. This guide will help you master Active Record queries with real-world examples and best practices.
Setting Up a Sample Database
To demonstrate complex Active Record queries, let’s create a Rails app with a sample database structure containing multiple tables.
Generate Models & Migrations
rails new MyApp --database=postgresql
cd MyApp
rails g model User name:string email:string
rails g model Post title:string body:text user:references
rails g model Comment body:text user:references post:references
rails g model Category name:string
rails g model PostCategory post:references category:references
rails g model Like user:references comment:references
rails db:migrate
Database Schema Overview
users: Stores user information.posts: Stores blog posts written by users.comments: Stores comments on posts, linked to users and posts.categories: Stores post categories.post_categories: Join table for posts and categories.likes: Stores likes on comments by users.
Basic Active Record Queries
1. Fetching All Records
User.all # Returns all users (Avoid using it directly on large datasets as it loads everything into memory)
⚠️ User.all can lead to performance issues if the table contains a large number of records. Instead, prefer pagination (User.limit(100).offset(0)) or batch processing (User.find_each).
2. Finding a Specific Record
User.find(1) # Finds a user by ID
User.find_by(email: 'john@example.com') # Finds by attribute
3. Filtering with where vs having
Post.where(user_id: 2) # Fetch all posts by user with ID 2
Difference between where and having:
whereis used for filtering records before grouping.havingis used for filtering aftergroupoperations.
Example:
Post.group(:user_id).having('COUNT(id) > ?', 5) # Users with more than 5 posts
4. Ordering Results
User.order(:name) # Order users alphabetically
Post.order(created_at: :desc) # Order posts by newest first
5. Limiting Results
Post.limit(5) # Get the first 5 posts
6. Selecting Specific Columns
User.select(:id, :name) # Only fetch ID and name
7. Fetching Users with a Specific Email Domain
User.where("email LIKE ?", "%@gmail.com")
8. Fetching the Most Recent Posts
Post.order(created_at: :desc).limit(5)
9. Using pluck for Efficient Data Retrieval
User.pluck(:email) # Fetch only emails as an array
10. Checking if a Record Exists Efficiently
User.exists?(email: 'john@example.com')
11. Including Associations (eager loading to avoid N+1 queries)
Post.includes(:comments).where(comments: { body: 'Great post!' })
Advanced Queries with Joins
1. Joining Tables (INNER JOIN)
Post.joins(:user).where(users: { name: 'John' })
2. Self Join Example
A self-join is useful when dealing with hierarchical relationships, such as an employee-manager structure.
Model Setup
class Employee < ApplicationRecord
belongs_to :manager, class_name: 'Employee', optional: true
has_many :subordinates, class_name: 'Employee', foreign_key: 'manager_id'
end
Sample Data
| id | name | manager_id |
|---|---|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Carol | 1 |
| 4 | Dave | 2 |
Query: Find Employees Who Report to Alice
Employee.joins(:manager).where(managers_employees: { name: 'Alice' })
Result:
| id | name | manager_id |
|---|---|---|
| 2 | Bob | 1 |
| 3 | Carol | 1 |
This query fetches employees who report to Alice (i.e., those where manager_id = 1).
3. Fetching Users with No Posts (LEFT JOIN with NULL check)
User.left_outer_joins(:posts).where(posts: { id: nil })
4. Counting Posts Per User
User.joins(:posts).group('users.id').count
Complex Queries in Active Record
1. Fetching Posts with the Most Comments
Post.joins(:comments)
.group('posts.id')
.order('COUNT(comments.id) DESC')
.limit(1)
2. Fetching Posts with More than 5 Comments
Post.joins(:comments)
.group(:id)
.having('COUNT(comments.id) > ?', 5)
3. Finding Users Who Liked the Most Comments
User.joins(comments: :likes)
.group('users.id')
.select('users.id, users.name, COUNT(likes.id) AS likes_count')
.order('likes_count DESC')
.limit(1)
4. Fetching Posts Belonging to Multiple Categories
Post.joins(:categories).group('posts.id').having('COUNT(categories.id) > ?', 1)
5. Fetching the Last Comment of Each Post
Comment.select('DISTINCT ON (post_id) *').order('post_id, created_at DESC')
6. Fetching Users Who Haven’t Commented on a Specific Post
User.where.not(id: Comment.where(post_id: 10).select(:user_id))
7. Fetching Users Who Have Commented on Every Post
User.joins(:comments).group(:id).having('COUNT(DISTINCT comments.post_id) = ?', Post.count)
8. Finding Posts With No Comments
Post.left_outer_joins(:comments).where(comments: { id: nil })
9. Fetching the User Who Created the Most Posts
User.joins(:posts)
.group('users.id')
.select('users.id, users.name, COUNT(posts.id) AS post_count')
.order('post_count DESC')
.limit(1)
10. Fetching the Most Liked Comment
Comment.joins(:likes)
.group('comments.id')
.order('COUNT(likes.id) DESC')
.limit(1)
11. Fetching Comments with More than 3 Likes and Their Associated Posts
Comment.joins(:likes, :post)
.group('comments.id', 'posts.id')
.having('COUNT(likes.id) > ?', 3)
12. Finding Users Who Haven’t Liked Any Comments
User.left_outer_joins(:likes).where(likes: { id: nil })
13. Fetching Users, Their Posts, and the Count of Comments on Each Post
User.joins(posts: :comments)
.group('users.id', 'posts.id')
.select('users.id, users.name, posts.id AS post_id, COUNT(comments.id) AS comment_count')
.order('comment_count DESC')
Importance of inverse_of in Model Associations
What is inverse_of?
The inverse_of option in Active Record associations helps Rails correctly link objects in memory, avoiding unnecessary database queries and ensuring bidirectional association consistency.
Example Usage
class User < ApplicationRecord
has_many :posts, inverse_of: :user
end
class Post < ApplicationRecord
belongs_to :user, inverse_of: :posts
end
Why Use inverse_of?
- Performance Optimization: Prevents extra queries by using already loaded objects.
- Ensures Data Consistency: Updates associations without additional database fetches.
- Enables Nested Attributes: Helps when using
accepts_nested_attributes_for.
Example:
user = User.new(name: 'Alice')
post = user.posts.build(title: 'First Post')
post.user == user # True without needing an additional query
Best Practices to use in Rails Projects
1. Using Scopes for Readability
class Post < ApplicationRecord
scope :recent, -> { order(created_at: :desc) }
end
Post.recent.limit(10) # Fetch recent posts
2. Using find_each for Large Datasets
User.find_each(batch_size: 100) do |user|
puts user.email
end
3. Avoiding SELECT * for Performance
User.select(:id, :name).load
4. Avoiding N+1 Queries with includes
Post.includes(:comments).each do |post|
puts post.comments.count
end
Conclusion
Mastering Active Record queries is essential for writing performant and maintainable Rails applications. By using joins, scopes, batch processing, and eager loading, you can write clean and efficient queries that scale well.
Do you have any favorite Active Record query tricks? Share them in the comments!
The Rails Drop 