Complete Guide to SQL to ActiveRecord Conversion

1. Introduction to ActiveRecord ORM

ActiveRecord is Rails' Object-Relational Mapping (ORM) system that allows you to interact with your database using Ruby code instead of raw SQL. While SQL is powerful and direct, ActiveRecord provides a more Ruby-like interface that's easier to maintain, test, and refactor.

Why Convert SQL to ActiveRecord?

• Database Agnosticism: ActiveRecord queries work across different database systems (PostgreSQL, MySQL, SQLite)
• Security: Built-in protection against SQL injection attacks
• Maintainability: Ruby code is easier to read and maintain than complex SQL
• Testing: ActiveRecord queries can be easily mocked and tested
• Association Magic: Leverage Rails associations for cleaner code

2. Basic Query Conversions (SELECT, WHERE, ORDER BY)

Simple SELECT Statements

The most basic SQL queries translate directly to ActiveRecord method calls:

SELECT * FROM users;

User.all

WHERE Clauses

WHERE conditions in SQL become method arguments in ActiveRecord:

SELECT * FROM users WHERE active = true;

User.where(active: true)

SELECT * FROM users 
WHERE age > 18 AND city = 'NYC';

User.where(city: 'NYC')
    .where('age > ?', 18)

SELECT * FROM users 
WHERE id IN (1, 2, 3, 4, 5);

User.where(id: [1, 2, 3, 4, 5])

ORDER BY and LIMIT

Sorting and limiting results follow intuitive ActiveRecord patterns:

SELECT * FROM posts 
ORDER BY created_at DESC;

Post.order(created_at: :desc)

SELECT * FROM products 
LIMIT 10 OFFSET 20;

Product.limit(10).offset(20)

3. JOIN Operations and Associations

This is where ActiveRecord truly shines. Instead of writing complex JOIN syntax, you can leverage Rails associations for cleaner, more maintainable code.

INNER JOINs

SELECT users.*, posts.* 
FROM users 
JOIN posts ON users.id = posts.user_id;

User.joins(:posts)
    .select('users.*, posts.*')

SELECT users.* 
FROM users 
JOIN posts ON users.id = posts.user_id 
WHERE posts.published = true;

User.joins(:posts)
    .where(posts: { published: true })

LEFT JOINs

LEFT JOINs include all records from the left table, even if there's no match in the right table:

SELECT users.* 
FROM users 
LEFT JOIN posts ON users.id = posts.user_id;

User.left_joins(:posts)

Multiple JOINs

For complex queries involving multiple tables:

SELECT users.name, posts.title, comments.body
FROM users
JOIN posts ON users.id = posts.user_id
JOIN comments ON posts.id = comments.post_id;

User.joins(posts: :comments)
    .select('users.name, posts.title, 
             comments.body')

4. Subquery Handling

Subqueries can often be replaced with more elegant ActiveRecord solutions, but sometimes you need the raw power of a subquery.

EXISTS Subqueries

SELECT * FROM users 
WHERE EXISTS (
  SELECT 1 FROM posts 
  WHERE posts.user_id = users.id 
  AND posts.published = true
);

User.where(id: 
  Post.where(published: true)
      .select(:user_id)
)

IN Subqueries

SELECT * FROM users 
WHERE id IN (
  SELECT user_id FROM orders 
  WHERE total > 1000
);

User.where(id: 
  Order.where('total > ?', 1000)
       .select(:user_id)
)

5. Aggregate Functions

ActiveRecord provides clean methods for all common SQL aggregate functions:

Basic Aggregations

SELECT COUNT(*) FROM users;

User.count

SELECT SUM(amount) FROM orders;

Order.sum(:amount)

GROUP BY Operations

SELECT user_id, COUNT(*) 
FROM posts 
GROUP BY user_id;

Post.group(:user_id).count

SELECT user_id, COUNT(*) 
FROM posts 
GROUP BY user_id 
HAVING COUNT(*) > 5;

Post.group(:user_id)
    .having('COUNT(*) > 5')
    .count

6. Advanced Patterns (UNION, EXISTS, Window Functions)

UNION Operations

While ActiveRecord doesn't have built-in UNION support, you can use raw SQL for complex scenarios:

SELECT id, name FROM users 
UNION 
SELECT id, name FROM customers;

User.from(
  "(#{User.select(:id, :name).to_sql} 
   UNION 
   #{Customer.select(:id, :name).to_sql}) 
   AS combined"
)

Complex WHERE Conditions

SELECT * FROM orders 
WHERE created_at BETWEEN 
'2023-01-01' AND '2023-12-31';

Order.where(
  created_at: 
    Date.new(2023,1,1)..Date.new(2023,12,31)
)

SELECT * FROM users 
WHERE email IS NOT NULL;

User.where.not(email: nil)

7. Performance Considerations

Understanding when ActiveRecord helps or hurts performance is crucial for building scalable applications.

N+1 Query Problems

# This generates N+1 queries!
users = User.all
users.each { |user| puts user.posts.count }

# This uses only 2 queries total
users = User.includes(:posts)
users.each { |user| puts user.posts.count }

When to Use Raw SQL

Sometimes raw SQL is the better choice:

• Complex Window Functions: Advanced analytics queries
• Database-Specific Features: PostgreSQL arrays, MySQL JSON functions
• Performance-Critical Paths: Highly optimized queries
• Complex Reporting: Multi-table aggregations with complex logic

8. Best Practices and Common Pitfalls

Do's

• Use includes/preload: Prevent N+1 queries
• Chain methods logically: User.active.recent.limit(10)
• Use scopes: Extract common query patterns
• Leverage associations: Let Rails handle the JOINs
• Use find_each: For processing large datasets

Don'ts

• Don't use string interpolation: Risk of SQL injection
• Don't ignore the SQL log: Monitor generated queries
• Don't over-abstract: Sometimes raw SQL is clearer
• Don't forget indexes: ActiveRecord can't fix missing indexes