More Active Record Query Interface

Tips for using Active Record well (maybe). The basics is here which you can already do so much.

To use it well, I recommend you learn more SQL. Build the habit of looking at generated SQL (to_sql) and verified it’s doing what you want. Try at database console with EXPLAIN (or call .explain) to read the query plan.

Tools we have from Active Record:

Tips

• Learn to use joins, left_joins for LEFT (OUTER) JOIN
• Also check LEFT JOIN LATERAL PostgreSQL 9.3+. How you use it is to use the joins method.
• Use FROM for subqueries
• .explain can show you the query plan
• Resolve to Arel and ActiveRecord::Associations::Preloader preload at last, it’s a private API after all.

Avoid SQL injection

Pass in argument instead of doing string interpolations.

where("author_id = ?",  42)
where("author_id IN (?)",  [1, 2, 3, 4])

For more complex stuff, look for sanitize_sql methods.

Avoid NULLs

Use COALESCE - returns the first of its arguments that is not NULL. NULL is returned only if all arguments are NULL.

For example, in the process of id -> bigint migration, you can select one that exists:

SELECT COALESCE((id)::bigint, id_bigint) AS id
FROM posts
;

Selective Aggregates

COUNT is usually slow and selective aggregates helped to filter out irrelevant rows.

SELECT
  events.id,
  COUNT(*) FILTER (WHERE guests.confirmed) -- <= Selective Aggregates
    AS confirmed_guests_count
FROM events
LEFT JOIN guests
ON events.id = guests.event_id
;

Unique values

PostgreSQL only.

SELECT DISTINCT ON

SELECT DISTINCT ON (name) name
FROM users
ORDER BY name DESC
;

Subquery

Book.where(author_id: User.reading.pluck(:id))
Book.where(author_id: User.reading.select(:id))

pluck immediately returns array of ids, 2 SQL.
select returns ActiveRecord::Relation resulted in subquery.

Use merge to put logics in the right place

User.joins(:accounts).where(accounts: { role: :moderator })

Can be rewrite using merge:

class Account
  belongs_to :user
  scope :moderator, -> { where(role: :moderator) }
end

User.joins(:accounts).merge(Account.moderator)

joins

ActiveRecord uses INNER JOIN by default. To perform nested joins:

User.joins(courses: :assignments)

Eager loading strategies

Preload

Use additional query to load associated objects. Compatible with polymorphic.

eager_load

Use LEFT JOIN.

includes

Same as preload.
Equals to eager_load when references used with includes.
Rails 6.1 has strict_loading to help you find missing includes.

Example: Pull counts

Nowadays the database is pretty fast. You can do this in one query when your app is not big enough.

def select_sql
  <<~SQL
    users.*,
    (#{following_select}) following_count,
    (#{followers_select}) followers_count
  SQL
end

def following_select
  Follow.
    select("COUNT(1)").
    where(follower_id: user.id).
    to_sql
end

def followers_select
  Follow.
    select("COUNT(1)").
    where(followee_id: user.id).
    to_sql
end

User.
  select(select_sql).
  find(user.id)

You can also use counter_culture.

Example: Product items count

class Product < ApplicationRecord
  ITEMS_COUNT_SQL = begin
    Item.
      group(:product_id).
      select("product_id, COUNT(*) AS items_count").
      to_sql
  end

  scope :with_items_count, ->() do
    select("products.*, items.*").
    joins(<<~SQL)
      LEFT JOIN (#{ITEMS_COUNT_SQL})
      AS items
      ON items.product_id = items.id
    SQL
  end
end

UNION ALL

SELECT COUNT(*) AS 'likes' FROM likes

UNION ALL

SELECT COUNT(*) AS 'bookmarks' FROM bookmarks
;