Advanced ActiveRecord Queries: Joins, Subqueries, and CTEs (Rails 8.1)

Advanced ActiveRecord queries in Rails chain relation methods and drop to SQL only where the relation API ends: joins or left_outer_joins to filter across associations, includes, preload, or eager_load to load them, where(id: relation) for subqueries, with for CTEs, select for window functions and computed columns, and find_by_sql with bind parameters when nothing else fits.

Every snippet and error message on this page ran on Rails 8.1.3.1 (Ruby 3.4.10) against PostgreSQL 17.11, with the SQLite differences called out where they exist. The models are a small shop: User, Order, Payment, PurchasedItem, SellerProfile, and Video. The seed has seven users (Grace manages Ana and Ben, Ana manages Carl, Dana and Eve buy, and Shopkeeper sells), two seller profiles (Shop A and Shop B), four orders (Dana’s Order 1 and Order 2, Eve’s Order 3 and Order 4, with 1 and 3 completed), three payments (Order 4 has none), five purchased items, and three videos. Performance topics stay on the database performance guide: N+1 detection, indexing strategy, and batching live in its N+1 section and onward, and this page links there instead of repeating them.

If you landed here mid-debug, jump to the error you are looking at:

• ArgumentError: You tried to define an association named transaction
• ActiveRecord::ConfigurationError: Can't join 'Order' to association named 'payments'
• ActiveRecord::AssociationNotFoundError: Association named 'payments' was not found on Order
• PG::AmbiguousColumn: column reference "id" is ambiguous
• PG::UndefinedTable: missing FROM-clause entry for table "payments"
• ArgumentError: Relation passed to #or must be structurally compatible
• ActiveRecord::UnknownAttributeReference: Dangerous query method
• PG::GroupingError: must appear in the GROUP BY clause or be used in an aggregate function
• PG::UndefinedColumn: column videos.filename does not exist
• ActiveRecord::UnmodifiableRelation (formerly ImmutableRelation)

Complex Joins and Associations in ActiveRecord for Rails

A one-to-many that started as has_many :purchased_items grows into a graph. An order belongs to a user, has one payment, and the payment points at a seller profile and at the sales member who closed it. The relation methods that walk that graph fall into two groups. joins and left_outer_joins add a JOIN so you can filter and sort on another table. includes, preload, and eager_load load the associated records so you can read them without a query per row. The abridged models used throughout:

# app/models (abridged)
class Order < ApplicationRecord
  belongs_to :user
  has_many :purchased_items
  has_one :payment
 
  scope :completed, -> { where(completed: true) }
end
 
class Payment < ApplicationRecord
  belongs_to :order
  belongs_to :seller_profile
  belongs_to :sales_member, class_name: "User", optional: true
end
 
class PurchasedItem < ApplicationRecord
  belongs_to :order
  validates :name, presence: true
end

joins vs left_outer_joins vs includes, preload, and eager_load

joins renders an INNER JOIN and selects only the base table’s columns. left_outer_joins (alias left_joins) renders a LEFT OUTER JOIN and keeps the orders that have no payment:

Order.joins(:payment)

SELECT "orders".* FROM "orders" INNER JOIN "payments" ON "payments"."order_id" = "orders"."id"

Order.left_outer_joins(:payment)

SELECT "orders".* FROM "orders" LEFT OUTER JOIN "payments" ON "payments"."order_id" = "orders"."id"

Neither loads the payment. Reading order.payment after a joins still runs one query per order, four statements for three orders here:

Order.joins(:payment).to_a.each(&:payment)

SELECT "orders".* FROM "orders" INNER JOIN "payments" ON "payments"."order_id" = "orders"."id"
SELECT "payments".* FROM "payments" WHERE "payments"."order_id" = 1 LIMIT 1
SELECT "payments".* FROM "payments" WHERE "payments"."order_id" = 2 LIMIT 1
SELECT "payments".* FROM "payments" WHERE "payments"."order_id" = 3 LIMIT 1

That is an N+1. The database performance guide covers detecting and fixing them; here the point is which loading method produces which SQL. preload runs one extra query per association:

Order.preload(:payment).to_a

SELECT "orders".* FROM "orders"
SELECT "payments".* FROM "payments" WHERE "payments"."order_id" IN (1, 2, 3, 4)

eager_load runs a single LEFT OUTER JOIN and aliases every column (t0_r0, t1_r0, and so on) so it can rebuild both records from one row:

Order.eager_load(:payment).to_a

SELECT "orders"."id" AS t0_r0, "orders"."user_id" AS t0_r1, "orders"."name" AS t0_r2, … "payments"."created_at" AS t1_r6, "payments"."updated_at" AS t1_r7 FROM "orders" LEFT OUTER JOIN "payments" ON "payments"."order_id" = "orders"."id"

includes chooses for you. On its own it behaves like preload; the moment a where or order hash references the joined table, or you call references, it switches to the eager_load form:

Order.includes(:payment).where(payments: { total: 50.. })

SELECT "orders"."id" AS t0_r0, … FROM "orders" LEFT OUTER JOIN "payments" ON "payments"."order_id" = "orders"."id" WHERE "payments"."total" >= 50.0

joins(:payment).includes(:payment) combines the two: one INNER JOIN query that both filters on the payment and loads it. The Rails guide on eager loading lists the remaining options.

Nested Joins and Hash Conditions on Joined Tables

joins takes the same nested hash an association chain would. This finds every item that a particular buyer bought from a particular seller by joining five tables:

buyer = User.find_by!(name: "Dana")
shop  = SellerProfile.find_by!(store_name: "Shop A")
user_id, seller_profile_id = buyer.id, shop.id
 
PurchasedItem.joins(order: [:user, payment: :seller_profile])
             .where(users: { id: user_id }, seller_profiles: { id: seller_profile_id })

SELECT "purchased_items".* FROM "purchased_items" INNER JOIN "orders" ON "orders"."id" = "purchased_items"."order_id" INNER JOIN "users" ON "users"."id" = "orders"."user_id" INNER JOIN "payments" ON "payments"."order_id" = "orders"."id" INNER JOIN "seller_profiles" ON "seller_profiles"."id" = "payments"."seller_profile_id" WHERE "users"."id" = 5 AND "seller_profiles"."id" = 1

PurchasedItem.joins(order: [:user, payment: :seller_profile])
             .where(users: { id: user_id }, seller_profiles: { id: seller_profile_id })
             .count
# => 3

The keys of a where hash are table names, not association names, so the flat form is the one to use. Nesting the hash the way the joins argument is nested still works, because Rails flattens it to the same SQL:

PurchasedItem.joins(order: [:user, payment: :seller_profile])
             .where(orders: { users: { id: user_id }, payments: { seller_profiles: { id: seller_profile_id } } })
             .to_sql ==
  PurchasedItem.joins(order: [:user, payment: :seller_profile])
               .where(users: { id: user_id }, seller_profiles: { id: seller_profile_id })
               .to_sql
# => true

Inside a joined table’s hash you can use an association name with a record as the value, and Rails turns it into the foreign key:

PurchasedItem.joins(order: :payment).where(payments: { seller_profile: shop })

