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:
- where
- from
- merge
- joins
- left_joins
- preload
- eager_load
Tips
- Learn to use
joins
,left_joins
forLEFT (OUTER) JOIN
- Also check LEFT JOIN LATERAL
-
ORDER BY
fields must inSELECT 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