Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Rails Database Best Practices
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
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
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
scope :ordered, => { order(:status).order('LOWER(name) DESC') }
# 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
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
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment