Run Raw Sql In Rails And Map The Results To A Ruby Struct
Reporting queries with window functions, CTEs, and cross-table aggregates are painful to express in ActiveRecord and produce untyped hashes when you drop to SQL. Mapping the rows into a Struct gives you named accessors without instantiating models you do not need.
Description
select_all returns an ActiveRecord::Result with columns and rows. Building a Struct from the column names and mapping each row into it gives you objects that respond to row.total_cents rather than row["total_cents"].
Data.define (Ruby 3.2+) is the better choice when the results should be immutable — which for a report row they should be.
Always parameterize. Use sanitize_sql_array or bind parameters; string interpolation into SQL is an injection vulnerability regardless of where the value came from.
Skipping model instantiation is the point — for a hundred thousand report rows this is several times faster and uses a fraction of the memory.
Sample input:
MonthlyRevenue.for(account_id: 42)
Sample Output:
# => [#<data Row month=2026-06-01, orders=142, revenue_cents=482100>, ...]
Answer
class MonthlyRevenue
SQL = <<~SQL.freeze
SELECT date_trunc('month', o.created_at)::date AS month,
COUNT(*) AS orders,
SUM(o.total_cents) AS revenue_cents
FROM orders o
WHERE o.account_id = :account_id
AND o.status = 'completed'
GROUP BY 1
ORDER BY 1 DESC
SQL
Row = Data.define(:month, :orders, :revenue_cents) do
def revenue = revenue_cents / 100.0
def average_order_cents = orders.zero? ? 0 : revenue_cents / orders
end
def self.for(account_id:)
sql = ActiveRecord::Base.sanitize_sql_array([SQL, { account_id: account_id }])
result = ActiveRecord::Base.connection.select_all(sql)
result.map { |row| Row.new(**row.symbolize_keys) }
end
end
rows = MonthlyRevenue.for(account_id: 42)
rows.first.revenue # => 4821.0
rows.sum(&:revenue_cents)
# Generic version — Struct built from whatever columns come back
def query_to_structs(sql, binds = {})
result = ActiveRecord::Base.connection.select_all(
ActiveRecord::Base.sanitize_sql_array([sql, binds])
)
return [] if result.empty?
klass = Struct.new(*result.columns.map(&:to_sym), keyword_init: true)
result.map { |row| klass.new(**row.symbolize_keys) }
end
Check viewARU - Brand Newsletter!
Newsletter to DEVs by DEVs - boost your Personal Brand & career! 🚀
