advanced arel: when activerecord just isn't enough

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

`WHOAMI`

CAMERON DUTROINTERNATIONAL ENGINEERINGTWITTER, INC

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

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 )

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 )

HMM, LET’S SIMPLIFY?

Posts .joins(:comments) .joins(:comments => :author) .where( "authors.name = ? AND posts.active = ?", "Barack Obama", true )

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

HAVE TO WRITE “JOIN” AND “ON”

PROBLEMS

HAVE TO KNOW MYSQL SYNTAX

PROBLEMS

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

CONFUSING TO MATCH ARGUMENTS WITH QUESTION MARKS

PROBLEMS

NOT OBJECT-ORIENTED

PROBLEMS

NOT OBJECT-ORIENTED

NO SYNTAX CHECKING

STACKOVERFLOW

RAILSCASTS #202

STACKOVERFLOW

RAILSCASTS #202

STACKOVERFLOW

RAILSCASTS #202

COWORKERS

STACKOVERFLOW

RAILSCASTS #202

COWORKERS

FRIENDS

STACKOVERFLOW

RAILSCASTS #202

COWORKERS

FRIENDS

FAMILY MEMBERS

STACKOVERFLOW

RAILSCASTS #202

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

DON’T HAVE TO KNOW SQL SYNTAX

A BETTER WAY

RUBY SYNTAX CHECKING

A BETTER WAY

OBJECT-ORIENTED (CHAINABLE)

A BETTER WAY

NO QUESTION MARKS

A BETTER WAY

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

“RELATIONAL ALGEBRA” FOR RUBY

BUILDS SQL QUERIES, GENERATES ASTS

APPLIES QUERY OPTIMIZATIONS

ENABLES CHAINING

“VEXINGLY UNDOCUMENTED”

KNOWS NOTHING ABOUT YOUR MODELS

KNOWS VERY LITTLE ABOUT YOUR DATABASE

DOES NOT RETRIEVE OR STORE DATA

KNOWS NOTHING ABOUT YOUR MODELS

KNOWS VERY LITTLE ABOUT YOUR DATABASE

DOES NOT RETRIEVE OR STORE DATA

ACTIVERECORD’S RESPONSIBILITY

CONSTRUCTS QUERIES

ACTIVERECORD

DOES EVERYTHING ELSE

HIERARCHY

activerecord

database

activerecord

database

CONSTRUCT QUERY

EXECUTE QUERY

HIERARCHY

WHAT’S AN AST?

LEFT CHILD

WHAT’S AN AST?

LEFT CHILD RIGHT CHILD

WHAT’S AN AST?

5 * (6 + 3)

WHAT’S AN AST?

SELECT id, text FROM posts

SELECT

id text

<query>

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

Post.arel_table[:id]

TABLES AND COLUMNS

Post.arel_table[:id]Post.arel_table[:text]

TABLES AND COLUMNS

=> #<struct Arel::Attributes::Attribute ... >

TABLES AND COLUMNS

class Post < ActiveRecord::Base include ArelHelpers::ArelTable has_many :commentsend

TABLES AND COLUMNS

class Post < ActiveRecord::Base include ArelHelpers::ArelTable has_many :commentsend

Post[:id]Post[:text]

RELATIONS

POP QUIZ! WHAT DOES THIS STATEMENT RETURN?

Post.select(:title)

RELATIONS

Post.select(:title)A. [“Rails is Cool” ... ]

RELATIONS

Post.select(:title)A. [“Rails is Cool” ... ]B. [#<Post title=”Rails is Cool”>, ... ]

RELATIONS

C. <ActiveRecord::Relation ... >B. [#<Post title=”Rails is Cool”>, ... ]

RELATIONS

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.to_sqlquery = query.select(:id)

RELATIONS

query.to_sql=> SELECT title, id FROM `posts`

query = query.select(:id)

RELATIONS

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).count.to_sql=> NoMethodError: undefined method `to_sql' for 26:Fixnum

SELECT

WHAT HAPPENED??

SELECT

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

first, last

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_slice(3)

TERMINAL METHODS

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].maximum).to_sql=> SELECT MAX(`posts`.`visitors`) AS max_id FROM `posts`

SELECT

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

include Arel::Nodes

SELECT

Post.select( NamedFunction.new( "LENGTH", [Post[:text]] ).as("length")).to_sql

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”

Post.where(title: "Arel is Cool").to_sql=> SELECT ùsers`.* FROM ùsers` WHERE ùsers`.`title` = 'Arel is Cool'

WITH ACTIVERECORD SUGAR

Post.where(title: "Arel is Cool").to_sql=> SELECT ùsers`.* FROM ùsers` WHERE ùsers`.`title` = 'Arel is Cool'

WITH ACTIVERECORD SUGAR

Post.where(Post[:title].eq("Arel is Cool")).to_sql=> SELECT ùsers`.* FROM ùsers` WHERE ùsers`.`title` = 'Arel is Cool'

WITH AREL

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)

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)

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)

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

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

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”

