Show Menu
Cheatography

Rails 5 ActiveRecord Queries Cheat Sheet (DRAFT) by

This is a draft cheat sheet. It is a work in progress and is not finished yet.

Active Record Queries

Find:
clients = Client.fi­nd([1, 10])
SELECT * FROM clients WHERE (clien­ts.id IN (1,10))
Find By:
Client.fi­nd_by! first_name: 'Nobody'
SELECT * FROM clients WHERE
(clients.first_name = 'Nobody)
LIMIT 1
find_by! will raise an error if no record is found.
# => ActiveRecord::RecordNotFound
Passing Params
Client.wh­­er­e­(­"­or­­der­­s_­count = ? AND locked = ?", params­­[:­o­r­ders], params­[:l­ocked])
Passing Params as Hash
Client.wh­ere­("cr­eat­ed_at >= :start­­_date AND created_at <= :end_d­­at­e­",­  {start­­_date: params­­[:­s­t­ar­­t_d­­ate], end_date: params­­[:­e­n­d_­­dat­­e]})}
Betw­een
Client.wh­­er­e­(­cr­­eat­­ed_at: (Time.n­­o­w.m­i­d­night - 1.day)..T­­im­e.n­o­w.m­­idn­­ight)
SELECT FROM clients WHERE(clien­ts.c­re­ated_at
BETWEEN '2008-­­12-21 00:00:00'
AND '2008-­­12-22 00:00:00')
Subs­ets: Find using SQL IN
Client.wh­ere­(or­der­s_c­ount: [1,3,5])
SELECT * FROM clients WHERE (clien­ts.o­rd­ers­_count IN (1,3,5))
Not:
Client.wh­­er­e.n­o­t­(l­­ocked: true)
SELECT * FROM clients WHERE (clien­­ts.l­­ocked != 1)
Dist­­in­ct:
Client.se­­le­c­t­(:­­nam­­e).d­­is­­tinct
SELECT DISTINCT name FROM clients
Limit
Client.li­­mit(5)
SELECT * FROM clients LIMIT 5
Take:
client = Client.take(2)
SELECT * FROM clients LIMIT 2
Returns record without any implicit ordering.
Returns nil if no record is found.
Offs­­et:
Client.li­­mi­t­(­5).o­f­­fse­­t(30)
SELECT * FROM clients LIMIT 5 OFFSET 30
Find or Create a New Object
Client.fi­nd_­or_­cre­ate­_by­!(f­irs­t_name: 'Andy')
SELECT * FROM clients WHERE(­cli­en­­ts.f­­i­r­s­t_name = 'Andy') LIMIT 1
BEGIN INSERT INTO client­s(c­rea­t­e­d_at, first_­­name, update­d_at)
VALUES ('2011­­-08-30 05:22:57', 'Andy', '2011-­­08-30 05:22:57') COMMIT
Find or Initialize a New Object
nick = Client.fi­nd_­or_­ini­tia­liz­e_b­y(f­irs­t_name: 'Nick')
Find by SQL
Client.fi­­nd­_­b­y_­­sql­­("S­­ELECT * FROM clients INNER JOIN orders ON clients.id = orders.cl­­ie­nt_id ORDER BY client­­s.c­­r­ea­­ted_at desc")
Exis­ts?
Client.ex­ist­s?(1)
 
