active_record_cheat_sheet.md

ActiveRecord Cheat Sheet

Where clause with joins

# bad: strings in where clause
Contract.joins(:user, :project) \
  .where("users.active = :active AND assignments.active = :active", active: true) \
  .references(:users, :assignments)

# better: hashes for relation qualifiers
Contract.joins(:user, :project) \
  .where(users: { active: true }) \
  .where(assignments: { active: true })
  
# will also work with includes by the way
Contract.includes(:user, :project) \
  .where(users: { active: true }) \
  .where(assignments: { active: true })

# even better: re-use scopes for relations
Contract.joins(:user, :project) \
  .merge(User.active) \
  .merge(Assignment.active)

Includes with conditions:

# Will not work!
Post.includes(:comments).where('comments.approved = true') 

# Works, because where clause is a hash!
Post.includes(:comments).where(comments: { approved: true })

# Also works (because of references), but is less elegant
Post.includes(:comments).where('comments.approved = true').references(:comments)
# What's happening behind the scenes: `references` forces this to be a join instead 
# of a separate request, so the string where clause works again. 
# If it's a hash, this is done automatically

Range queries

# bad: writing ranges yourself with >= and <=
Schedule.where(["schedules.date >= ? AND schedules.date <= ?", from, to])
# or in model:
scope :in_date_range, ->(from, to) { where(["schedules.date >= ? AND schedules.date <= ?", from, to]) }

# better: use ranges that are easier to read
Schedule.where(date: (from..to))
# or in model:
scope :in_date_range, ->(from, to) { where(date: (from..to)) }

Sub-query instead of plucking

# bad: pluck does a separate query just to get IDs
current_account.tags.joins(:purchases).select("tags.*, count(*)") \
  .where(purchases: { id: acc.purchases.pluck(:id)}) \
  .group('tags.id, tags.name, tags.color')

# better: select(:id) actually transforms this into a sub-query
current_account.tags.joins(:purchases).select("tags.*, count(*)") \
  .where(purchases: { id: acc.purchases.select(:id)}) \
  .group('tags.id, tags.name, tags.color')

# bad: pluck does a separate query
current_account.projects.where(leader_id: User.active.pluck(:id)).count

# better: select(:id) actually transforms this into a sub-query
current_account.projects.where(leader_id: User.active.select(:id)).count

Use distinct AR query instead of string

# bad: using a string just for the DISTINCT
activities_to_be_billed.pluck('DISTINCT assignment_id')

# better use `distinct` qualifier
activities_to_be_billed.distinct.pluck(:assignment_id)

Order by joined relations (debatable)

# bad: writing out the order as strings
Contract.joins(user: :unit) \
  .order("units.name, users.name, users.firstname")

# better: using model orders
Contract.joins(user: :unit) \
  .merge(Unit.order(:name)) \
  .merge(User.order(:name, :firstname))

Count values

account.projects.group(:leader_id).count(:leader_id)