Tips for using Active Record well (maybe).

Learn SQL (tutorial by Mode). Look at generated SQL and verified it’s what you want. Try at database console with EXPLAIN to decide if you need index.

Tools we have:

Tips

• Learn to use joins, left_joins for LEFT (OUTER) JOIN
• Also check LEFT JOIN LATERAL
• ORDER BY fields must in SELECT DISTINCT fields
• Use from for subqueries
• .explain can tell you about the query
• Resolve to Arel and ActiveRecord::Associations::Preloader preload at last, it’s a private API after all

Avoid SQL injection

Pass in argument as 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.

Selective Aggregates

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

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

Use INNER JOIN. 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.

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