class Comment < ActiveRecord::Base belongs_to :post has_one :authorend

class Author < ActiveRecord::Base belongs_to :commentend

Author.joins(:comment).where(id: 42).to_sql

=> SELECT àuthors`.* FROM àuthors` INNER JOIN `comments` ON `comments`.ìd` = àuthors`.`comment_id` WHERE àuthors`.ìd` = 42

Author .joins(:comment, :comment => :post) .where(Post[:id].eq(42)) .to_sql

=> SELECT àuthors`.* FROM àuthors` INNER JOIN `comments` ON `comments`.ìd` = àuthors`.`comment_id` INNER JOIN `posts` ON `posts`.ìd` = `comments`.`post_id` WHERE `posts`.ìd` = 42

WAIT, WHAT ABOUT OUTER JOINS?

Author .joins(:comment, :comment => :post) .where(Post[:id].eq(42)) .to_sql

Author .joins(:comment) .joins(Comment.joins(:post).join_sources) .where(Post[:id].eq(42)) .to_sql

Author .joins(Author.joins(:comment).join_sources) .joins(Comment.joins(:post).join_sources) .where(Post[:id].eq(42)) .to_sql

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

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

=> SELECT àuthors`.* FROM àuthors` LEFT OUTER JOIN `comments` ON `comments`.ìd` = àuthors`.`comment_id` LEFT OUTER JOIN `posts` ON `posts`.ìd` = `comments`.`post_id` WHERE `posts`.ìd` = 42

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

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

Author .joins(join_association(Author, :comment, Arel::OuterJoin)) .joins(join_association(Comment, :post, Arel::OuterJoin)) .where(Post[:id].eq(42)) .to_sql

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

=> SELECT àuthors`.* FROM àuthors` INNER JOIN `comments` ON `comments`.ìd` = àuthors`.`comment_id` AND `comments`.`created_at` <= '2014-04-15' LEFT OUTER JOIN `posts` ON `posts`.ìd` = `comments`.`post_id` WHERE `posts`.ìd` = 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

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

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”

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(: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”

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

private

def with_title_matching(title) reflect( query.where(post[:title].matches("%#{title}%")) )end

private

def with_comments_by(usernames) reflect( query .joins(:comments => :author) .where(Author[:username].in(usernames)) )end

private

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`.ìd` INNER JOIN àuthors` ON àuthors`.`comment_id` = `comments`.ìd` WHERE àuthors`.ùsername` 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`.ìd` INNER JOIN àuthors` ON àuthors`.`comment_id` = `comments`.ìd` WHERE àuthors`.ùsername` 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`.ìd` INNER JOIN àuthors` ON àuthors`.`comment_id` = `comments`.ìd` WHERE àuthors`.ùsername` 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...“

SCUTTLEwww.scuttle.io

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!

advanced arel: when activerecord just isn't enough

querybuilder

relations

mysql syntax

end def withtitlematching

base include

end def withcommentsby

tosql include

undefined

Software

activerecord & arel

arel design katalog

model driven activerecord with yed - university of...

arel university

arel brochure

activerecord query interface (2), season 2

arel and rails 2

arel - 18 aprile, 2013

where's my sql? designing databases with activerecord...

lecture3 activerecord–railspersistence layer -...

replacing activerecord with datamapper

disconnecting the database with activerecord

guide arel

activerecord callbacks. Готовим правильно

refactoring activerecord models

dakar arel report

implementation of eav pattern for activerecord models

arel industriale

arel distance education center

using arel to refactor big queries