Rails 7.1: Mastering ActiveRecord `.with` for Efficient Database Queries

Are you ready to write cleaner, faster, and more maintainable database queries in your Ruby on Rails applications? Rails 7.1 introduces a game-changing feature for Ruby on Rails developers: the .with query method. This article dives deep into how you can leverage Common Table Expressions (CTEs) directly within ActiveRecord, boosting your application's performance and code readability.

What are Common Table Expressions (CTEs) and Why Should Rails Developers Care?

Common Table Expressions (CTEs) are like temporary, named result sets that exist only for the duration of a single query. For Ruby on Rails developers, they provide several key advantages when dealing with complex data manipulations:

Improved Readability: Break down complex queries into smaller, logical units.
Better Organization: Make complicated SQL easier to understand and maintain.
Code Reusability: Reference the same subquery multiple times without repetition, adhering to the DRY principle.
Enhanced Performance: In many cases, databases can optimize CTEs more effectively than nested subqueries.

Unleash the Power of `.with`: A Ruby on Rails Developer's Guide

Rails 7.1's .with method allows you to define CTEs directly within your ActiveRecord queries. Here's how to get started:

Basic CTE Example with `.with` in Rails

# Add a CTE named 'posts_with_tags' that selects posts with tags
Post.with(posts_with_tags: Post.where(tags_count: 1..))
# => ActiveRecord::Relation
# WITH posts_with_tags AS (
# SELECT * FROM posts WHERE (tags_count > 0)
# )
# SELECT * FROM posts

This snippet defines a CTE called posts_with_tags that selects all posts with at least one tag.

Using CTEs in `FROM` and `JOIN` Clauses: Deeper Dive for Rails

Once you've defined a CTE, you can reference it in your FROM or JOIN clauses:

# Using a CTE in a FROM clause in Ruby on Rails
Post.with(posts_with_tags: Post.where(tags_count: 1..))
  .from("posts_with_tags AS posts")
# => ActiveRecord::Relation
# WITH posts_with_tags AS (
# SELECT * FROM posts WHERE (tags_count > 0)
# )
# SELECT * FROM posts_with_tags AS posts

# Using a CTE in a JOIN clause
Post.with(posts_with_tags: Post.where(tags_count: 1..))
  .joins("JOIN posts_with_tags ON posts_with_tags.id = posts.id")
# => ActiveRecord::Relation
# WITH posts_with_tags AS (
# SELECT * FROM posts WHERE (tags_count > 0)
# )
# SELECT * FROM posts JOIN posts_with_tags ON posts_with_tags.id = posts.id

This example shows how to use the posts_with_tags CTE in both a FROM and a JOIN clause, demonstrating its flexibility.

Combining Sub-queries with `UNION ALL` for Rails Projects

You can combine multiple sub-queries within a CTE using UNION ALL:

Post.with(
  posts_with_tags_or_comments: [
    Post.where(tags_count: 1..),
    Post.where(comments_count: 1..)
  ]
)
# => ActiveRecord::Relation
# WITH posts_with_tags_or_comments AS (
# (SELECT * FROM posts WHERE (tags_count > 0))
# UNION ALL
# (SELECT * FROM posts WHERE (comments_count > 0))
# )
# SELECT * FROM posts

This creates a CTE that combines posts with tags and posts with comments using UNION ALL.

Real-World Examples of `.with` in Ruby on Rails Applications

Let's explore some practical scenarios where CTEs can significantly improve your Rails code:

Finding Users with Articles: A Rails Use Case

# First, create a CTE for users who have articles
users_with_articles = User.joins(:articles).select(:id).distinct
# Then, use the CTE in our main query
result = User.with(users_with_articles: users_with_articles)
  .joins("JOIN users_with_articles ON users.id = users_with_articles.id")
  .select(:id, :name)

This query efficiently finds all users who have written at least one article.

Working with Multiple CTEs for Complex Rails Queries

You can define multiple CTEs in a single query using two methods:

# Method 1: Pass multiple key-value pairs
Post.with(
  posts_with_comments: Post.where(comments_count: 1..),
  posts_with_tags: Post.where(tags_count: 1..)
)