SELECT "purchased_items".* FROM "purchased_items" INNER JOIN "orders" ON "orders"."id" = "purchased_items"."order_id" INNER JOIN "payments" ON "payments"."order_id" = "orders"."id" WHERE "payments"."seller_profile_id" = 1

To read columns from the joined tables, select them with aliases. Each alias becomes a reader on the returned objects:

items = PurchasedItem.joins(order: [:user, payment: :seller_profile])
                     .where(users: { id: user_id })
                     .select("purchased_items.id, purchased_items.name, purchased_items.price")
                     .select("payments.id AS payment_id, payments.total AS total_order_amount, users.name AS purchaser_name, users.email AS purchaser_email")
item = items.first
[item.name, item.payment_id, item.total_order_amount.to_f, item.purchaser_name]
# => ["Keyboard", 1, 130.0, "Dana"]

A symbol select after a join is table-qualified by Rails (select(:id) renders "purchased_items"."id"), so the ambiguity is only a risk with strings. And a partial select means partial objects: reading a column you did not select raises ActiveModel::MissingAttributeError:

Order.select(:id, :name).first.completed
# raises ActiveModel::MissingAttributeError: missing attribute 'completed' for Order

where.associated and where.missing

Two shortcuts answer the “has none” and “has one” questions. where.missing (Rails 6.1) renders a LEFT OUTER JOIN plus an IS NULL check, and where.associated (Rails 7.0) an INNER JOIN plus IS NOT NULL:

Order.where.missing(:payment)

SELECT "orders".* FROM "orders" LEFT OUTER JOIN "payments" ON "payments"."order_id" = "orders"."id" WHERE "payments"."id" IS NULL

Order.where.missing(:payment).pluck(:name)
# => ["Order 4"]

Order.where.associated(:payment)

SELECT "orders".* FROM "orders" INNER JOIN "payments" ON "payments"."order_id" = "orders"."id" WHERE "payments"."id" IS NOT NULL

Both accept several association names (where.missing(:payment, :purchased_items) renders two LEFT OUTER JOINs), but flat names only: a nested hash raises NoMethodError: undefined method 'to_sym' for an instance of Hash.

merge, or, and, and where.not

merge pulls another model’s scope into a relation that has already joined that model’s table. The scope is applied to the joined table, so Order.completed filters purchased items by their order:

PurchasedItem.joins(:order).merge(Order.completed)

SELECT "purchased_items".* FROM "purchased_items" INNER JOIN "orders" ON "orders"."id" = "purchased_items"."order_id" WHERE "orders"."completed" = TRUE

Without the joins, the merged condition references a table that is not in the query, which is the missing FROM-clause entry error covered later in this section.

or and and combine two relations on the same model:

Order.where(completed: true).or(Order.where(user_id: buyer.id))

SELECT "orders".* FROM "orders" WHERE ("orders"."completed" = TRUE OR "orders"."user_id" = 5)

where.not with several keys negates the whole conjunction (one NOT over both, since Rails 6.1), which is not the same as chaining two where.not calls:

Order.where.not(completed: true, user_id: buyer.id)

SELECT "orders".* FROM "orders" WHERE NOT ("orders"."completed" = TRUE AND "orders"."user_id" = 5)

Order.where.not(completed: true).where.not(user_id: buyer.id)

SELECT "orders".* FROM "orders" WHERE "orders"."completed" != TRUE AND "orders"."user_id" != 5

where.not(name: nil) renders IS NOT NULL, and a range renders NOT (… BETWEEN …). Ranges in a plain where map to comparison operators: an endless range (price: 10..) renders >=, a beginless one (price: ..50) renders <=, an exclusive one (10...100) renders >= AND <, and Date.current.all_day renders BETWEEN. Association keys accept records and relations, and Rails 7.1 added column tuples:

Order.where(user: User.where(name: "Dana"))

SELECT "orders".* FROM "orders" WHERE "orders"."user_id" IN (SELECT "users"."id" FROM "users" WHERE "users"."name" = 'Dana')

PurchasedItem.where([:order_id, :name] => [[1, "Keyboard"], [2, "Cable"]])

SELECT "purchased_items".* FROM "purchased_items" WHERE ("purchased_items"."order_id" = 1 AND "purchased_items"."name" = 'Keyboard' OR "purchased_items"."order_id" = 2 AND "purchased_items"."name" = 'Cable')

Raw JOIN Strings and Table Aliases

When the relationship is not an association, joins accepts a SQL string. The classic case is a join on a column the schema was never told about, such as an internal identifier shared by two tables, or a second join to users for the sales member rather than the buyer:

Payment.joins("JOIN users AS sales_member ON sales_member.id = payments.sales_member_id")
       .joins("JOIN orders ON orders.proprietary_internal_company_identifier = payments.proprietary_internal_company_order_identifier")
       .count
# => 3

Read the aliased table through select:

Payment.joins("JOIN users AS sales_member ON sales_member.id = payments.sales_member_id")
       .select("payments.*, sales_member.name AS sales_member_name")
       .order(:id)
       .map(&:sales_member_name)
# => ["Ana", "Ben", "Ana"]

When the relationship is a real association, let Rails write the join. Payment.joins(:sales_member) renders the same users join with quoting and the foreign key filled in:

Payment.joins(:sales_member)

SELECT "payments".* FROM "payments" INNER JOIN "users" ON "users"."id" = "payments"."sales_member_id"

The errors that joins raise are next, each with the shape that triggers it.

ArgumentError: You tried to define an association named transaction on the model Order, but this will conflict with a method transaction already defined by Active Record

class Order < ApplicationRecord
  has_one :transaction
end

ArgumentError: You tried to define an association named transaction on the model Order, but this will conflict with a method transaction already defined by Active Record. Please choose a different association name.

Cause: transaction is a method every ActiveRecord model already has, like hash, class, or attributes, and an association would overwrite it. Rails checks the name when the class body runs, so the error appears at boot or on first reference, not when you query. Order.dangerous_attribute_method?(:transaction) returns true for any name in that list. An earlier version of this post used exactly this association, which is why the examples now use Payment.

Fix: Rename the association. The model can keep its name; a Transaction model behind a differently named association joins fine:

Order.dangerous_attribute_method?(:transaction)
# => true

class Order < ApplicationRecord
  has_one :sale, class_name: "Transaction", foreign_key: :order_id
end

ActiveRecord::ConfigurationError: Can't join 'Order' to association named 'payments'; perhaps you misspelled it?

Order.joins(:payments).to_a

ActiveRecord::ConfigurationError: Can't join 'Order' to association named 'payments'; perhaps you misspelled it?

Cause: joins and eager_load (and a nested joins(order: :payments) from another model) look the name up as an association on the model, and Order has has_one :payment, singular. The same typo on an instance is a plain NoMethodError: undefined method 'payments' for an instance of Order.

Fix: Use the association name as declared: Order.joins(:payment). Check with Order.reflect_on_association(:payment) when the name comes from a variable.

ActiveRecord::AssociationNotFoundError: Association named 'payments' was not found on Order; perhaps you misspelled it?

Order.includes(:payments).to_a

ActiveRecord::AssociationNotFoundError: Association named 'payments' was not found on Order; perhaps you misspelled it?

Cause: The same misspelling, raised by the preloader instead of the join builder: includes and preload resolve the association when they load, so this class shows up in place of ConfigurationError.

