Rails Active Record Queries - A Cheatsheet

TLDR

  • Prefer joins over includes for inner join (better perfomance)
  • Use scopes within models, chain them together with merge()

Simple Joins


      class Person < ActiveRecord::Base
        belongs_to :role
      end

      class Role < ActiveRecord::Base
        has_many :people
      end

      Person.all.joins(:role).where(roles: { billable: true })

      SELECT "people".*
      FROM "people"
      INNER JOIN "roles" ON "roles.id" = "people"."role_id"
      WHERE "roles"."billable" = true;
    

Keep Concerns Separate(same sql, more clear)


      class Role < ActiveRecord::Base
        def self.billable
          where(billable: true)
        end
      end
      Person.joins(:role).merge(Role.billable)
    

even better


      class Person < ActiveRecord::Base
        def self.billable
          joins(:role).merge(Role.billable)
        end
      end

      Person.billable
    

Nested joins

Event.joins(:store).where(stores: {retailer_id: 2})

or

Event.joins(:store => :retailer).where(stores: {retailer: {id: 2}})

Join Vs Includes Vs Preload

Joins

  • gets only the requested records, doing whatever joins need to happen to ensure only the requested records are fetched

    ex User.joins(:addresses).where("addresses.country = ?", "Poland")

  • Inner Join - only fetches users, not addresses

Includes

  • will eager load when necessary, using INNER JOINS. ex. User.joins(:addresses).where("addresses.country = ?", "Poland").includes(:addresses)
  • fetches any user with a Poland address, and their Poland address. If they have another address, that won’t be fetched

Preload

  • will eager load using OUTER JOINS. ex. User.joins(:addresses).where("addresses.country = ?", "Poland").preload(:addresses)
  • fetches all users with a Poland address, and preloads all addresses for those users

Using Scopes


      # Bad
      # Time.now would be always the time when the class was loaded
      # You might not even spot the bug in development because classes are
      # automatically reloaded for you after changes.
      scope :from_the_past, where("happens_at <= ?", Time.now)

      # OK
      scope :from_the_past, -> { where("happens_at <= ?", Time.now) }

      # OK
      def self.from_the_past
        where("happens_at <= ?", Time.now)
      end
    

Links for Further Reading