Raw SQL when needed in Ruby on Rails - Step-by-Step Execution

Concept Flow - Raw SQL when needed

Start ActiveRecord Query

↓

Check if complex query

↓

Use raw SQL

↓

Execute SQL

↓

Return Results

This flow shows when Rails uses raw SQL for complex queries and ActiveRecord for simpler ones, then executes and returns results.

Execution Sample

Ruby on Rails

User.find_by_sql("SELECT * FROM users WHERE age > 30")

This code runs a raw SQL query to get users older than 30.

Execution Table

Step	Action	SQL/AR Query	Execution Result	Notes
1	Call find_by_sql	SELECT * FROM users WHERE age > 30	Query prepared	Raw SQL string passed
2	Execute SQL	SELECT * FROM users WHERE age > 30	Database returns matching users	Direct SQL execution
3	Map results	N/A	User objects created	Results converted to model instances
4	Return results	N/A	Array of User objects	Ready for use in app
5	End	N/A	Process complete	No further action

💡 All matching users older than 30 are returned as User objects

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
sql_query	N/A	SELECT * FROM users WHERE age > 30	SELECT * FROM users WHERE age > 30	SELECT * FROM users WHERE age > 30
results_raw	N/A	DB rows matching age > 30	DB rows matching age > 30	DB rows matching age > 30
results_mapped	N/A	N/A	Array of User objects	Array of User objects

Key Moments - 2 Insights

Why do we use raw SQL instead of ActiveRecord methods here?

Because the query is complex or not easily expressed with ActiveRecord, raw SQL gives full control. See execution_table step 1 where raw SQL is passed.

Are the results from raw SQL still Rails model objects?

Yes, after execution, results are mapped to User objects as shown in execution_table step 3.

Visual Quiz - 3 Questions

Look at the execution_table, what happens at step 2?

A The raw SQL query is executed on the database

B The results are mapped to User objects

C The ActiveRecord query is built

D The method returns the results

💡 Hint

Check the 'Execution Result' column at step 2 in the execution_table

According to variable_tracker, what is the value of results_mapped after step 3?

A N/A

B Database rows matching age > 30

C Array of User objects

D SQL query string

💡 Hint

Look at the 'results_mapped' row under 'After Step 3' in variable_tracker

If we used ActiveRecord instead of raw SQL, which step would be skipped?

A Step 3: Map results

B Step 1: Call find_by_sql

C Step 2: Execute SQL

D Step 4: Return results

💡 Hint

Raw SQL is passed only in step 1; ActiveRecord builds queries differently

Concept Snapshot

Raw SQL in Rails:
Use find_by_sql("SQL") for complex queries.
Executes SQL directly on DB.
Maps results to model objects.
Use when ActiveRecord can't express query.
Returns array of model instances.

Full Transcript

In Rails, sometimes you need to write raw SQL for complex queries that ActiveRecord can't handle easily. You use the method find_by_sql with a SQL string. Rails sends this SQL directly to the database and gets back rows. Then Rails converts these rows into model objects so you can use them like normal. This process involves calling find_by_sql, executing the SQL, mapping results, and returning them. This lets you write powerful queries while still working with Rails models.