In a Rails chess-club app, when would you write the ranking report with find_by_sql rather than a relation using select, with or from, and what do you give up?
answer
- Array, not Relation
- no chaining, no includes, no scopes
- select and with keep a relation
- Preloader for loaded records
- select_all when no models needed
basics
~20 sKeep a report as a relation while select, with, from or joins can express it, so it stays chainable and preloadable. Use find_by_sql when the SQL reads better whole; it returns a plain Array, so where, includes and scopes no longer apply.
solid answer
~40 sA ranking with a window function can often stay a relation: `Member.where(club: club).select("members.*", "RANK() OVER (ORDER BY rating DESC) AS position")`, with `with` for a CTE and `from` for a subquery. It still chains `where`, `limit`, scopes, `includes` and a pagination gem, and `to_sql` shows the query. `find_by_sql` earns its place when the statement is clearer as one block of SQL, such as several CTEs, a `UNION` or a database-specific clause, but it returns an `Array`: no further chaining, no `includes` (use `ActiveRecord::Associations::Preloader` on the loaded records), and unselected columns raise `ActiveModel::MissingAttributeError`. When the report only prints rows, `select_all` skips instantiation entirely. Either way, keep the SQL in one named query method with bound values and a test against the real adapter.
code
ruby · 18 linesclass Rankings::ClubStandings
SQL = <<~SQL
WITH recent AS (
SELECT white_id AS member_id FROM games WHERE played_on >= :since
UNION ALL
SELECT black_id FROM games WHERE played_on >= :since
)
SELECT members.*, COUNT(recent.member_id) AS games_played,
RANK() OVER (ORDER BY members.rating DESC) AS position
FROM members LEFT JOIN recent ON recent.member_id = members.id
WHERE members.club_id = :club_id
GROUP BY members.id
SQL
def self.call(club, since: 90.days.ago)
Member.find_by_sql([SQL, { club_id: club.id, since: since }])
end
endgo deeper
Know that find_by_sql returns an Array of models and cannot be chained, while select with an alias keeps a relation.
Explain how select, with, from and joins strings keep complex SQL inside a relation, and how Preloader attaches associations to records loaded by find_by_sql.
Argue the trade for a real report: composition and pagination against readability, the select_all option when no models are needed, and tests on the production adapter.
Set the team rule for raw SQL: named query objects, bound values, adapter-level tests, and a clear bar for when a hand-written statement is worth its maintenance cost.
## The question behind the question Interviewers ask when raw SQL is justified because both extremes hurt. Forcing a complex report through the relation builder produces chains of string fragments that nobody can read; dropping to raw SQL for everything throws away composition. The honest answer weighs **what the relation still gives you** against **how much SQL the builder can carry**. ## A relation can carry more SQL than people expect Active Record's query methods accept SQL fragments while keeping the result an `ActiveRecord::Relation`: - **`select`** takes expressions and aliases, including window functions: `select("members.*", "RANK() OVER (ORDER BY rating DESC) AS position")`. Each alias becomes a reader on the loaded records. - **`with`** adds a common table expression from a relation or `Arel.sql`, and `with_recursive` a recursive one. - **`from`** replaces the FROM clause with a subquery: `Member.from(subquery, :members)`. - **`joins`** accepts a SQL string for a join the associations do not describe. - **`order`** with `Arel.sql` handles a computed sort. ```ruby standings = Member .where(club: club) .select("members.*", "RANK() OVER (ORDER BY rating DESC) AS position") .order(rating: :desc) standings.includes(:title_norms).limit(20) # still a relation standings.to_sql # inspect the SQL ``` What staying a relation buys: 1. **Composition**: scopes, extra `where` clauses, `limit` and `offset`, and pagination gems keep working. 2. **Loading**: `includes`, `preload` and `eager_load` attach associations to the records. 3. **Laziness**: nothing runs until the relation is read, and `load_async` can start it early. 4. **Inspection**: `to_sql` and `explain` show what will run. ## When find_by_sql is the better tool `find_by_sql` takes the whole statement as one string (or `[sql, values]`) and returns model instances. It is justified when: - the SQL is clearer as one block than as fragments: several CTEs feeding each other, a `UNION` of two rating pools, a lateral join; - the statement uses a feature of one database that the builder has no method for, in an app already tied to that database; - the query is written and tuned by someone reading the SQL directly, and fragments would hide its shape. ## What you give up | Concern | Relation | `find_by_sql` | |---|---|---| | Return type | `ActiveRecord::Relation` | `Array` of records | | Chain `where`, `limit`, scopes | yes | no: `Array` has no `where` | | `includes` / `preload` | yes | no: use `ActiveRecord::Associations::Preloader` | | Pagination gems | yes | not on an `Array` without extra work | | Unselected columns | missing, raise on read | missing, raise on read | | Computed columns | readers via `select` aliases | readers on each record | Loaded records can still get associations without an N+1: ```ruby members = Member.find_by_sql([REPORT_SQL, { club_id: club.id }]) ActiveRecord::Associations::Preloader.new(records: members, associations: :games).call ``` Calling `Member.includes(:games).find_by_sql(sql)` does not do this: `find_by_sql` runs exactly the SQL it is given, and the relation's `includes` is not applied to its records. ## When no models are needed If the report only feeds a table, a CSV or a JSON payload, `Member.with_connection { |c| c.select_all(sql) }` returns an `ActiveRecord::Result` and skips building thousands of model objects. You lose model methods and attribute casting, which a printout rarely needs. ## Async variants For a dashboard that renders several slow reports, `Member.async_find_by_sql([sql, values])` and `async_count_by_sql` start the query on a background thread and return an `ActiveRecord::Promise`; calling `value` waits for the records. A relation gets the same effect with `load_async`. Both need `config.active_record.async_query_executor` set; otherwise the query runs in the foreground instead of concurrently. ## Keeping raw SQL maintainable - Put each report in **one named place**, a class method or a query object such as `Rankings::ClubStandings`, rather than inline in a controller. - Pass every value through the **array form** with named placeholders. - Cover it with a test that runs against the same adapter as production, because SQL the builder did not generate is only checked when it executes. - Re-check it after schema changes: a renamed column breaks raw SQL at run time, not at boot.
- How would you paginate the find_by_sql ranking without loading every member?Put `LIMIT :limit OFFSET :offset` in the SQL and bind both values, or rewrite the report as a relation so `limit`, `offset` and a pagination gem apply. Slicing the returned `Array` in Ruby still loads every row first.
- How would you check that a relation rewrite of the report runs the SQL you expect?Call `to_sql` on the relation to print the statement it will run and compare it with the hand-written version, or read the development log. `explain` on the relation also shows the database's plan, which is the quickest way to confirm the rewrite did not change how the query executes.
- find_by_sql returns a relation, so you can add where and limit afterwards.
- Member.includes(:games).find_by_sql(sql) preloads games for the returned records.
- Window functions and CTEs always force you out of the relation builder.
- Raw SQL is checked when the app boots, so renamed columns fail fast.
- Any report with a GROUP BY should be written with find_by_sql.