Active Record Queries Cheatsheet

Active Record Queries Cheat Sheet

When passing a select(:offering_id) into another query, it'll create a subquery, reducing the number of DB trips that are made


Simple Join

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;

Try to separate Concerns: chain smaller stuff together to get the same sql, but make it 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

Pluck ids from associations

question_choices = Question.where(id: 40087).joins(:choices).map(&:id)
= choices ids: [40087, 40087, 40087, 40087, 40087, 40087]

Person.billable

Nested joins

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

or

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




Deeply Nested

CustomerRequest
.joins(user: :account)
.where(accounts: { sales_rep_id: REP_ID }


SalesforceUser
.joins(accounts: { users: :customer_requests})
.find_by(customer_requests: { id: customer_request.id} )


@readings = Reading.includes(
	planet: [ 
		country: [ 
			region: [ 
				{stores: :manager}
			]
		]
	]
).where(manager: { name: 'John Smith' }, region: {id: 1234567 })

Join Vs Includes Vs Preload

prefer joins over includes for inner join for performance, since includes pulls up the tables where joins just looks at the connecting columns.  Use Preload only if you actually need all those tables. 


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")

• only fetches users with Poland addresses, doesn't fetch 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
```ruby
scope :from_the_past, -> { where("happens_at <= ?", Time.now) }




OK

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

Benchmarking 

When in doubt of which method is more performance, use these methods to benchmark the options against each other.  Scale matters. 


Benchmark.bmbm do |x|
 x.report(‘without includes’) do
  User.limit(1000).map { |u| [u.login, u.account.name] }
 end
end
```


(faster)
```ruby
Benchmark.bmbm do |x|
 x.report(‘with includes’) do
  User.includes(:account).limit(1000).map { |u| [u.login, u.account.name] }
 end
end




Overfetching

Avoid dot access - this pulls up each association, when you may only need a single value 


Bad

User.select(:email_address).find(123).email_address

OK

User.find_by(id: 123).pluck(:email_address) (pluck is more efficient at scale)




Existentials 

Prefer exists? or any?  (they are the same) 


Bad

(fetches the whole object)

User.find_by(id: 123).present?

OK 

User.where(id: 123).exists?

Fetch Association Columns

MyProjects::Projects.select(
        :"users.first_name", :"users.last_name", :id, :name, :description).joins(:user)

Sub query

# use select over pluck to do a sub query?  
offerings = @vendor.offerings.by_vendor_user(current_user.id).select(:id)
 @users = @vendor.users.by_vendor_offerings(offerings).order(:first_name)