advanced arel: when activerecord just isn't enough
DESCRIPTION
Talk given at RailsConf 2014 about using Arel to build database queries of arbitrary complexity.TRANSCRIPT
ADVANCED ARELWHEN ACTIVERECORD JUST ISN’T ENOUGH
Author.where( name: "Cameron Dutro", username: "@camertron")
`WHOAMI`
CAMERON DUTROINTERNATIONAL MAN OF MYSTERYUS GOVERNMENT
@CAMERTRONGITHUB.COM/CAMERTRON
`WHOAMI`
CAMERON DUTROINTERNATIONAL MAN OF MYSTERYUS GOVERNMENT
@CAMERTRONGITHUB.COM/CAMERTRON
`WHOAMI`
CAMERON DUTROINTERNATIONAL ENGINEERINGTWITTER, INC
@CAMERTRONGITHUB.COM/CAMERTRON
RAISE YOUR HAND IF...
Posts.find(:all, joins: [ "JOIN comments ON comments.post_id = posts.id", "JOIN authors ON authors.id = comments.author_id" ], conditions: [ "authors.name = ? AND posts.active = ?", "Barack Obama", true ])
2
RAISE YOUR HAND IF...
Posts .joins( "JOIN comments ON comments.post_id = posts.id", "JOIN authors ON authors.id = comments.author_id") .where( "authors.name = ? AND posts.active = ?", "Barack Obama", true )
3/4
RAISE YOUR HAND IF...
Posts .joins( "JOIN comments ON comments.post_id = posts.id", "JOIN authors ON authors.id = comments.author_id") .where( "authors.name = ? AND posts.active = ?", "Barack Obama", true )
3/4
HMM, LET’S SIMPLIFY?
Posts .joins(:comments) .joins( "JOIN authors ON authors.id = comments.author_id" ) .where( "authors.name = ? AND posts.active = ?", "Barack Obama", true )
3/4
HMM, LET’S SIMPLIFY?
Posts .joins(:comments) .joins(:comments => :author) .where( "authors.name = ? AND posts.active = ?", "Barack Obama", true )
3/4
PROBLEMS
3/4Posts .joins(:comments) .joins( "JOIN authors ON authors.id = comments.author_id" ) .where( "authors.name = ? AND posts.active = ?", "Barack Obama", true )
PROBLEMS
.joins( "JOIN authors ON authors.id = comments.author_id" )
PROBLEMS
.joins( "JOIN authors ON authors.id = comments.author_id" )
HAVE TO WRITE “JOIN” AND “ON”
PROBLEMS
.joins( "JOIN authors ON authors.id = comments.author_id" )
HAVE TO WRITE “JOIN” AND “ON”
HAVE TO KNOW MYSQL SYNTAX
PROBLEMS
.joins( "JOIN authors ON authors.id = comments.author_id" )
HAVE TO WRITE “JOIN” AND “ON”
HAVE TO KNOW MYSQL SYNTAX
NO SYNTAX CHECKING
PROBLEMS
3/4Posts .joins(:comments) .joins( "JOIN authors ON authors.id = comments.author_id" ) .where( "authors.name = ? AND posts.active = ?", "Barack Obama", true )
PROBLEMS
.where( "authors.name = ? AND posts.active = ?", "Barack Obama", true)
PROBLEMS
.where( "authors.name = ? AND posts.active = ?", "Barack Obama", true)
HAVE TO KNOW MYSQL SYNTAX
PROBLEMS
.where( "authors.name = ? AND posts.active = ?", "Barack Obama", true)
HAVE TO KNOW MYSQL SYNTAX
CONFUSING TO MATCH ARGUMENTS WITH QUESTION MARKS
PROBLEMS
.where( "authors.name = ? AND posts.active = ?", "Barack Obama", true)
HAVE TO KNOW MYSQL SYNTAX
CONFUSING TO MATCH ARGUMENTS WITH QUESTION MARKS
NOT OBJECT-ORIENTED
PROBLEMS
.where( "authors.name = ? AND posts.active = ?", "Barack Obama", true)
HAVE TO KNOW MYSQL SYNTAX
CONFUSING TO MATCH ARGUMENTS WITH QUESTION MARKS
NOT OBJECT-ORIENTED
NO SYNTAX CHECKING
STACKOVERFLOW
STACKOVERFLOW
RAILSCASTS #202
STACKOVERFLOW
RAILSCASTS #202
BLOGS
STACKOVERFLOW
RAILSCASTS #202
BLOGS
COWORKERS
STACKOVERFLOW
RAILSCASTS #202
BLOGS
COWORKERS
FRIENDS
STACKOVERFLOW
RAILSCASTS #202
BLOGS
COWORKERS
FRIENDS
FAMILY MEMBERS
STACKOVERFLOW
RAILSCASTS #202
BLOGS
COWORKERS
FRIENDS
FAMILY MEMBERS
YOUR CAT
KEEP CALMANDAVOID
LITERAL STRINGS IN
YOUR QUERIES
A BETTER WAY
Post .joins(:comments) .joins(Comment.joins(:author).join_sources) .where( Author[:name].eq("Barack Obama") .and(Post[:active].eq(true)) )
A BETTER WAY
Post .joins(:comments) .joins(Comment.joins(:author).join_sources) .where( Author[:name].eq("Barack Obama") .and(Post[:active].eq(true)) )
DON’T HAVE TO KNOW SQL SYNTAX
A BETTER WAY
Post .joins(:comments) .joins(Comment.joins(:author).join_sources) .where( Author[:name].eq("Barack Obama") .and(Post[:active].eq(true)) )
DON’T HAVE TO KNOW SQL SYNTAX
RUBY SYNTAX CHECKING
A BETTER WAY
Post .joins(:comments) .joins(Comment.joins(:author).join_sources) .where( Author[:name].eq("Barack Obama") .and(Post[:active].eq(true)) )
DON’T HAVE TO KNOW SQL SYNTAX
RUBY SYNTAX CHECKING
OBJECT-ORIENTED (CHAINABLE)
A BETTER WAY
Post .joins(:comments) .joins(Comment.joins(:author).join_sources) .where( Author[:name].eq("Barack Obama") .and(Post[:active].eq(true)) )
DON’T HAVE TO KNOW SQL SYNTAX
RUBY SYNTAX CHECKING
OBJECT-ORIENTED (CHAINABLE)
NO QUESTION MARKS
A BETTER WAY
Post .joins(:comments) .joins(Comment.joins(:author).join_sources) .where( Author[:name].eq("Barack Obama") .and(Post[:active].eq(true)) )
DON’T HAVE TO KNOW SQL SYNTAX
RUBY SYNTAX CHECKING
OBJECT-ORIENTED (CHAINABLE)
NO QUESTION MARKS
EASY TO READ - IT’S JUST RUBY!
WHAT WE’LL COVER
ACTIVERECORD VS AREL
TABLES, COLUMNS
TERMINAL METHODS
SELECT, WHERE, JOIN, JOIN ASSOCIATION, ORDER
AND, OR , GREATER/LESS THAN, NOT EQUALS, ETC
MATCH, IN
ACTIVERECORD
DATABASE ABSTRACTION
NO NEED TO SPEAK A DIALECT OF SQL
PERSISTENCE
DATABASE ROWS AS RUBY OBJECTS
DOMAIN LOGIC
MODELS CONTAIN APPLICATION LOGIC, VALIDATIONS, ETC
MODELS DEFINE ASSOCIATIONS
AREL
“RELATIONAL ALGEBRA” FOR RUBY
BUILDS SQL QUERIES, GENERATES ASTS
APPLIES QUERY OPTIMIZATIONS
ENABLES CHAINING
“VEXINGLY UNDOCUMENTED”
AREL
KNOWS NOTHING ABOUT YOUR MODELS
KNOWS VERY LITTLE ABOUT YOUR DATABASE
DOES NOT RETRIEVE OR STORE DATA
AREL
KNOWS NOTHING ABOUT YOUR MODELS
KNOWS VERY LITTLE ABOUT YOUR DATABASE
DOES NOT RETRIEVE OR STORE DATA
ACTIVERECORD’S RESPONSIBILITY
AREL
CONSTRUCTS QUERIES
ACTIVERECORD
DOES EVERYTHING ELSE
HIERARCHY
activerecord
arel
database
activerecord
arel
database
CONSTRUCT QUERY
EXECUTE QUERY
HIERARCHY
WHAT’S AN AST?
7
4 9
1 5
WHAT’S AN AST?
7
4 9
1 5
LEFT CHILD
WHAT’S AN AST?
7
4 9
1 5
LEFT CHILD RIGHT CHILD
WHAT’S AN AST?
5 * (6 + 3)
*
5 +
6 3
WHAT’S AN AST?
SELECT id, text FROM posts
SELECT
id text
<query>
FROM
posts
LET’S GET TO SOME CODE
AREL-HELPERS GEM
gem install arel-helpersgem “arel-helpers”, “~> 1.1.0”
TABLES AND COLUMNS
class Post < ActiveRecord::Base has_many :commentsend
TABLES AND COLUMNS
class Post < ActiveRecord::Base has_many :commentsend
Post.arel_table[:id]
TABLES AND COLUMNS
class Post < ActiveRecord::Base has_many :commentsend
Post.arel_table[:id]Post.arel_table[:text]
TABLES AND COLUMNS
class Post < ActiveRecord::Base has_many :commentsend
Post.arel_table[:id]Post.arel_table[:text]
=> #<struct Arel::Attributes::Attribute ... >
TABLES AND COLUMNS
class Post < ActiveRecord::Base include ArelHelpers::ArelTable has_many :commentsend
Post.arel_table[:id]Post.arel_table[:text]
=> #<struct Arel::Attributes::Attribute ... >
TABLES AND COLUMNS
class Post < ActiveRecord::Base include ArelHelpers::ArelTable has_many :commentsend
Post[:id]Post[:text]
=> #<struct Arel::Attributes::Attribute ... >
RELATIONS
POP QUIZ! WHAT DOES THIS STATEMENT RETURN?
Post.select(:title)
RELATIONS
POP QUIZ! WHAT DOES THIS STATEMENT RETURN?
Post.select(:title)A. [“Rails is Cool” ... ]
RELATIONS
POP QUIZ! WHAT DOES THIS STATEMENT RETURN?
Post.select(:title)A. [“Rails is Cool” ... ]B. [#<Post title=”Rails is Cool”>, ... ]
RELATIONS
POP QUIZ! WHAT DOES THIS STATEMENT RETURN?
Post.select(:title)A. [“Rails is Cool” ... ]
C. <ActiveRecord::Relation ... >B. [#<Post title=”Rails is Cool”>, ... ]
RELATIONS
POP QUIZ! WHAT DOES THIS STATEMENT RETURN?
Post.select(:title)A. [“Rails is Cool” ... ]
C. <ActiveRecord::Relation ... >B. [#<Post title=”Rails is Cool”>, ... ]
RELATIONS
query = Post.select(:title)
RELATIONS
query = Post.select(:title)query = query.select(:id)
RELATIONS
query = Post.select(:title)
query.to_sqlquery = query.select(:id)
RELATIONS
query = Post.select(:title)
query.to_sql=> SELECT title, id FROM `posts`
query = query.select(:id)
RELATIONS
query = Post.select(:title)
query.to_sql=> SELECT title, id FROM `posts`
query = query.select(:id)
RELATIONS CAN BE CHAINED!
THE SERENDIPITY OF “SELECT”
SELECT
Post.select([:id, :text]).to_sql=> SELECT id, text FROM `posts`
SELECT
Post.select([:id, :text]).to_sql=> SELECT id, text FROM `posts`
Post.select(:id).count.to_sql=> NoMethodError: undefined method `to_sql' for 26:Fixnum
SELECT
Post.select([:id, :text]).to_sql=> SELECT id, text FROM `posts`
Post.select(:id).count.to_sql=> NoMethodError: undefined method `to_sql' for 26:Fixnum
WHAT HAPPENED??
SELECT
Post.select([:id, :text]).to_sql=> SELECT id, text FROM `posts`
Post.select(:id).count.to_sql=> NoMethodError: undefined method `to_sql' for 26:Fixnum
WHAT HAPPENED??
.count IS A TERMINAL METHOD
SELECT
Post.select([Post[:id].count, :text]).to_sql=> SELECT COUNT(`posts`.`id`), text FROM `posts`
TERMINAL METHODS
EXECUTE IMMEDIATELY
DO NOT RETURN AN ActiveRecord::Relation
count
first, last
to_a
pluck
each, map, ETC
TERMINAL METHODS
Post.where(title: "Arel is Cool").each do |post| puts post.textend
TERMINAL METHODS
Post.where(title: "Arel is Cool").each do |post| puts post.textend
Post.where(title: "Arel is Cool").each_slice(3)
TERMINAL METHODS
Post.where(title: "Arel is Cool").each do |post| puts post.textend
Post.where(title: "Arel is Cool").each_slice(3)
BOTH EXECUTE THE QUERY IMMEDIATELY
SELECT
Post.select(Post[:visitors].sum).to_sql=> SELECT SUM(`posts`.`visitors`) AS sum_id FROM `posts`
SELECT
Post.select(Post[:visitors].sum.as("visitor_total")).to_sql=> SELECT SUM(`posts`.`views`) AS visitor_total FROM `posts`
SELECT
Post.select(Post[:visitors].sum).to_sql=> SELECT SUM(`posts`.`visitors`) AS sum_id FROM `posts`
Post.select(Post[:visitors].maximum).to_sql=> SELECT MAX(`posts`.`visitors`) AS max_id FROM `posts`
SELECT
Post.select(Post[:visitors].sum).to_sql=> SELECT SUM(`posts`.`visitors`) AS sum_id FROM `posts`
Post.select(Post[:visitors].maximum).to_sql=> SELECT MAX(`posts`.`visitors`) AS max_id FROM `posts`
Post.select(Post[:visitors].minimum).to_sql=> SELECT MIN(`posts`.`visitors`) AS min_id FROM `posts`
SELECT
Post.select( Arel::Nodes::NamedFunction.new( "LENGTH", [Post[:text]] ).as("length")).to_sql
=> SELECT LENGTH(`posts`.`text`) AS length FROM `posts`
SELECT
Post.select( Arel::Nodes::NamedFunction.new( "LENGTH", [Post[:text]] ).as("length")).to_sql
=> SELECT LENGTH(`posts`.`text`) AS length FROM `posts`
SELECT
Post.select( Arel::Nodes::NamedFunction.new( "LENGTH", [Post[:text]] ).as("length")).to_sql
=> SELECT LENGTH(`posts`.`text`) AS length FROM `posts`
include Arel::Nodes
SELECT
Post.select( NamedFunction.new( "LENGTH", [Post[:text]] ).as("length")).to_sql
=> SELECT LENGTH(`posts`.`text`) AS length FROM `posts`
include Arel::Nodes
SELECT
Post.select(Arel.star).to_sql=> SELECT * FROM `posts`
SELECT FROM
Post.select(:id).from(Post.select([:id, :text]).ast).to_sql=> SELECT id FROM SELECT id, text FROM `posts`
THE WONDER OF “WHERE”
WHERE
Post.where(title: "Arel is Cool").to_sql=> SELECT `users`.* FROM `users` WHERE `users`.`title` = 'Arel is Cool'
WITH ACTIVERECORD SUGAR
WHERE
Post.where(title: "Arel is Cool").to_sql=> SELECT `users`.* FROM `users` WHERE `users`.`title` = 'Arel is Cool'
WITH ACTIVERECORD SUGAR
Post.where(Post[:title].eq("Arel is Cool")).to_sql=> SELECT `users`.* FROM `users` WHERE `users`.`title` = 'Arel is Cool'
WITH AREL
WHERE
Post.where(Post[:title].not_eq("Arel is Cool")).to_sql=> SELECT `posts`.* FROM `posts` WHERE (`posts`.`title` != 'Arel is Cool')
WHERE
Post.where(Post[:title].not_eq("Arel is Cool")).to_sql=> SELECT `posts`.* FROM `posts` WHERE (`posts`.`title` != 'Arel is Cool')
Post.where(Post[:title].not_eq(nil)).to_sql=> SELECT `posts`.* FROM `posts` WHERE (`posts`.`title` IS NOT NULL)
WHERE
Post.where(Post[:visitors].gt(250)).to_sql=> SELECT `posts`.* FROM `posts` WHERE (`posts`.`visitors` > 250)
WHERE
Post.where(Post[:visitors].gt(250)).to_sql=> SELECT `posts`.* FROM `posts` WHERE (`posts`.`visitors` > 250)
Post.where(Post[:visitors].lt(250)).to_sql=> SELECT `posts`.* FROM `posts` WHERE (`posts`.`visitors` < 250)
WHERE
Post.where(Post[:visitors].gteq(250)).to_sql=> SELECT `posts`.* FROM `posts` WHERE (`posts`.`visitors` >= 250)
Post.where(Post[:visitors].lteq(250)).to_sql=> SELECT `posts`.* FROM `posts` WHERE (`posts`.`visitors` <= 250)
WHERE
Post.where( Post[:title].eq("Arel is Cool").and( Post[:id].eq(22).or( Post[:id].eq(23) ) )).to_sql=> SELECT `posts`.* FROM `posts` WHERE ( `posts`.`title` = 'Arel is Cool' AND (`posts`.`id` = 22 OR `posts`.`id` = 23) )
WHERE
Post.where( Post[:title].eq("Arel is Cool").and( Post[:id].in(22, 23) )).to_sql
=> SELECT `posts`.* FROM `posts` WHERE ( `posts`.`title` = 'Arel is Cool' AND `posts`.`id` IN (22, 23) )
WHERE
Post.where( Post[:title].eq("Arel is Cool").and( NamedFunction.new("LENGTH", [Post[:slug]]).gt(10) )).to_sql
=> SELECT `posts`.* FROM `posts` WHERE ( `posts`.`title` = 'Arel is Cool' AND LENGTH(`posts`.`slug`) > 10 )
THE JOY OF “JOIN”
JOIN
class Post < ActiveRecord::Base has_many :commentsend
class Comment < ActiveRecord::Base belongs_to :post has_one :authorend
class Author < ActiveRecord::Base belongs_to :commentend
JOIN
Author.joins(:comment).where(id: 42).to_sql
=> SELECT `authors`.* FROM `authors` INNER JOIN `comments` ON `comments`.`id` = `authors`.`comment_id` WHERE `authors`.`id` = 42
JOIN
Author .joins(:comment, :comment => :post) .where(Post[:id].eq(42)) .to_sql
=> SELECT `authors`.* FROM `authors` INNER JOIN `comments` ON `comments`.`id` = `authors`.`comment_id` INNER JOIN `posts` ON `posts`.`id` = `comments`.`post_id` WHERE `posts`.`id` = 42
WAIT, WHAT ABOUT OUTER JOINS?
JOIN
Author .joins(:comment, :comment => :post) .where(Post[:id].eq(42)) .to_sql
=> SELECT `authors`.* FROM `authors` INNER JOIN `comments` ON `comments`.`id` = `authors`.`comment_id` INNER JOIN `posts` ON `posts`.`id` = `comments`.`post_id` WHERE `posts`.`id` = 42
JOIN
Author .joins(:comment) .joins(Comment.joins(:post).join_sources) .where(Post[:id].eq(42)) .to_sql
=> SELECT `authors`.* FROM `authors` INNER JOIN `comments` ON `comments`.`id` = `authors`.`comment_id` INNER JOIN `posts` ON `posts`.`id` = `comments`.`post_id` WHERE `posts`.`id` = 42
JOIN
Author .joins(Author.joins(:comment).join_sources) .joins(Comment.joins(:post).join_sources) .where(Post[:id].eq(42)) .to_sql
=> SELECT `authors`.* FROM `authors` INNER JOIN `comments` ON `comments`.`id` = `authors`.`comment_id` INNER JOIN `posts` ON `posts`.`id` = `comments`.`post_id` WHERE `posts`.`id` = 42
JOIN
Author .joins( Author.arel_table.join(Comment.arel_table) .on(Comment[:id].eq(Author[:comment_id])) .join_sources ) .joins( Comment.arel_table.join(Post.arel_table) .on(Post[:id].eq(Comment[:post_id])) .join_sources ) .where(Post[:id].eq(42)) .to_sql
JOIN
Author .joins( Author.arel_table.join(Comment.arel_table, Arel::OuterJoin) .on(Comment[:id].eq(Author[:comment_id])) .join_sources ) .joins( Comment.arel_table.join(Post.arel_table, Arel::OuterJoin) .on(Post[:id].eq(Comment[:post_id])) .join_sources ) .where(Post[:id].eq(42)) .to_sql
JOIN
=> SELECT `authors`.* FROM `authors` LEFT OUTER JOIN `comments` ON `comments`.`id` = `authors`.`comment_id` LEFT OUTER JOIN `posts` ON `posts`.`id` = `comments`.`post_id` WHERE `posts`.`id` = 42
JOIN
Author .joins( Author.arel_table.join(Comment.arel_table, Arel::OuterJoin) .on(Comment[:id].eq(Author[:comment_id])) .join_sources ) .joins( Comment.arel_table.join(Post.arel_table, Arel::OuterJoin) .on(Post[:id].eq(Comment[:post_id])) .join_sources ) .where(Post[:id].eq(42)) .to_sql
JOIN
Author .joins( Author.arel_table.join(Comment.arel_table, Arel::OuterJoin) .on(Comment[:id].eq(Author[:comment_id])) .join_sources ) .joins( Comment.arel_table.join(Post.arel_table, Arel::OuterJoin) .on(Post[:id].eq(Comment[:post_id])) .join_sources ) .where(Post[:id].eq(42)) .to_sql
JOIN
Author .joins( Author.arel_table.join(Comment.arel_table, Arel::OuterJoin) .on(Comment[:id].eq(Author[:comment_id])) .join_sources ) .joins( Comment.arel_table.join(Post.arel_table, Arel::OuterJoin) .on(Post[:id].eq(Comment[:post_id])) .join_sources ) .where(Post[:id].eq(42)) .to_sql
include ArelHelpers::JoinAssociation
JOIN
Author .joins(join_association(Author, :comment, Arel::OuterJoin)) .joins( Comment.arel_table.join(Post.arel_table, Arel::OuterJoin) .on(Post[:id].eq(Comment[:post_id])) .join_sources ) .where(Post[:id].eq(42)) .to_sql
include ArelHelpers::JoinAssociation
JOIN
Author .joins(join_association(Author, :comment, Arel::OuterJoin)) .joins(join_association(Comment, :post, Arel::OuterJoin)) .where(Post[:id].eq(42)) .to_sql
include ArelHelpers::JoinAssociation
JOIN
Author .joins( join_association(Author, :comment) do |assoc_name, join_conds| join_conds.and(Comment[:created_at].lteq(Date.yesterday)) end ) .joins(join_association(Comment, :post, Arel::OuterJoin)) .where(Post[:id].eq(42)) .to_sql
include ArelHelpers::JoinAssociation
JOIN
=> SELECT `authors`.* FROM `authors` INNER JOIN `comments` ON `comments`.`id` = `authors`.`comment_id` AND `comments`.`created_at` <= '2014-04-15' LEFT OUTER JOIN `posts` ON `posts`.`id` = `comments`.`post_id` WHERE `posts`.`id` = 42
JOIN TABLES
class Course < ActiveRecord::Base has_and_belongs_to_many :teachersend
class Teacher < ActiveRecord::Base has_and_belongs_to_many :coursesend
JOIN TABLES
class Course < ActiveRecord::Base has_and_belongs_to_many :teachersend
class Teacher < ActiveRecord::Base has_and_belongs_to_many :coursesend
coursesteacherscourses_teachers
JOIN TABLES
JOIN TABLES
course TABLE
JOIN TABLES
Course.arel_table
course TABLE
JOIN TABLES
Course.arel_table
course TABLE
teacher TABLE
JOIN TABLES
Course.arel_table
Teacher.arel_table
course TABLE
teacher TABLE
JOIN TABLES
Course.arel_table
Teacher.arel_table
course TABLE
teacher TABLE
courses_teachers TABLE
JOIN TABLES
Course.arel_table
Teacher.arel_table
course TABLE
teacher TABLE
???? (no model class)
courses_teachers TABLE
JOIN TABLES
JOIN TABLES
ct = Arel::Table.new(:courses_teachers)
JOIN TABLES
ct = Arel::Table.new(:courses_teachers)
Course .joins( Course.arel_table.join(Teacher.arel_table) .on(Course[:id].eq(ct[:course_id])) .and(Teacher[:id].eq(ct[:teacher_id])) .join_sources ) .to_sql
THE OPULENCE OF “ORDER”
ORDER
Post.order(:visitors).to_sql=> SELECT `posts`.* FROM `posts` ORDER BY visitors
ORDER
Post.order(:visitors).to_sql=> SELECT `posts`.* FROM `posts` ORDER BY visitors
Post.order(:views).reverse_order.to_sql=> SELECT `posts`.* FROM `posts` ORDER BY visitors DESC
ORDER
Post.order(:visitors).to_sql=> SELECT `posts`.* FROM `posts` ORDER BY visitors
Post.order(:views).reverse_order.to_sql=> SELECT `posts`.* FROM `posts` ORDER BY visitors DESC
Post.order(Post[:views].desc).to_sql=> SELECT `posts`.* FROM `posts` ORDER BY visitors DESC
SUBQUERIES WITH “IN”
IN
Post.where( Post.arel_table[:title].in( Post.select(:title).where(id: 5).ast )).to_sql
=> SELECT `phrases`.* FROM `phrases` WHERE `phrases`.`title` IN ( SELECT title FROM `phrases` WHERE `phrases`.`id` = 5 )
LIKE QUERIES WITH “MATCHES”
MATCHES
Post.where(Post[:title].matches("%arel%")).to_sql=> SELECT `phrases`.* FROM `phrases` WHERE (`phrases`.`key` LIKE x'256172656c25')
QUERY BUILDERS
class QueryBuilder extend Forwardable attr_reader :query def_delegators :@query, :to_a, :to_sql, :each
def initialize(query) @query = query end
protected
def reflect(query) self.class.new(query) endend
class PostQueryBuilder < QueryBuilder def initialize(query = nil) super(query || post.unscoped) end
def with_title_matching(title) reflect( query.where(post[:title].matches("%#{title}%")) ) end
def with_comments_by(usernames) reflect( query .joins(:comments => :author) .where(Author[:username].in(usernames)) ) end
def since_yesterday reflect( query.where(post[:created_at].gteq(Date.yesterday)) ) end
private
def author Author end
def post Post endend
class PostQueryBuilder < QueryBuilder def initialize(query = nil) super(query || post.unscoped) end
def with_title_matching(title) reflect( query.where(post[:title].matches("%#{title}%")) ) end
def with_comments_by(usernames) reflect( query .joins(:comments => :author) .where(Author[:username].in(usernames)) ) end
def since_yesterday reflect( query.where(post[:created_at].gteq(Date.yesterday)) ) end
private
def author Author end
def post Post endend
def with_title_matching(title) reflect( query.where(post[:title].matches("%#{title}%")) )end
class PostQueryBuilder < QueryBuilder def initialize(query = nil) super(query || post.unscoped) end
def with_title_matching(title) reflect( query.where(post[:title].matches("%#{title}%")) ) end
def with_comments_by(usernames) reflect( query .joins(:comments => :author) .where(Author[:username].in(usernames)) ) end
def since_yesterday reflect( query.where(post[:created_at].gteq(Date.yesterday)) ) end
private
def author Author end
def post Post endend
class PostQueryBuilder < QueryBuilder def initialize(query = nil) super(query || post.unscoped) end
def with_title_matching(title) reflect( query.where(post[:title].matches("%#{title}%")) ) end
def with_comments_by(usernames) reflect( query .joins(:comments => :author) .where(Author[:username].in(usernames)) ) end
def since_yesterday reflect( query.where(post[:created_at].gteq(Date.yesterday)) ) end
private
def author Author end
def post Post endend
def with_comments_by(usernames) reflect( query .joins(:comments => :author) .where(Author[:username].in(usernames)) )end
class PostQueryBuilder < QueryBuilder def initialize(query = nil) super(query || post.unscoped) end
def with_title_matching(title) reflect( query.where(post[:title].matches("%#{title}%")) ) end
def with_comments_by(usernames) reflect( query .joins(:comments => :author) .where(Author[:username].in(usernames)) ) end
def since_yesterday reflect( query.where(post[:created_at].gteq(Date.yesterday)) ) end
private
def author Author end
def post Post endend
class PostQueryBuilder < QueryBuilder def initialize(query = nil) super(query || post.unscoped) end
def with_title_matching(title) reflect( query.where(post[:title].matches("%#{title}%")) ) end
def with_comments_by(usernames) reflect( query .joins(:comments => :author) .where(Author[:username].in(usernames)) ) end
def since_yesterday reflect( query.where(post[:created_at].gteq(Date.yesterday)) ) end
private
def author Author end
def post Post endend
def since_yesterday reflect( query.where( post[:created_at].gteq(Date.yesterday) ) )end
EXAMPLES
PostQueryBuilder.new .with_comments_by(['camertron', 'catwithtail']) .to_sql
=> SELECT `posts`.* FROM `posts` INNER JOIN `comments` ON `comments`.`post_id` = `posts`.`id` INNER JOIN `authors` ON `authors`.`comment_id` = `comments`.`id` WHERE `authors`.`username` IN ( 'camertron', 'catwithtail' )
EXAMPLES
PostQueryBuilder.new .with_comments_by(['camertron', 'catwithtail']) .with_title_matching("arel").to_sql
=> SELECT `posts`.* FROM `posts` INNER JOIN `comments` ON `comments`.`post_id` = `posts`.`id` INNER JOIN `authors` ON `authors`.`comment_id` = `comments`.`id` WHERE `authors`.`username` IN ( 'camertron', 'catwithtail' ) AND (`posts`.`title` LIKE '%arel%')
EXAMPLES
PostQueryBuilder.new .with_comments_by(['camertron', 'catwithtail']) .with_title_matching(`arel`).since_yesterday.to_sql
=> SELECT `posts`.* FROM `posts` INNER JOIN `comments` ON `comments`.`post_id` = `posts`.`id` INNER JOIN `authors` ON `authors`.`comment_id` = `comments`.`id` WHERE `authors`.`username` IN ( 'camertron', 'catwithtail' ) AND (`posts`.`title` LIKE '%arel%') AND (`posts`.`created_at` >= '2014-04-20')
Sigh... this is all so complicated. Surely there’s a tool out there...“
”
SCUTTLE
sql-parser (java)http://github.com/camertron/sql-parser
scuttle-rb (jRuby gem)http://github.com/camertron/scuttle-rb
scuttle-server (sinatra jRuby)http://github.com/camertron/scuttle-server
THANK YOU!