Fix: Same as the previous error: Order.includes(:payment).

ActiveRecord::StatementInvalid: PG::AmbiguousColumn: ERROR: column reference "id" is ambiguous

Order.joins(:payment).where("id = ?", 5).to_a

ActiveRecord::StatementInvalid: PG::AmbiguousColumn: ERROR:  column reference "id" is ambiguous
LINE 1: ...nts" ON "payments"."order_id" = "orders"."id" WHERE (id = 5)
                                                                ^

Cause: After a join, a string condition that names a column both tables have (id, created_at, updated_at) leaves PostgreSQL no way to pick a table. where("created_at > ?", 1.year.ago).count after the same join raises the same error.

Fix: Qualify the column (where("orders.id = ?", 5)) or use the hash form, which Rails qualifies for you (where(id: 5) renders "orders"."id" = 5). Two related shapes behave differently. pluck("created_at") after a join is qualified by Rails. order("id") after a join runs while id is in the select list, because ORDER BY resolves output column names first, and fails the moment it is not: Order.joins(:payment).order("id").pluck(:name) raises the same column reference "id" is ambiguous on PostgreSQL and SQLite3::SQLException: ambiguous column name: id on SQLite. The symbol form order(:id) is qualified and works everywhere.

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "payments"

Order.where(payments: { total: 100 }).to_a

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "payments"
LINE 1: SELECT "orders".* FROM "orders" WHERE "payments"."total" = 1...
                                              ^

Cause: The condition references a table that is not in the query. Every one of these raises it: a hash or string condition on payments with no joins; preload(:payment).where(payments: { total: 50.. }), because preload never joins; includes(:payment).where("payments.total > 50"), because a string condition does not switch includes to the join form; and merge(Order.completed) on a relation that never joined orders.

Fix: Add the join (joins(:payment) or left_outer_joins(:payment)), or, with includes, use the hash form or add references(:payments) so Rails switches to eager_load.

ArgumentError: Relation passed to #or must be structurally compatible. Incompatible values: [:joins]

Order.where(completed: true).or(Order.joins(:payment).where(payments: { total: 100.. }))

ArgumentError: Relation passed to #or must be structurally compatible. Incompatible values: [:joins]

Cause: or only combines the WHERE clauses. Everything else (joins, includes, references, limit, order) has to line up, and the error lists what the argument carries that the receiver does not. Here, the right-hand relation adds a joins.

Fix: Build the base relation once and branch from it, so both sides carry the same joins:

paid = Order.joins(:payment)
paid.where(completed: true).or(paid.where(payments: { total: 100.. }))

SELECT "orders".* FROM "orders" INNER JOIN "payments" ON "payments"."order_id" = "orders"."id" WHERE ("orders"."completed" = TRUE OR "payments"."total" >= 100.0)

Self-Referential Associations

A self-referential association points a model at itself. Managers and the sales members who report to them are both users, so the User model declares both sides of the relationship on one table:

class User < ApplicationRecord
  # Associates sales_members to a user where the user is the manager
  has_many :sales_members, class_name: "User", foreign_key: "manager_id"
  # Links a user to their manager, also a User
  belongs_to :manager, class_name: "User", optional: true
end

This allows traversal in both directions (user.manager, manager.sales_members) and it makes self joins ordinary joins. Rails aliases the second copy of the table, because two users in one FROM would be ambiguous:

User.joins(:manager)

SELECT "users".* FROM "users" INNER JOIN "users" "managers_users" ON "managers_users"."id" = "users"."manager_id"

Filter through the alias, managers_users for belongs_to :manager and sales_members_users for the has_many:

User.joins(:manager).where(managers_users: { name: "Grace" }).order(:id).pluck(:name)
# => ["Ana", "Ben"]

where.missing finds the top of the tree, with a different alias because it builds its own LEFT OUTER JOIN:

User.where.missing(:manager)

SELECT "users".* FROM "users" LEFT OUTER JOIN "users" "manager" ON "manager"."id" = "users"."manager_id" WHERE "manager"."id" IS NULL

User.where.missing(:manager).order(:id).pluck(:name)
# => ["Grace", "Dana", "Eve", "Shopkeeper"]

A manager_id column covers a tree; a many-to-many between users needs a join table. Either way, self-referential joins make queries harder to read. If you do not need the hierarchy on the same table, a separate model is often the better choice.

Walking the Whole Tree with with_recursive

A single join reaches one level. To collect everyone under a manager at any depth, Rails 7.2’s with_recursive writes a WITH RECURSIVE CTE. The first relation is the anchor, the second joins the CTE to itself, and from selects from the CTE under the model’s table name so the rest of the relation still works:

boss = User.find_by!(name: "Grace")
reports = User.with_recursive(reports: [User.where(id: boss.id), User.joins("JOIN reports ON users.manager_id = reports.id")])
              .from("reports AS users")
              .order(:id)

WITH RECURSIVE "reports" AS ( SELECT "users".* FROM "users" WHERE "users"."id" = 1 UNION ALL SELECT "users".* FROM "users" JOIN reports ON users.manager_id = reports.id ) SELECT "users".* FROM reports AS users ORDER BY "users"."id" ASC

reports.pluck(:name)
# => ["Grace", "Ana", "Ben", "Carl"]

Plain with does not do this. It renders a WITH without RECURSIVE, and PostgreSQL rejects the self-reference with PG::UndefinedTable: ERROR: relation "reports" does not exist.

Subqueries and CTEs with ActiveRecord Relations

Any relation can be a subquery. Rails renders it inline, with binds, and keeps the outer relation composable, which raw IN (...) strings do not.

Subqueries: where(id: relation), EXISTS, and from

A relation as a hash value renders IN (SELECT ...). Select the column you compare against, and the inner relation’s conditions come along:

Order.where(id: Payment.where("total > ?", 50).select(:order_id))

SELECT "orders".* FROM "orders" WHERE "orders"."id" IN (SELECT "payments"."order_id" FROM "payments" WHERE (total > 50))

Under an association key, Rails infers the primary key of the inner model:

Order.where(user: User.where(name: "Dana")).pluck(:name)
# => ["Order 1", "Order 2"]

where("id IN (?)", relation) expands to the same subquery, but as a string it cannot be merged or negated by the relation API. EXISTS has no relation method, so build the correlated predicate with Arel and pass it to where or where.not:

paid = Payment.where(Payment.arel_table[:order_id].eq(Order.arel_table[:id]))
Order.where(paid.arel.exists)

SELECT "orders".* FROM "orders" WHERE EXISTS (SELECT "payments".* FROM "payments" WHERE "payments"."order_id" = "orders"."id")

Order.where.not(paid.arel.exists).pluck(:name)
# => ["Order 4"]

from with a relation and an alias turns a grouped relation into a derived table, the way to filter on an aggregate without a HAVING on the outer query:

totals = PurchasedItem.select("order_id, SUM(price * quantity) AS order_total").group(:order_id)
PurchasedItem.from(totals, :totals)
             .where("totals.order_total > 50")
             .order("totals.order_id")
             .pluck("totals.order_id", "totals.order_total")
             .map { |id, total| [id, total.to_f] }
# => [[1, 130.0], [3, 75.0]]

