\documentclass[10pt,a4paper]{article}

% Packages
\usepackage{fancyhdr}           % For header and footer
\usepackage{multicol}           % Allows multicols in tables
\usepackage{tabularx}           % Intelligent column widths
\usepackage{tabulary}           % Used in header and footer
\usepackage{hhline}             % Border under tables
\usepackage{graphicx}           % For images
\usepackage{xcolor}             % For hex colours
%\usepackage[utf8x]{inputenc}    % For unicode character support
\usepackage[T1]{fontenc}        % Without this we get weird character replacements
\usepackage{colortbl}           % For coloured tables
\usepackage{setspace}           % For line height
\usepackage{lastpage}           % Needed for total page number
\usepackage{seqsplit}           % Splits long words.
%\usepackage{opensans}          % Can't make this work so far. Shame. Would be lovely.
\usepackage[normalem]{ulem}     % For underlining links
% Most of the following are not required for the majority
% of cheat sheets but are needed for some symbol support.
\usepackage{amsmath}            % Symbols
\usepackage{MnSymbol}           % Symbols
\usepackage{wasysym}            % Symbols
%\usepackage[english,german,french,spanish,italian]{babel}              % Languages

% Document Info
\author{dwapi}
\pdfinfo{
  /Title (rails-5-activerecord-queries.pdf)
  /Creator (Cheatography)
  /Author (dwapi)
  /Subject (Rails 5 ActiveRecord Queries Cheat Sheet)
}

% Lengths and widths
\addtolength{\textwidth}{6cm}
\addtolength{\textheight}{-1cm}
\addtolength{\hoffset}{-3cm}
\addtolength{\voffset}{-2cm}
\setlength{\tabcolsep}{0.2cm} % Space between columns
\setlength{\headsep}{-12pt} % Reduce space between header and content
\setlength{\headheight}{85pt} % If less, LaTeX automatically increases it
\renewcommand{\footrulewidth}{0pt} % Remove footer line
\renewcommand{\headrulewidth}{0pt} % Remove header line
\renewcommand{\seqinsert}{\ifmmode\allowbreak\else\-\fi} % Hyphens in seqsplit
% This two commands together give roughly
% the right line height in the tables
\renewcommand{\arraystretch}{1.3}
\onehalfspacing