Client.ex­­is­t­s­?(­­name: ['John', 'Sergei']
 
Client.wh­­er­e­(­fi­­rst­­_name: 'Ryan'­).e­xists?
Count
Client.count
SELECT count(*) AS count_all FROM clients
Aver­­age
Client.av­­er­a­g­e(­­"­­or­d­e­rs­­_co­­un­t­")
Minimum and Maximum
Client.mi­­ni­m­u­m(­­"­­ag­e­")
 
minimu­­m/­m­a­ximum value of a field
Sum
Client.su­­m(­"­o­r­de­­rs_­­co­u­n­t")
 
sum of a field
Ordering Results
Client.or­­de­r­(­:o­­rde­­rs­_­c­ount, create­­d_at: :desc)
 
Client.or­­de­r­(­"­or­­der­­s_­count ASC, created_at DESC")
Chaining ORDER BY
Client.or­­de­r­(­"­or­­der­­s_­count ASC"­­).o­­r­de­­r("c­­re­a­t­ed_at DESC")
SELECT * FROM clients ORDER BY orders­­_count ASC, created_at DESC

Joining Tables

# Join Through Defined Associ­ations (Inner Join):
Catego­ry.j­oi­ns(­:ar­ticles)
Articl­e.j­oin­s(:­cat­egory, :comments) SELECT articles.* FROM articles INNER JOIN categories ON articl­es.c­at­ego­ry_id = catego­ries.id INNER JOIN comments ON commen­ts.a­rt­icle_id = articl­es.id
# Outer Joins:
Author.le­ft_­out­er_­joi­ns(­:po­sts­).d­ist­inc­t.s­ele­ct(­'au­tho­rs., COUNT(­pos­ts.) AS posts_­cou­nt'­).g­rou­p('­aut­hor­s.id')
SELECT DISTINCT authors., COUNT(­pos­ts.) AS posts_­count FROM "­aut­hor­s" LEFT OUTER JOIN posts ON posts.a­ut­hor_id = authors.id GROUP BY authors.id
# N + 1 queries problem: Always use .inclu­des()
Articl­e.i­ncl­ude­s(:­cat­egory, :comme­nts­).w­her­e(c­omm­ents: { visible: true })
Includes will decide between INNER JOIN eager_load (LOJ) or Seperate Queries
# Join Using Raw SQL:
Author.jo­ins­("INNER JOIN posts ON posts.a­ut­hor_id = authors.id AND posts.p­ub­lished = 't'")
SELECT authors.* FROM authors INNER JOIN posts ON posts.a­ut­hor_id = authors.id AND posts.p­ub­lished = 't'
# Retr­ieving filtered data from multiple tables: If you want to call order multiple times, subsequent orders will be appended to the first.
Person .selec­t('­peo­ple.id, people.name, commen­ts.t­ext') .joins­(:c­omm­ents) .where­('c­omm­ent­s.c­rea­ted_at > ?', 1.week.ago)
SELECT people.id, people.name, commen­ts.text FROM people INNER JOIN comments ON commen­ts.p­er­son_id = people.id WHERE commen­ts.c­re­ated_at > '2015-­01-01'
# Retr­ieving specific data from multiple tables:
Person .selec­t('­peo­ple.id, people.name, compan­ies.name') .joins­(:c­ompany) .find_­by(­'pe­opl­e.name' => 'John') # this should be the last
SELECT people.id, people.name, compan­ies.name FROM people INNER JOIN companies ON compan­ies.pe­rson_id = people.id WHERE people.name = 'John' LIMIT 1

Group By and Having

# Group By: Find a collection of the dates on which orders were created.
Order.s­el­ect­("da­te(­cre­ate­d_at) as ordere­d_date, sum(price) as total_­pri­ce").gr­oup­("da­te(­cre­ate­d_a­t)")
SELECT date(c­rea­ted_at) as ordere­d_date, sum(price) as total_­price FROM orders GROUP BY date(c­rea­ted_at)
# Total of grouped items: To get the total of grouped items on a single query, call count after the group.
Order.g­ro­up(­:st­atu­s).c­ount
SELECT COUNT (*) AS count_all, status AS status FROM "­ord­ers­" GROUP BY status)
# => { 'await­ing­_ap­proval' => 7, 'paid' => 12 }
# Havi­ng: SQL uses the HAVING clause to specify conditions on the GROUP BY fields. You can add the HAVING clause to the SQL fired by the Model.find by adding the having method to the find.
Order.s­el­ect­("da­te(­cre­ate­d_at) as ordere­d_date, sum(price) as total_­pri­ce"). group(­"­dat­e(c­rea­ted­_at­)").h­av­ing­("su­m(p­rice) > ?", 100)
SELECT date(c­rea­ted_at) as ordere­d_date, sum(price) as total_­price FROM orders GROUP BY date(c­rea­ted_at) HAVING sum(price) > 100

Pluck

Client.wh­ere­(ac­tive: true).p­lu­ck(:id)
SELECT id FROM clients WHERE active = 1
# => [1, 2, 3]
Client.di­sti­nct.pl­uck­(:role)
SELECT DISTINCT role FROM clients
# => ['admin', 'member', 'guest']
Client.pl­uck­(:id, :name)
SELECT client­s.id, client­s.name FROM clients
# => [[1, 'David'], [2, 'Jeremy'], [3, 'Jose']]
pluck can be used to query single or multiple columns from the underlying table of a model.

pluck makes it possible to replace code like Client.se­lec­t(:­id).map { |c| c.id }

Unlike select, pluck directly converts a database result into a Ruby Array, without constr­ucting Active­Record objects
       

Support Cheatography!