CTEs with with and with_recursive

Rails 7.1’s with adds a WITH clause from a relation, and since the same release joins accepts the CTE name:

paid_orders = Payment.select(:order_id).where("total > ?", 50)
Order.with(paid_orders: paid_orders).joins(:paid_orders)

WITH "paid_orders" AS (SELECT "payments"."order_id" FROM "payments" WHERE (total > 50)) SELECT "orders".* FROM "orders" INNER JOIN "paid_orders" ON "paid_orders"."order_id" = "orders"."id"

Order.with(paid_orders: paid_orders).joins(:paid_orders).pluck(:name)
# => ["Order 1", "Order 3"]

A string join works too (joins("JOIN paid_orders ON paid_orders.order_id = orders.id")), several CTEs go in one with call, and from("recent_orders AS orders") selects from a CTE in place of the table. Since Rails 7.1, when with arrived, the CTE body can be a bound SQL literal:

Order.with(paid_orders: Arel.sql("SELECT order_id FROM payments WHERE total > ?", 50))
     .joins("JOIN paid_orders ON paid_orders.order_id = orders.id")

WITH "paid_orders" AS (SELECT order_id FROM payments WHERE total > 50) SELECT "orders".* FROM "orders" JOIN paid_orders ON paid_orders.order_id = orders.id

Recursive CTEs are with_recursive, shown in the self-join section. The API docs for with and with_recursive cover both.

select, pluck, group, and Window Functions

Everything in this section changes what the SELECT list contains, which is where Rails draws the line between attribute names it can quote and SQL it cannot check.

Computed Columns with select, pluck, and pick

A select string with an alias adds a reader to each record. Keep purchased_items.* in the list when you still want a full model:

items = PurchasedItem.select("purchased_items.*, price * quantity AS line_total").order(:id)
items.map { |i| [i.name, i.line_total.to_f] }
# => [["Keyboard", 100.0], ["Mouse", 30.0], ["Cable", 20.0], ["Monitor", 75.0], ["Sticker", 5.0]]

Rails 7.1 added a hash form of select for joined tables, which Rails quotes and qualifies:

PurchasedItem.joins(:order).select(purchased_items: [:id, :name], orders: [:name])

SELECT "purchased_items"."id", "purchased_items"."name", "orders"."name" FROM "purchased_items" INNER JOIN "orders" ON "orders"."id" = "purchased_items"."order_id"

pluck skips model instantiation and returns arrays, pick is limit(1).pluck(...).first, and since Rails 7.2 pluck takes the same hash form:

PurchasedItem.order(:id).pluck(:name, :quantity)
# => [["Keyboard", 1], ["Mouse", 1], ["Cable", 2], ["Monitor", 1], ["Sticker", 5]]

PurchasedItem.order(:id).pick(:name, :quantity)
# => ["Keyboard", 1]

Order.joins(:purchased_items).order(:id).pluck(orders: [:id, :name], purchased_items: [:name]).first(2)
# => [[1, "Order 1", "Keyboard"], [1, "Order 1", "Mouse"]]

ActiveRecord::UnknownAttributeReference: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s)

PurchasedItem.pluck("price * quantity")

ActiveRecord::UnknownAttributeReference: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "price * quantity".This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql().

Cause: order and pluck interpolate their string arguments into SQL, so Rails only accepts strings it can recognize as attribute references: a column name with an optional table qualifier, an ASC/DESC direction with NULLS FIRST/LAST, and a function call with at most one argument. order("LENGTH(name)"), order("name DESC NULLS LAST"), order("payments.total DESC"), and pluck("LOWER(name)") all pass. price * quantity, LENGTH(name) + 1, and a CASE expression do not. select and group are not guarded, which is why the same expression is fine in select.

Fix: Wrap SQL you wrote yourself in Arel.sql, and never wrap request parameters or anything else a user controls, because the wrapper is a promise that the string is safe:

PurchasedItem.order(:id).pluck(Arel.sql("price * quantity")).map(&:to_f)
# => [100.0, 30.0, 20.0, 75.0, 5.0]

A follow-on gotcha: reverse_order (which last uses) cannot flip an expression that has no direction, so order(Arel.sql("COALESCE(name, 'z')")).last raises ActiveRecord::IrreversibleOrderError. order(Arel.sql("LENGTH(name) ASC")).reverse_order works because it ends in a direction:

Order.order(Arel.sql("COALESCE(name, 'z')")).last
# raises ActiveRecord::IrreversibleOrderError: Order "COALESCE(name, 'z')" cannot be reversed automatically

group, having, and Aggregates

group with count, sum, average, minimum, or maximum returns a hash keyed by the grouped column, and having takes the aggregate condition:

PurchasedItem.group(:order_id).order(:order_id).count
# => {1 => 2, 2 => 1, 3 => 1, 4 => 1}

PurchasedItem.group(:order_id).having("SUM(price * quantity) > ?", 50).order(:order_id).count
# => {1 => 2, 3 => 1}

SELECT COUNT(*) AS "count_all", "purchased_items"."order_id" AS "purchased_items_order_id" FROM "purchased_items" GROUP BY "purchased_items"."order_id" HAVING (SUM(price * quantity) > 50) ORDER BY "purchased_items"."order_id" ASC

Across a join, group by the base table’s primary key and count the joined rows:

Order.joins(:purchased_items).group("orders.id").having("COUNT(purchased_items.id) > 1").pluck("orders.id")
# => [1]

The count_all alias is available to order, in string or hash form (order(count_all: :desc)). Three details that trip people up:

• count always runs SELECT COUNT(*); size runs a count only when the relation is not loaded, and length loads it.
• Joins duplicate rows. Order.joins(:purchased_items).count is 5 here, while Order.joins(:purchased_items).distinct.count is 4.
• exists? renders SELECT 1 AS one … LIMIT 1, the cheapest presence check.

[Order.joins(:purchased_items).count, Order.joins(:purchased_items).distinct.count]
# => [5, 4]

ActiveRecord::StatementInvalid: PG::GroupingError: ERROR: column "purchased_items.name" must appear in the GROUP BY clause or be used in an aggregate function

PurchasedItem.group(:order_id).select("order_id, name").to_a

ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  column "purchased_items.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT order_id, name FROM "purchased_items" GROUP BY "purch...
                         ^

Cause: Every selected column must be either grouped or aggregated; PostgreSQL refuses to guess which name a group of rows should report. SQLite returns an arbitrary row for the same query without complaint, so a query that passed in a SQLite test suite can fail on PostgreSQL.

Fix: Aggregate the column (select("order_id, MAX(name) AS name")), add it to group, or, when you want one whole row per group, use a window function as shown in the next two sections.

in_order_of and Ordering by Expressions

Rails 7.0’s in_order_of sorts by an explicit list of values, rendering a CASE expression and, by default, an IN filter:

Order.in_order_of(:id, [3, 1, 2])

SELECT "orders".* FROM "orders" WHERE "orders"."id" IN (3, 1, 2) ORDER BY CASE WHEN "orders"."id" = 3 THEN 1 WHEN "orders"."id" = 1 THEN 2 WHEN "orders"."id" = 2 THEN 3 END ASC

Order.in_order_of(:id, [3, 1, 2]).pluck(:name)
# => ["Order 3", "Order 1", "Order 2"]

