Rails Database Best Practices

better.rb

class Client < ActiveRecord::Base
  has_many :projects

  scope :active, -> { where(active: true) }
end

class Project < ActiveRecord::Model
  belongs_to :client

  scope :active, -> {
    where(active: true)
      .joins(:client)
      .merge(Client.active)
  }
  
  scope :ordered, -> {
    order('LOWER(name)')
  }
end

migration_indexes.rb

class SomeMigration < ActiveRecord::Migration
  def change
    # Specify that an index is desired when initially defining the table.
    create_table :memberships do |t|
      t.timestamps             null: false
      t.string :status,        null: false, default: 'active', index: true
      t.references :account,   null: false, index: true, foreign_key: true
      t.references :club,      null: false, index: true, foreign_key: true
      # ...
    end
    
    # Add an index to an existing table.
    add_index :payments, :billing_period
    
    # An index on multiple columns.
    # This is useful when we always use multiple items in the where clause.
    add_index :accounts, [:provider, :uid]
  end
end

omg-no.rb

class Client < ActiveRecord::Base
  has_many :projects
end

class Project < ActiveRecord::Base
  belongs_to :client
  
  # Please don't do this...
  scope :active, -> {
    includes(:client)
      .where(active: true)
      .select { |project| project.client.active? }
      .sort_by { |project| project.name.downcase }
  }
end

ordered_scope.rb

scope :ordered, => { order(:status).order('LOWER(name) DESC') }

query_relation.rb

# A query that returns all of the adults who have signed up as volunteers this year,
# but have not yet become a pta member.
class VolunteersNotMembersQuery
  def initialize(year:)
    @year = year
  end

  def relation
    volunteer_ids  = GroupMembership.select(:person_id).school_year(@year)
    pta_member_ids = PtaMembership.select(:person_id).school_year(@year)

    Person
      .active
      .adults
      .where(id: volunteer_ids)
      .where.not(id: pta_member_ids)
      .order(:last_name)
  end
end

query_relation_sql.sql

SELECT people.*
FROM people
WHERE people.status = 0
  AND people.kind != "student"
  AND (people.id IN (SELECT group_memberships.person_id FROM group_memberships WHERE group_memberships.school_year_id = 1))
  AND (people.id NOT IN (SELECT pta_memberships.person_id FROM pta_memberships WHERE pta_memberships.school_year_id = 1))
ORDER BY people.last_name ASC

synthetic_attribute.rb

class Group < ActiveRecord::Base
  # Augment the results with the number of memberships to this group for the specified school_year.
  # The count is captured in a synthetic attribute (i.e. not backed by a table column) called 'membership_count'.
  #
  # Group.with_membership_count(current_year).each do |group|
  #   group.membership_count
  #   ...
  # end
  #
  def self.with_membership_count(year)
    select <<~SQL
      groups.*, (
        SELECT count(*) FROM group_memberships
        WHERE group_memberships.group_id = groups.id AND group_memberships.school_year_id = #{year.id}
      ) AS membership_count
    SQL
  end
end