# Method 2: Chain multiple .with calls
Post.with(posts_with_comments: Post.where(comments_count: 1..))
  .with(posts_with_tags: Post.where(tags_count: 1..))

Combining Multiple CTEs in a Complex Query: Rails Example

recent_articles = Article.where(created_at: 1.month.ago..).select(:id, :user_id)
active_users = User.where(active: true).select(:id)

result = User.with(
  recent_articles: recent_articles,
  active_users: active_users
)
  .joins("JOIN recent_articles ON users.id = recent_articles.user_id")
  .joins("JOIN active_users ON users.id = active_users.id")
  .distinct

This example combines two CTEs to find active users who have written recent articles.

Advanced ActiveRecord Techniques

Using Raw SQL inside CTEs

You can also use raw SQL inside your CTEs in Rails:

# Only use when you've verified it's safe
Post.with(popular_posts: Arel.sql("... complex sql to calculate posts popularity ..."))

Warning: Be extremely cautious about SQL injection vulnerabilities when using raw SQL.

Calculating Comment Likes and Sorting: A Dynamic Rails Example

# Create a CTE that calculates the total likes for each comment
comment_likes = Comment.select(:id, 'COUNT(likes.id) AS likes_count')
  .left_joins(:likes)
  .group(:id)

# Use the CTE in the main query to sort comments by popularity
popular_comments = Comment.with(comment_likes: comment_likes)
  .joins("JOIN comment_likes ON comments.id = comment_likes.id")
  .select('comments.*, comment_likes.likes_count')
  .order('comment_likes.likes_count DESC')
  .limit(10)

This query finds the 10 most liked comments by calculating the total likes for each comment in a CTE.

Building a Hierarchical Category Tree: Recursive Queries in Rails

Recursive CTEs are perfect for working with hierarchical data. Here's how to display a complete category tree:


# Base query: select top-level categories
base_categories = Category.where(parent_id: nil)
  .select(:id, :name, :parent_id, 'ARRAY[id] AS path', '0 AS depth')

# Recursive part: join child categories
child_categories = Category.select('categories.id, categories.name, categories.parent_id,(categories_tree.path || categories.id) AS path, categories_tree.depth + 1 AS depth')
  .joins('JOIN categories_tree ON categories.parent_id = categories_tree.id')

# Combine into a recursive CTE
categories_tree = base_categories.recursive_union(child_categories)

# Use the recursive CTE
result = Category.with.recursive(categories_tree: categories_tree)
  .from('categories_tree')
  .order('path')

# This will return all categories with their depth and path information

More Rails 7.1 and ActiveRecord Examples

Here are a few more ideas to get your creative juices flowing:

Finding Active Users with Recent Posts and Comment Counts: Combine multiple datasets to gain insights into user activity.
Paginated Search Results with Ranking: Implement advanced search logic with ranking algorithms.
Calculating Running Totals for Financial Reports: Generate complex financial reports with running totals.
Finding Gaps in Sequential Data: Identify missing values in sequential data.

Recursive CTEs in Rails 7.1

# Base case
base_query = Comment.where(parent_id: nil)
  .select(:id, :parent_id, :content, '0 as depth')

# Recursive part
recursive_query = Comment.select('comments.id, comments.parent_id, comments.content, depth + 1')
  .joins('JOIN comments_hierarchy ON comments.parent_id = comments_hierarchy.id')

# Combine using RECURSIVE
comments_hierarchy = base_query.recursive_union(recursive_query)

# Use the recursive CTE
result = Comment.with.recursive(comments_hierarchy: comments_hierarchy)
  .from('comments_hierarchy')
  .order('depth, id')

Important Implementation Details for Rails Developers

The .with method is available in Rails 7.1 and later.
CTEs are supported by major database backends like PostgreSQL, MySQL 8.0+, and SQLite 3.8.3+.
You can use both recursive and non-recursive CTEs.

Conclusion: Elevate your Rails Queries with `.with` and CTEs

The .with query method in Rails 7.1 unlocks a new level of power and elegance for ActiveRecord queries. By mastering CTEs, you can write more maintainable, efficient, and readable code. Embrace this powerful tool and elevate your Ruby on Rails development skills.