% Commands
\newcommand{\SetRowColor}[1]{\noalign{\gdef\RowColorName{#1}}\rowcolor{\RowColorName}} % Shortcut for row colour
\newcommand{\mymulticolumn}[3]{\multicolumn{#1}{>{\columncolor{\RowColorName}}#2}{#3}} % For coloured multi-cols
\newcolumntype{x}[1]{>{\raggedright}p{#1}} % New column types for ragged-right paragraph columns
\newcommand{\tn}{\tabularnewline} % Required as custom column type in use

% Font and Colours
\definecolor{HeadBackground}{HTML}{333333}
\definecolor{FootBackground}{HTML}{666666}
\definecolor{TextColor}{HTML}{333333}
\definecolor{DarkBackground}{HTML}{A34346}
\definecolor{LightBackground}{HTML}{F9F3F3}
\renewcommand{\familydefault}{\sfdefault}
\color{TextColor}

% Header and Footer
\pagestyle{fancy}
\fancyhead{} % Set header to blank
\fancyfoot{} % Set footer to blank
\fancyhead[L]{
\noindent
\begin{multicols}{3}
\begin{tabulary}{5.8cm}{C}
    \SetRowColor{DarkBackground}
    \vspace{-7pt}
    {\parbox{\dimexpr\textwidth-2\fboxsep\relax}{\noindent
        \hspace*{-6pt}\includegraphics[width=5.8cm]{/web/www.cheatography.com/public/images/cheatography_logo.pdf}}
    }
\end{tabulary}
\columnbreak
\begin{tabulary}{11cm}{L}
    \vspace{-2pt}\large{\bf{\textcolor{DarkBackground}{\textrm{Rails 5 ActiveRecord Queries Cheat Sheet}}}} \\
    \normalsize{by \textcolor{DarkBackground}{dwapi} via \textcolor{DarkBackground}{\uline{cheatography.com/43665/cs/12982/}}}
\end{tabulary}
\end{multicols}}

\fancyfoot[L]{ \footnotesize
\noindent
\begin{multicols}{3}
\begin{tabulary}{5.8cm}{LL}
  \SetRowColor{FootBackground}
  \mymulticolumn{2}{p{5.377cm}}{\bf\textcolor{white}{Cheatographer}}  \\
  \vspace{-2pt}dwapi \\
  \uline{cheatography.com/dwapi} \\
  \end{tabulary}
\vfill
\columnbreak
\begin{tabulary}{5.8cm}{L}
  \SetRowColor{FootBackground}
  \mymulticolumn{1}{p{5.377cm}}{\bf\textcolor{white}{Cheat Sheet}}  \\
   \vspace{-2pt}Not Yet Published.\\
   Updated 3rd October, 2017.\\
   Page {\thepage} of \pageref{LastPage}.
\end{tabulary}
\vfill
\columnbreak
\begin{tabulary}{5.8cm}{L}
  \SetRowColor{FootBackground}
  \mymulticolumn{1}{p{5.377cm}}{\bf\textcolor{white}{Sponsor}}  \\
  \SetRowColor{white}
  \vspace{-5pt}
  %\includegraphics[width=48px,height=48px]{dave.jpeg}
  Measure your website readability!\\
  www.readability-score.com
\end{tabulary}
\end{multicols}}




\begin{document}
\raggedright
\raggedcolumns

% Set font size to small. Switch to any value
% from this page to resize cheat sheet text:
% www.emerson.emory.edu/services/latex/latex_169.html
\footnotesize % Small font.


\begin{tabularx}{17.67cm}{x{4.1175 cm} x{4.1175 cm} x{4.1175 cm} x{4.1175 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{4}{x{17.67cm}}{\bf\textcolor{white}{Active Record Queries}}  \tn
% Row 0
\SetRowColor{LightBackground}
{\bf{Find:}} & \{\{nobreak\}\}clients = Client.find({[}1, 10{]})\{\{nobreak\}\} & \{\{nobreak\}\}SELECT * FROM clients WHERE \seqsplit{(clients.id} IN (1,10))\{\{nobreak\}\} &  \tn 
% Row Count 8 (+ 8)
% Row 1
\SetRowColor{white}
{\bf{Find By:}} & \{\{nobreak\}\}Client.find\_by! \seqsplit{first\_name:} 'Nobody' & SELECT * FROM clients WHERE\{\{nl\}\}(clients.first\_name = 'Nobody)\{\{nl\}\} LIMIT 1 & find\_by! will raise an error if no record is found.\{\{nl\}\}\{\{nobreak\}\}\# =\textgreater{} \seqsplit{ActiveRecord::RecordNotFound} \tn 
% Row Count 19 (+ 11)
% Row 2
\SetRowColor{LightBackground}
{\bf{Passing Params}} & \seqsplit{Client.where("orders\_count} = ? AND locked = ?", params{[}:orders{]}, params{[}:locked{]}) &  &  \tn 
% Row Count 29 (+ 10)
% Row 3
\SetRowColor{white}
\{\{nobreak\}\}{\bf{Passing Params as Hash}} & \seqsplit{Client.where("created\_at} \textgreater{}= \seqsplit{:start\_date} AND \seqsplit{created\_at} \textless{}= \seqsplit{:end\_date"},~ \{start\_date: params{[}:start\_date{]}, \seqsplit{end\_date:} params{[}:end\_date{]}\})\} &  &  \tn 
% Row Count 46 (+ 17)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{4.1175 cm} x{4.1175 cm} x{4.1175 cm} x{4.1175 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{4}{x{17.67cm}}{\bf\textcolor{white}{Active Record Queries (cont)}}  \tn
% Row 4
\SetRowColor{LightBackground}
{\bf{Between}} & \seqsplit{Client.where(created\_at:} (Time.now.midnight - \seqsplit{1.day)..Time.now.midnight)} & SELECT FROM clients \seqsplit{WHERE(clients}.created\_at\{\{nl\}\} BETWEEN \seqsplit{'2008-12-21} 00:00:00'\{\{nl\}\} AND \seqsplit{'2008-12-22} \seqsplit{00:00:00')} &  \tn 
% Row Count 13 (+ 13)
% Row 5
\SetRowColor{white}
\{\{nobreak\}\}{\bf{Subsets:}} Find using SQL {\bf{IN}} & \seqsplit{Client.where(orders\_count:} {[}1,3,5{]}) & SELECT * FROM clients WHERE \seqsplit{(clients.orders\_count} IN (1,3,5)) &  \tn 
% Row Count 20 (+ 7)
% Row 6
\SetRowColor{LightBackground}
{\bf{Not:}} & \seqsplit{Client.where.not(locked:} true) & SELECT * FROM clients WHERE \seqsplit{(clients.locked} != 1) &  \tn 
% Row Count 26 (+ 6)
% Row 7
\SetRowColor{white}
{\bf{Distinct:}} & \seqsplit{Client.select(:name).distinct} & SELECT DISTINCT name FROM clients &  \tn 
% Row Count 31 (+ 5)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{4.1175 cm} x{4.1175 cm} x{4.1175 cm} x{4.1175 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{4}{x{17.67cm}}{\bf\textcolor{white}{Active Record Queries (cont)}}  \tn
% Row 8
\SetRowColor{LightBackground}
{\bf{Limit}} & \seqsplit{Client.limit(5)} & SELECT * FROM clients LIMIT 5 &  \tn 
% Row Count 3 (+ 3)
% Row 9
\SetRowColor{white}
{\bf{Take:}} & \{\{nobreak\}\}client = Client.take(2)\{\{nobreak\}\} & \{\{nobreak\}\}SELECT * FROM clients LIMIT 2\{\{nobreak\}\} & Returns record without any implicit ordering.\{\{nl\}\}Returns nil if no record is found. \tn 
% Row Count 12 (+ 9)
% Row 10
\SetRowColor{LightBackground}
{\bf{Offset:}} & \seqsplit{Client.limit(5).offset(30)} & SELECT * FROM clients LIMIT 5 OFFSET 30 &  \tn 
% Row Count 16 (+ 4)
% Row 11
\SetRowColor{white}
\{\{nobreak\}\}{\bf{Find or Create a New Object}} & \{\{nobreak\}\}Client.find\_or\_create\_by!(first\_name: 'Andy') & SELECT * FROM clients \seqsplit{WHERE(clients}.first\_name = 'Andy') LIMIT 1\{\{nl\}\}BEGIN INSERT INTO \seqsplit{clients(created\_at}, \seqsplit{first\_name}, updated\_at)\{\{nl\}\}VALUES \seqsplit{('2011-08-30} 05:22:57', 'Andy', \seqsplit{'2011-08-30} \seqsplit{05:22:57')} COMMIT &  \tn 
% Row Count 39 (+ 23)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{4.1175 cm} x{4.1175 cm} x{4.1175 cm} x{4.1175 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{4}{x{17.67cm}}{\bf\textcolor{white}{Active Record Queries (cont)}}  \tn
% Row 12
\SetRowColor{LightBackground}
\{\{nobreak\}\}{\bf{Find or \seqsplit{Initialize} a New Object}} & \{\{nobreak\}\}nick = \seqsplit{Client.find\_or\_initialize\_by(first\_name:} 'Nick') &  &  \tn 
% Row Count 9 (+ 9)
% Row 13
\SetRowColor{white}
{\bf{ Find by SQL}} & \seqsplit{Client.find\_by\_sql("SELECT} * FROM clients INNER JOIN orders ON \seqsplit{clients.id} = \seqsplit{orders.client\_id} ORDER BY \seqsplit{clients.created\_at} desc") &  &  \tn 
% Row Count 24 (+ 15)
% Row 14
\SetRowColor{LightBackground}
{\bf{Exists?}} & \seqsplit{Client.exists?(1)} &  &  \tn 
% Row Count 26 (+ 2)
% Row 15
\SetRowColor{white}
 & \seqsplit{Client.exists?(name:} {[}'John', 'Sergei'{]} &  &  \tn 
% Row Count 31 (+ 5)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{4.1175 cm} x{4.1175 cm} x{4.1175 cm} x{4.1175 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{4}{x{17.67cm}}{\bf\textcolor{white}{Active Record Queries (cont)}}  \tn
% Row 16
\SetRowColor{LightBackground}
 & \seqsplit{Client.where(first\_name:} 'Ryan').exists? &  &  \tn 
% Row Count 5 (+ 5)
% Row 17
\SetRowColor{white}
{\bf{Count}} & \seqsplit{Client.count} & SELECT count(*) AS \seqsplit{count\_all} FROM clients &  \tn 
% Row Count 10 (+ 5)
% Row 18
\SetRowColor{LightBackground}
{\bf{Average}} & \seqsplit{Client.average("orders\_count")} &  &  \tn 
% Row Count 15 (+ 5)
% Row 19
\SetRowColor{white}
{\bf{Minimum and Maximum}} & \seqsplit{Client.minimum("age")} &  & \seqsplit{minimum/maximum} value of a field \tn 
% Row Count 19 (+ 4)
% Row 20
\SetRowColor{LightBackground}
{\bf{Sum}} & \seqsplit{Client.sum("orders\_count")} &  & sum of a field \tn 
% Row Count 23 (+ 4)
% Row 21
\SetRowColor{white}
{\bf{Ordering Results}} & \seqsplit{Client.order(:orders\_count}, \seqsplit{created\_at:} :desc) &  &  \tn 
% Row Count 29 (+ 6)
% Row 22
\SetRowColor{LightBackground}
 & \seqsplit{Client.order("orders\_count} ASC, \seqsplit{created\_at} DESC") &  &  \tn 
% Row Count 35 (+ 6)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{4.1175 cm} x{4.1175 cm} x{4.1175 cm} x{4.1175 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{4}{x{17.67cm}}{\bf\textcolor{white}{Active Record Queries (cont)}}  \tn
% Row 23
\SetRowColor{LightBackground}
{\bf{Chaining ORDER BY}} & \seqsplit{Client.order("orders\_count} ASC").order("created\_at DESC") & SELECT * FROM clients ORDER BY \seqsplit{orders\_count} ASC, \seqsplit{created\_at} DESC &  \tn 
% Row Count 8 (+ 8)
\hhline{>{\arrayrulecolor{DarkBackground}}----}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{17.67cm}}{\bf\textcolor{white}{Joining Tables}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{17.67cm}}{\# {\bf{Join Through Defined Associations (Inner Join):}}\{\{nl\}\} \seqsplit{Category.joins(:articles)}  \{\{nl\}\} Article.joins(:category, :comments)  `   SELECT articles.* FROM articles   INNER JOIN categories ON articles.category\_id = categories.id   INNER JOIN comments ON comments.article\_id = articles.id   `   \{\{nl\}\}} \tn 
% Row Count 7 (+ 7)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{17.67cm}}{\# {\bf{Outer Joins:}} \{\{nl\}\} \seqsplit{Author.left\_outer\_joins(:posts).distinct.select('authors}.{\emph{, COUNT(posts.}}) AS \seqsplit{posts\_count').group('authors.id')}  \{\{nl\}\} `   SELECT DISTINCT authors.{\emph{, COUNT(posts.}}) AS posts\_count FROM "authors" LEFT OUTER JOIN posts ON posts.author\_id = authors.id GROUP BY authors.id   `   \{\{nl\}\}} \tn 
% Row Count 14 (+ 7)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{17.67cm}}{\# {\bf{N + 1 queries problem:}} Always use .includes() \{\{nl\}\} \seqsplit{Article.includes(:category}, \seqsplit{:comments).where(comments:} \{ visible: true \})  \{\{nl\}\} `   Includes will decide between INNER JOIN eager\_load (LOJ) or  Seperate Queries `   \{\{nl\}\}} \tn 
% Row Count 19 (+ 5)
% Row 3
\SetRowColor{white}
\mymulticolumn{1}{x{17.67cm}}{\# {\bf{Join Using Raw SQL:}}\{\{nl\}\} Author.joins("INNER JOIN posts ON posts.author\_id = authors.id AND posts.published = 't'")  \{\{nl\}\} `   SELECT authors.* FROM authors INNER JOIN posts ON posts.author\_id = authors.id AND posts.published = 't'   `   \{\{nl\}\}} \tn 
% Row Count 25 (+ 6)
% Row 4
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{17.67cm}}{\# {\bf{Retrieving filtered data from multiple tables:}} If you want to call order multiple times, subsequent orders will be appended to the first. \{\{nl\}\} Person   .select('people.id, people.name, comments.text')   .joins(:comments)   \seqsplit{.where('comments.created\_at} \textgreater{} ?', 1.week.ago)\{\{nl\}\} `   SELECT people.id, people.name, comments.text FROM people INNER JOIN comments   ON comments.person\_id = people.id WHERE comments.created\_at \textgreater{} '2015-01-01' `   \{\{nl\}\}} \tn 
% Row Count 35 (+ 10)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{17.67cm}}{\bf\textcolor{white}{Joining Tables (cont)}}  \tn
% Row 5
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{17.67cm}}{\# {\bf{Retrieving specific data from multiple tables:}}  \{\{nl\}\} Person   .select('people.id, people.name, companies.name')   .joins(:company)   .find\_by('people.name' =\textgreater{} 'John') \# this should be the last  \{\{nl\}\} `   SELECT people.id, people.name, companies.name FROM people INNER JOIN companies   ON companies.person\_id = people.id WHERE people.name = 'John' LIMIT 1 `   \{\{nl\}\}} \tn 
% Row Count 8 (+ 8)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{17.67cm}}{\bf\textcolor{white}{Group By and Having}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{17.67cm}}{\# {\bf{Group By:}}  Find a collection of the dates on which orders were created. \{\{nl\}\} \seqsplit{Order.select("date(created\_at)} as ordered\_date, sum(price) as \seqsplit{total\_price").group("date(created\_at)")} \{\{nl\}\} `   SELECT date(created\_at) as ordered\_date, sum(price) as total\_price FROM orders GROUP BY date(created\_at) `   \{\{nl\}\}} \tn 
% Row Count 7 (+ 7)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{17.67cm}}{\# {\bf{Total of grouped items:}}  To get the total of grouped items on a single query, call count after the group. \{\{nl\}\} \seqsplit{Order.group(:status).count} \{\{nl\}\} `   SELECT COUNT (*) AS count\_all, status AS status FROM "orders" GROUP BY status) `   \{\{nl\}\} \# =\textgreater{} \{ 'awaiting\_approval' =\textgreater{} 7, 'paid' =\textgreater{} 12 \}} \tn 
% Row Count 13 (+ 6)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{17.67cm}}{\# {\bf{Having:}}  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. \{\{nl\}\} \seqsplit{Order.select("date(created\_at)} as ordered\_date, sum(price) as total\_price").   \seqsplit{group("date(created\_at)")}.having("sum(price) \textgreater{} ?", 100) \{\{nl\}\} `   SELECT date(created\_at) as ordered\_date, sum(price) as total\_price FROM orders GROUP BY date(created\_at) HAVING sum(price) \textgreater{} 100 `   \{\{nl\}\}} \tn 
% Row Count 23 (+ 10)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{5.3984 cm} x{5.7358 cm} x{5.7358 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{Pluck}}  \tn
% Row 0
\SetRowColor{LightBackground}
\seqsplit{Client.where(active:} true).pluck(:id) & SELECT id FROM clients WHERE active = 1 & \# =\textgreater{} {[}1, 2, 3{]} \tn 
% Row Count 4 (+ 4)
% Row 1
\SetRowColor{white}
\seqsplit{Client.distinct.pluck(:role)} & SELECT DISTINCT role FROM clients & \# =\textgreater{} {[}'admin', 'member', 'guest'{]} \tn 
% Row Count 7 (+ 3)
% Row 2
\SetRowColor{LightBackground}
\seqsplit{Client.pluck(:id}, :name) & SELECT clients.id, clients.name FROM clients & \# =\textgreater{} {[}{[}1, 'David'{]}, {[}2, 'Jeremy'{]}, {[}3, 'Jose'{]}{]} \tn 
% Row Count 11 (+ 4)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\SetRowColor{LightBackground}
\mymulticolumn{3}{x{17.67cm}}{pluck can be used to query single or multiple columns from the underlying table of a model. \{\{nl\}\} \newline pluck makes it possible to replace code like Client.select(:id).map \{ |c| c.id \} \{\{nl\}\} \newline Unlike select, pluck directly converts a database result into a Ruby Array, without constructing ActiveRecord objects}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}



\end{document}