Rails 8.0’s filter: false drops the IN and adds an ELSE, so unlisted rows sort last instead of disappearing:

Order.in_order_of(:id, [3, 1, 2], filter: false).pluck(:name)
# => ["Order 3", "Order 1", "Order 2", "Order 4"]

order takes a hash for a joined table (order(payments: { total: :desc }) renders ORDER BY "payments"."total" DESC, and without the join raises the missing FROM-clause error), and takes the same guarded strings as pluck:

Order.joins(:payment).order(payments: { total: :desc }).pluck(:name)
# => ["Order 1", "Order 3", "Order 2"]

Window Functions Through select

There is no relation method for window functions; they go in select as SQL, and the alias becomes a reader:

ranked = PurchasedItem.select("purchased_items.*, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY price DESC) AS rank_in_order")
ranked.order(:id).map { |i| [i.order_id, i.name, i.rank_in_order] }
# => [[1, "Keyboard", 1], [1, "Mouse", 2], [2, "Cable", 1], [3, "Monitor", 1], [4, "Sticker", 1]]

Arel builds the same node when you would rather not concatenate strings:

items = PurchasedItem.arel_table
rank = Arel::Nodes::NamedFunction.new("ROW_NUMBER", [])
                                 .over(Arel::Nodes::Window.new.partition(items[:order_id]).order(items[:price].desc))
                                 .as("rank_in_order")
PurchasedItem.select(items[Arel.star], rank)

SELECT "purchased_items".*, ROW_NUMBER() OVER (PARTITION BY "purchased_items"."order_id" ORDER BY "purchased_items"."price" DESC) AS rank_in_order FROM "purchased_items"

A window function cannot be filtered in the same WHERE, so the “top item per order” query goes through a CTE. Selecting from the CTE under the model’s table name keeps the hash where working on the computed column:

PurchasedItem.with(ranked: ranked)
             .from("ranked AS purchased_items")
             .where(rank_in_order: ..1)
             .order(:order_id)
             .pluck(:order_id, :name)
# => [[1, "Keyboard"], [2, "Cable"], [3, "Monitor"], [4, "Sticker"]]

The window and CTE forms are standard SQL and run unchanged on SQLite.

Database-Specific Features in ActiveRecord for Ruby on Rails

On PostgreSQL, ActiveRecord supports JSON and JSONB columns and arrays. A model with many optional attributes, or one that keeps growing model-specific columns (user preferences, video stats, a webhook payload you want to store whole), is the case for a document column instead of another migration.

JSON vs JSONB and Which Index to Add

json stores the text as written and parses it on every read. jsonb stores a binary form that is slower to write, larger, and much faster to query, and only jsonb supports the containment operators and GIN indexes (PostgreSQL’s JSON types). If you query the column, use jsonb. Default it to an empty document so readers never have to check for NULL:

# db/migrate/20260909000002_add_video_stats_to_videos.rb
class AddVideoStatsToVideos < ActiveRecord::Migration[8.1]
  def change
    add_column :videos, :video_stats, :jsonb, default: {}
    add_index :videos, :video_stats, using: :gin, opclass: :jsonb_path_ops
  end
end

schema.rb dumps this as t.jsonb "video_stats", default: {} and t.index ["video_stats"], name: "index_videos_on_video_stats", opclass: :jsonb_path_ops, using: :gin. “Supports GIN indexing” hides two things. The first is the operator class. The default, jsonb_ops, indexes every key and value and serves the key-existence operators (?, ?|, ?&) as well as containment. jsonb_path_ops indexes hashed paths, serves @> and the jsonpath operators only, and is the smaller index (1.2 MB against 1.7 MB for 20,003 of these small documents). The second is not a migration choice at all. A GIN index that already exists when rows arrive in bulk keeps the new entries in a pending list until VACUUM (or autovacuum) merges them. Until then, the planner can price the index higher than a sequential scan. This is a default-class index that was in place before 20,000 rows were inserted, after ANALYZE:

# a jsonb_ops GIN index that existed before the 20,000 rows were inserted
puts Video.where("video_stats @> ?", { filename: "clip.mp4" }.to_json).explain(:analyze).inspect

EXPLAIN (ANALYZE) SELECT "videos".* FROM "videos" WHERE (video_stats @> '{"filename":"clip.mp4"}')
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------
 Seq Scan on videos  (cost=0.00..614.04 rows=2 width=112) (actual time=0.004..2.883 rows=1 loops=1)
   Filter: (video_stats @> '{"filename": "clip.mp4"}'::jsonb)
   Rows Removed by Filter: 20002
 Planning Time: 0.034 ms
 Execution Time: 2.889 ms
(5 rows)

The same index after VACUUM ANALYZE videos:

# after VACUUM ANALYZE videos
puts Video.where("video_stats @> ?", { filename: "clip.mp4" }.to_json).explain(:analyze).inspect

EXPLAIN (ANALYZE) SELECT "videos".* FROM "videos" WHERE (video_stats @> '{"filename":"clip.mp4"}')
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on videos  (cost=21.54..29.12 rows=2 width=112) (actual time=0.017..0.018 rows=1 loops=1)
   Recheck Cond: (video_stats @> '{"filename": "clip.mp4"}'::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on index_videos_on_video_stats_jsonb_ops  (cost=0.00..21.54 rows=2 width=0) (actual time=0.015..0.015 rows=1 loops=1)
         Index Cond: (video_stats @> '{"filename": "clip.mp4"}'::jsonb)
 Planning Time: 0.033 ms
 Execution Time: 0.024 ms
(7 rows)

The jsonb_path_ops index from the migration, built after the rows were loaded, is used straight away and at a lower cost:

# with the jsonb_path_ops index from the migration
puts Video.where("video_stats @> ?", { filename: "clip.mp4" }.to_json).explain(:analyze).inspect

EXPLAIN (ANALYZE) SELECT "videos".* FROM "videos" WHERE (video_stats @> '{"filename":"clip.mp4"}')
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on videos  (cost=12.84..20.42 rows=2 width=112) (actual time=0.005..0.006 rows=1 loops=1)
   Recheck Cond: (video_stats @> '{"filename": "clip.mp4"}'::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on index_videos_on_video_stats  (cost=0.00..12.84 rows=2 width=0) (actual time=0.003..0.003 rows=1 loops=1)
         Index Cond: (video_stats @> '{"filename": "clip.mp4"}'::jsonb)
 Planning Time: 0.033 ms
 Execution Time: 0.012 ms
(7 rows)

Equality on one key is a different query. video_stats ->> 'filename' = ? extracts text, GIN cannot serve it at all, and it wants a btree index on the expression:

# db/migrate/20260910000003_index_videos_on_filename.rb
class IndexVideosOnFilename < ActiveRecord::Migration[8.1]
  def change
    add_index :videos, "(video_stats ->> 'filename')", name: "index_videos_on_video_stats_filename"
  end
end

# with the expression index
puts Video.where("video_stats ->> 'filename' = ?", "clip.mp4").explain(:analyze).inspect

EXPLAIN (ANALYZE) SELECT "videos".* FROM "videos" WHERE (video_stats ->> 'filename' = 'clip.mp4')
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_videos_on_video_stats_filename on videos  (cost=0.29..8.30 rows=1 width=112) (actual time=0.002..0.003 rows=1 loops=1)
   Index Cond: ((video_stats ->> 'filename'::text) = 'clip.mp4'::text)
 Planning Time: 0.013 ms
 Execution Time: 0.006 ms
(4 rows)

Without that index the same query was a sequential scan over all 20,003 rows (2.1 ms in this run). Which index a real table needs depends on the queries you run against it, and the indexing section of the performance guide covers how to decide; PostgreSQL’s GIN documentation covers the operator classes and the pending list.

Querying JSONB: @>, ->>, and store_accessor

Containment (@>) is the query the GIN index serves. Pass the document as JSON through a bind:

Video.where("video_stats @> ?", { filename: "uploaded_video_1080p.mp4" }.to_json)

SELECT "videos".* FROM "videos" WHERE (video_stats @> '{"filename":"uploaded_video_1080p.mp4"}')

->> extracts a key as text. Bind the value, and cast before comparing with anything that is not text, because text < numeric has no operator:

Video.where("video_stats ->> 'filename' = ?", "clip.mp4").pluck(:title)
# => ["Short"]

Video.where("(video_stats ->> 'duration')::float < ?", 6.0).order(:id).pluck(:title)
# => ["Intro", "Short"]

Video.where("video_stats ->> 'duration' < ?", 6.0).to_a
# raises ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  operator does not exist: text < numeric

A longer string condition is where precedence bites. AND binds tighter than OR, so this query, which reads as “mp4 files that are short or unprocessed”, also returns the unprocessed .mov:

Video.where("
  video_stats ->> 'filename' LIKE '%mp4' AND
  (video_stats ->> 'duration')::float < 6.0 OR
  (video_stats ->> 'processed')::boolean = false
").order(:id).pluck(:title)
# => ["Intro", "Long", "Short"]

Video.where("
  video_stats ->> 'filename' LIKE '%mp4' AND
  ((video_stats ->> 'duration')::float < 6.0 OR (video_stats ->> 'processed')::boolean = false)
").order(:id).pluck(:title)
# => ["Intro", "Short"]

Two more shapes to know. The key-exists operator is ?, which collides with positional binds (where("video_stats ? ?", "filename") raises ActiveRecord::PreparedStatementInvalid: wrong number of bind variables (1 for 2)), so use a named bind or the jsonb_exists function:

Video.where("video_stats ? :key", key: "filename").count
# => 3

And a hash condition on the column is equality on the whole document, not containment:

Video.where(video_stats: { filename: "clip.mp4" })

SELECT "videos".* FROM "videos" WHERE "videos"."video_stats" = '{"filename":"clip.mp4"}'

Video.where(video_stats: { filename: "clip.mp4" }).count
# => 0

A document column is not self-documenting: the keys never show up in schema.rb. store_accessor declares them on the model and gives each one a reader and writer that persist into the document:

# app/models/video.rb
class Video < ApplicationRecord
  store_accessor :video_stats, :filename, :duration, :processed
end

video = Video.find_by!(title: "Short")
video.duration
# => 3.0

video.update!(duration: 9.5)
video.reload.video_stats
# => {"duration" => 9.5, "filename" => "clip.mp4", "processed" => false}

Use store_accessor, not store. store :video_stats, accessors: [...] wraps a text column in a YAML coder, and on a jsonb column it raises TypeError: no implicit conversion of Hash into String on the first read. And do not name an accessor after a real column: store_accessor :video_stats, :title on a table with a title column shadows the column, so video.title returns nil while video.read_attribute(:title) still has the value.

On SQLite the column type is json, ->> works from SQLite 3.38, and @> does not exist (unrecognized token: "@").

ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column videos.filename does not exist

Video.where(filename: "clip.mp4").to_a

ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column videos.filename does not exist
LINE 1: SELECT "videos".* FROM "videos" WHERE "videos"."filename" = ...
                                              ^

Cause: A store_accessor key is a reader on the model, not a column, and the hash form of where only knows columns. Rails passes "videos"."filename" through and PostgreSQL reports it missing.

Fix: Query the document, with @> for equality on a key or ->> for comparisons, as shown in the previous section. For a plain misspelled column, where PostgreSQL adds a HINT with the nearest match, see the same error on the performance guide.

Bulk Updates with update_all

A job that processed a batch of records should not save them one by one. update_all runs one UPDATE statement for the whole relation and returns the number of rows it touched:

order = Order.find_by!(name: "Order 1")

PurchasedItem.where(order: order).update_all(processed: true)
# => 2

UPDATE "purchased_items" SET "processed" = TRUE WHERE "purchased_items"."order_id" = 1

Three things it does not do. It does not touch updated_at, so pass it yourself (update_all(processed: true, updated_at: Time.current)) or call touch_all. It does not run validations or callbacks: update_all(name: nil) succeeds on a model that validates name for presence. And it does not instantiate anything, which is the point. It accepts SQL as well as a hash (update_all("quantity = quantity + 1")).

item = PurchasedItem.find_by!(name: "Keyboard")
before = item.updated_at
PurchasedItem.where(id: item.id).update_all(processed: false)
item.reload.updated_at == before
# => true

When a callback has to run once after a bulk write (a recount, a cache bust), enqueue a job after the statement rather than updating one record through the callback path. For large tables, batch the write with in_batches, which selects a page of ids and updates it, page by page:

PurchasedItem.where(processed: false).in_batches(of: 2).update_all(processed: true)

SELECT "purchased_items"."id" FROM "purchased_items" WHERE "purchased_items"."processed" = FALSE ORDER BY "purchased_items"."id" ASC LIMIT 2
UPDATE "purchased_items" SET "processed" = TRUE WHERE "purchased_items"."processed" = FALSE AND "purchased_items"."id" IN (1, 3)
SELECT "purchased_items"."id" FROM "purchased_items" WHERE "purchased_items"."processed" = FALSE AND "purchased_items"."id" > 3 ORDER BY "purchased_items"."id" ASC LIMIT 2
UPDATE "purchased_items" SET "processed" = TRUE WHERE "purchased_items"."processed" = FALSE AND "purchased_items"."id" IN (4, 5)
SELECT "purchased_items"."id" FROM "purchased_items" WHERE "purchased_items"."processed" = FALSE AND "purchased_items"."id" > 5 ORDER BY "purchased_items"."id" ASC LIMIT 2

Choosing batch sizes and moving the work to a job are covered in the performance guide’s batching section.

insert_all and upsert_all

insert_all writes an array of hashes in one INSERT, skips validations and callbacks, and since Rails 7.0 fills created_at and updated_at itself. Rows that hit a unique index are skipped silently (ON CONFLICT DO NOTHING), and the result lists the ids that were written:

result = PurchasedItem.insert_all([
  { order_id: order.id, name: "Stand", price: 40, quantity: 1 },
  { order_id: order.id, name: "Keyboard", price: 100, quantity: 1 }
])
result.rows
# => [[6]]

INSERT INTO "purchased_items" ("order_id","name","price","quantity","created_at","updated_at") VALUES (1, 'Stand', 40.0, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (1, 'Keyboard', 100.0, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) ON CONFLICT  DO NOTHING RETURNING "id"

returning: [:id, :name] chooses the columns that come back. Passing record_timestamps: false is what makes the old “you must include the timestamps” warning true again:

PurchasedItem.insert_all([{ order_id: order.id, name: "Hub", price: 15 }], record_timestamps: false)
# raises ActiveRecord::NotNullViolation: PG::NotNullViolation: ERROR:  null value in column "created_at" of relation "purchased_items" violates not-null constraint

upsert_all updates the conflicting rows instead. unique_by names the index, and the generated SET keeps updated_at unchanged when nothing else changed:

PurchasedItem.upsert_all(
  [{ order_id: order.id, name: "Keyboard", price: 90, quantity: 2 }],
  unique_by: [:order_id, :name]
)

INSERT INTO "purchased_items" ("order_id","name","price","quantity","created_at","updated_at") VALUES (1, 'Keyboard', 90.0, 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) ON CONFLICT ("order_id","name") DO UPDATE SET updated_at=(CASE WHEN ("purchased_items"."price" IS NOT DISTINCT FROM excluded."price" AND "purchased_items"."quantity" IS NOT DISTINCT FROM excluded."quantity") THEN "purchased_items".updated_at ELSE CURRENT_TIMESTAMP END),"price"=excluded."price","quantity"=excluded."quantity" RETURNING "id"

update_only: [:price] limits the columns that are updated, and on_duplicate: Arel.sql("quantity = purchased_items.quantity + EXCLUDED.quantity") replaces the generated SET with your own. Since Rails 8.1 a nil primary key in a row renders as DEFAULT, so one upsert_all can insert new rows and update existing ones:

PurchasedItem.upsert_all([{ id: nil, order_id: order.id, name: "Adapter", price: 12, quantity: 1 }], unique_by: [:order_id, :name])

INSERT INTO "purchased_items" ("id","order_id","name","price","quantity","created_at","updated_at") VALUES (DEFAULT, 1, 'Adapter', 12.0, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) ON CONFLICT ("order_id","name") DO UPDATE SET …

explain, annotate, and Asynchronous Queries

Once a query is right, the questions are whether the database runs it the way you expect, which code sent it, and whether it has to block the request.

Reading the Plan with explain and explain(:analyze)

explain returns an ActiveRecord::Relation::ExplainProxy since Rails 7.2; earlier versions returned a plain string. The console prints it through inspect; in a script or a job, call inspect yourself, because puts relation.explain prints the proxy object. The proxy also answers count, pluck, first, last, and the aggregates, explaining that query instead:

query = PurchasedItem.joins(:order).where(orders: { completed: true })
puts query.explain.inspect

EXPLAIN SELECT "purchased_items".* FROM "purchased_items" INNER JOIN "orders" ON "orders"."id" = "purchased_items"."order_id" WHERE "orders"."completed" = TRUE
                               QUERY PLAN
-------------------------------------------------------------------------
 Hash Join  (cost=20.56..39.66 rows=360 width=85)
   Hash Cond: (purchased_items.order_id = orders.id)
   ->  Seq Scan on purchased_items  (cost=0.00..17.20 rows=720 width=85)
   ->  Hash  (cost=16.50..16.50 rows=325 width=8)
         ->  Seq Scan on orders  (cost=0.00..16.50 rows=325 width=8)
               Filter: completed
(6 rows)

Options are passed through to the database: explain(:analyze) runs the query and reports actual times, explain(:analyze, :buffers) adds buffer counts, explain(:analyze, :verbose) renders EXPLAIN (ANALYZE, VERBOSE), and an unknown option reaches PostgreSQL unchanged (explain(:bogus) raises PG::SyntaxError: ERROR: unrecognized EXPLAIN option "bogus"):

puts query.explain(:analyze).inspect

EXPLAIN (ANALYZE) SELECT "purchased_items".* FROM "purchased_items" INNER JOIN "orders" ON "orders"."id" = "purchased_items"."order_id" WHERE "orders"."completed" = TRUE
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=20.56..39.66 rows=360 width=85) (actual time=0.023..0.025 rows=7 loops=1)
   Hash Cond: (purchased_items.order_id = orders.id)
   ->  Seq Scan on purchased_items  (cost=0.00..17.20 rows=720 width=85) (actual time=0.005..0.005 rows=9 loops=1)
   ->  Hash  (cost=16.50..16.50 rows=325 width=8) (actual time=0.004..0.004 rows=2 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on orders  (cost=0.00..16.50 rows=325 width=8) (actual time=0.003..0.003 rows=2 loops=1)
               Filter: completed
               Rows Removed by Filter: 2
 Planning Time: 0.072 ms
 Execution Time: 0.036 ms
(10 rows)

One gotcha inside a request, a job, or rails runner, where the query cache is on: explain(:analyze) on a relation whose SELECT already ran prints an empty string, because the statement is served from the cache and there is nothing to explain. Wrap the call in ActiveRecord::Base.uncached:

ActiveRecord::Base.cache do
  query.to_a
  query.explain(:analyze).inspect.empty?
end
# => true

ActiveRecord::Base.cache do
  query.to_a
  ActiveRecord::Base.uncached { query.explain(:analyze).inspect }.include?("Execution Time")
end
# => true

Reading a plan and choosing the index it asks for is the performance guide’s job, and the find-fix-verify loop for a production query is in the slow-query post.

A plan read against five rows on a laptop is not the plan production runs, and explain(:analyze) only helps once you have the exact statement that got slow. AppSignal’s Rails integration reports every sql.active_record event in the request timeline and records the query text with its values removed, so you can lift the slow statement out of a trace, fill the values back in, and run explain(:analyze) against production-sized data before you change the query.

annotate and Query Log Tags

annotate appends a SQL comment to the statement, so the query shows its origin in pg_stat_statements and slow-query logs. It survives update_all and delete_all on the same relation:

Order.annotate("report: monthly sales")

SELECT "orders".* FROM "orders" /* report: monthly sales */

Query log tags do this for every statement. Rails 8.1 apps default to the sqlcommenter format, and a custom tag is a lambda over the current ActiveSupport::ExecutionContext:

# config/environments/development.rb
config.active_record.query_log_tags_enabled = true
config.active_record.query_log_tags = [
  :application, :controller, :action, :job,
  { report: ->(context) { context[:report_name] } }
]

# query log tags enabled
ActiveSupport::ExecutionContext.set(report_name: "q4-report") { Order.count }

SELECT COUNT(*) FROM "orders" /*application='Demo',report='q4-report'*/

Tags whose value is nil are dropped, which is why a plain Order.count outside a request carries only application. ActiveRecord::QueryLogs.prepend_comment = true moves the comment in front of the statement for tools that truncate long queries.

load_async and async_count

load_async schedules a relation’s SELECT on a background thread pool and blocks on to_a only if the result is not there yet; async_count, async_sum, async_pluck, and friends do the same for single values and return an ActiveRecord::Promise. The catch is the executor. In a Rails 8.1 app config.active_record.async_query_executor is nil, and with no executor every async method runs the query on the calling thread and hands back an already-resolved promise:

promise = Order.async_count
promise.class
# => ActiveRecord::Promise::Complete

promise.value
# => 4

Set the executor and the same call returns a pending promise, with async: true in the sql.active_record payload:

# after setting config.active_record.async_query_executor = :global_thread_pool
promise = Order.async_count
promise.class
# => ActiveRecord::Promise

Until the executor is configured, async queries are a no-op. When they help, and when a second connection per request costs more than it saves, is a capacity question that belongs with the performance guide.

Raw SQL Safely: find_by_sql, sanitize_sql, and Arel

Some queries are clearer as SQL than as a relation chain. The rule for all of them is the same: values go through bind parameters or the sanitizers, never through string interpolation.

find_by_sql and with_connection with Bind Parameters

find_by_sql takes a statement with ? placeholders (or :named ones) and returns instances of the model, with any extra selected columns as readers:

Order.find_by_sql([
  "SELECT orders.*, payments.total FROM orders JOIN payments ON payments.order_id = orders.id WHERE payments.total > ?", 50
]).map { |o| [o.name, o.total.to_f] }
# => [["Order 1", 130.0], ["Order 3", 75.0]]

Order.find_by_sql(["SELECT * FROM orders WHERE name = :name", { name: "Order 1" }]).map(&:name)
# => ["Order 1"]

The sanitizers are the same functions where uses, exposed for fragments you assemble yourself. sanitize_sql_like escapes the % and _ wildcards in a value that goes into a LIKE:

Order.sanitize_sql_array(["name LIKE ?", "Order%"])
# => "name LIKE 'Order%'"

Order.sanitize_sql_like("50%_off")
# => "50\\%\\_off"

A wrong placeholder count raises ActiveRecord::PreparedStatementInvalid before anything reaches the database. When you need rows rather than models, Rails 7.2’s with_connection checks a connection out for the block and returns it, and select_all returns an ActiveRecord::Result:

ActiveRecord::Base.with_connection { |c| c.select_all("SELECT COUNT(*) AS n FROM orders").to_a }
# => [{"n" => 4}]

The difference between interpolation and a bind is one quote. Never ship the first form:

x = "x' OR 1=1 --"
Order.where("name = '#{x}'")

SELECT "orders".* FROM "orders" WHERE (name = 'x' OR 1=1 --')

Order.where("name = ?", x)

SELECT "orders".* FROM "orders" WHERE (name = 'x'' OR 1=1 --')

Arel Basics: Predicates, Functions, and Arel.sql

Arel is the SQL AST underneath every relation. Model.arel_table gives you the table, table[:column] an attribute, and the attribute’s methods (eq, not_eq, gt, lt, between, in, matches, or, and, lower, asc, desc) build predicates that where and order accept directly. matches renders ILIKE on PostgreSQL and LIKE on SQLite:

users = User.arel_table
User.where(users[:name].matches("A%").or(users[:id].in([1, 2])))
    .order(users[:name].lower.desc)

SELECT "users".* FROM "users" WHERE ("users"."name" ILIKE 'A%' OR "users"."id" IN (1, 2)) ORDER BY LOWER("users"."name") DESC

User.where(users[:name].matches("A%").or(users[:id].in([1, 2]))).order(users[:name].lower.desc).pluck(:name)
# => ["Grace", "Ana"]

Arel::Nodes::NamedFunction.new("LENGTH", [users[:name]]) wraps any function, and Arel.sql marks a string as SQL you vouch for. It also takes binds (Rails 7.1) and, since Rails 7.2, a retryable: flag that tells the adapter the statement is safe to retry after a dropped connection:

Order.where(Arel.sql("completed = ?", true, retryable: true))

SELECT "orders".* FROM "orders" WHERE completed = TRUE

The Rails security guide on SQL injection is the reference for what Arel.sql must never wrap.

ActiveRecord for Ruby on Rails: Words of Caution

Be mindful of SQL injection. Rails protects the hash forms and the bind parameters; it cannot protect a string you interpolated. Writing SQL strings with single quotes is a small habit that reminds you not to interpolate anything a user controls into them.

Rails 8.1 closes two more doors. limit validates its argument at the call, so a string that is not an integer raises before any SQL is built:

Order.limit("5; DROP TABLE orders")
# raises ArgumentError: invalid value for Integer(): "5; DROP TABLE orders"

And first or last on a relation with no order, on a model that has no primary key, no implicit_order_column, and no query_constraints, raises ActiveRecord::MissingRequiredOrderError instead of returning an arbitrary row (the message names those three settings). A trap that predates both: counting a relation that already has a select list passes that list to COUNT:

Order.select(:id, :name).count
# raises ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  function count(bigint, character varying) does not exist

Count before you select, or pluck and count in Ruby when the set is small. JSON columns are not self-documenting, so declare their keys with store_accessor as shown in the JSONB section. Every bulk write (update_all, insert_all, upsert_all) skips validations and callbacks, so either you do not need them or you run them afterwards, usually from a job.

Rewriting a Relation: rewhere, regroup, unscope, and reorder

Calling where twice on the same column ANDs the conditions, which on a boolean is an empty set:

Order.where(completed: true).where(completed: false)

SELECT "orders".* FROM "orders" WHERE "orders"."completed" = TRUE AND "orders"."completed" = FALSE

rewhere replaces the condition on that column, unscope removes clauses by kind or by column, and the re* family replaces a whole clause: reorder, regroup (Rails 7.1), reselect. except(:order) and only(:where) copy a relation without or with only the named clauses:

scope = Order.where(completed: true, user_id: buyer.id)
scope.rewhere(completed: false).where_values_hash
# => {"user_id" => 5, "completed" => false}

scope.unscope(where: :completed).where_values_hash
# => {"user_id" => 5}

scope.unscope(:where).where_values_hash
# => {}

Order.none renders WHERE (1=0) and runs no query for to_a, which makes it the right return value for an empty branch that callers will keep chaining. readonly marks records so save raises ActiveRecord::ReadOnlyRecord: Order is marked as readonly, and Rails 8.0 added readonly? on the relation.

ActiveRecord::UnmodifiableRelation (formerly ActiveRecord::ImmutableRelation)

orders = Order.where(completed: true).load
orders.where!(user_id: buyer.id)

ActiveRecord::UnmodifiableRelation: ActiveRecord::UnmodifiableRelation

Cause: The bang query methods (where!, order!, joins!) mutate the relation in place, and a relation that has already loaded its records refuses to change. The message is the class name only. Rails 7.2 renamed the class from ImmutableRelation, and on 8.1 the old constant is gone: ActiveRecord::ImmutableRelation raises NameError: uninitialized constant ActiveRecord::ImmutableRelation.

Fix: Use the non-bang methods, which return a new relation and leave the loaded one alone. Rescue the new name if you were rescuing the old one:

orders.where(user_id: buyer.id).pluck(:name)
# => ["Order 1"]

Wrapping Up

The relation API now reaches most of the way into SQL, with with, with_recursive, hash select and pluck, in_order_of, and where.missing all arriving between Rails 6.1 and 8.1, and Arel or a bound string covers the rest. The errors along the way are specific enough to search for, which is why each one on this page has its own anchor. If the relation API is the part of Rails you would rather replace, the Active Record versus Sequel comparison covers the alternative.

P.S. If you’d like to read Ruby Magic posts as soon as they get off the press, subscribe to our Ruby Magic newsletter and never miss a single post!