Tweet
Tweet

Slide 1

Slide 1 text

Getting to know Arel Active Record's nerdy little brother

Slide 2

Slide 2 text

What is Arel? Arel is a SQL AST manager for Ruby Active Record uses Arel to build queries It adapts to various RDBMSes It is intended to be a framework framework

Slide 3

Slide 3 text

Active Record is pretty good...

Slide 4

Slide 4 text

D o g . w h e r e ( n a m e : ' F i d o ' ) # W H E R E " d o g s " . " n a m e " = ' F i d o '

Slide 5

Slide 5 text

D o g . w h e r e ( n a m e : n i l ) # W H E R E " d o g s " . " n a m e " I S N U L L

Slide 6

Slide 6 text

D o g . w h e r e ( n a m e : [ ' F i d o ' , ' J e f f ' ] ) # W H E R E " d o g s " . " n a m e " I N ( ' F i d o ' , ' J e f f ' )

Slide 7

Slide 7 text

D o g . w h e r e ( n a m e : [ ' F i d o ' , ' J e f f ' , n i l ] ) # W H E R E ( # " d o g s " . " n a m e " I N ( ' F i d o ' , ' J e f f ' ) O R # " d o g s " . " n a m e " I S N U L L # )

Slide 8

Slide 8 text

Active Record is pretty good, until it isn't.

Slide 9

Slide 9 text

It only supports equality D o g . w h e r e ( ' a g e > ? ' , 5 ) # W H E R E a g e > 5

Slide 10

Slide 10 text

No support for OR (until 5.0.0) D o g . w h e r e ( ' a g e = ? O R n a m e = ? ' , 5 , ' F i d o ' ) # W H E R E a g e = 5 O R n a m e = ' F i d o '

Slide 11

Slide 11 text

No support for explicit joins D o g . j o i n s ( ' I N N E R J O I N o w n e r s o O N o . i d = d o g s . o w n e r _ i d ' )

Slide 12

Slide 12 text

No outer joins (without loading everything into memory) D o g . j o i n s ( ' L E F T O U T E R J O I N o w n e r s O N o w n e r s . i d = d o g s . o w n e r _ i d ' )

Slide 13

Slide 13 text

Composability goes out the window c l a s s D o g < A c t i v e R e c o r d : : B a s e s c o p e : o l d , - > { w h e r e ( ' a g e > ? ' , 5 ) } s c o p e : n a m e d _ f i d o , - > { w h e r e ( n a m e : ' F i d o ' ) } d e f s e l f . n a m e d _ f i d o _ o r _ o l d w h e r e ( ' a g e > ? O R n a m e = ? ' , 5 , ' F i d o ' ) e n d e n d

Slide 14

Slide 14 text

Not to mention... Not database agnostic No syntax checking Question marks can be tough to track down What ever happened to the 80 characters/line? M o d e l . j o i n s ( ' L E F T J O I N c a n d i d a c i e s a s s e a r c h _ c a n d i d a t e s o n s e a r c h _ c a n d i d a t e s . c o n t a c t _ i d = c o n t a c t s . i d ' . j o i n s ( ' L E F T J O I N s e a r c h e s a s c o n t a c t _ s e a r c h e s o n s e a r c h _ c a n d i d a t e s . s e a r c h _ i d = c o n t a c t _ s e a r c h e s . i d ' . w h e r e ( ' ( l o w e r ( c o n t a c t _ s e a r c h e s . n a m e ) l i k e ? A N D s e a r c h _ c a n d i d a t e s . d e l e t e d = ? ) ' , " % # { n a m e } % " . d o w n c a s e

Slide 15

Slide 15 text

Arel to the rescue!

Slide 16

Slide 16 text

Arel::Table A r e l : : T a b l e . n e w ( : d o g s ) A r e l : : T a b l e . n e w ( : d o g s ) [ : n a m e ] # a n A r e l : : A t t r i b u t e D o g . a r e l _ t a b l e D o g . a r e l _ t a b l e [ : n a m e ] # a n A r e l : : A t t r i b u t e

Slide 17

Slide 17 text

Predications a g e = D o g . a r e l _ t a b l e [ : a g e ] n a m e = D o g . a r e l _ t a b l e [ : n a m e ] D o g . w h e r e a g e . g t ( 5 ) # W H E R E " d o g s " . " a g e " > 5 D o g . w h e r e a g e . n o t _ e q ( 5 ) # W H E R E " d o g s " . " a g e " ! = 5 D o g . w h e r e n a m e . m a t c h e s ( ' % i d o ' ) # W H E R E " d o g s " . " n a m e " L I K E ' % i d o '

Slide 18

Slide 18 text

Grouping i d = D o g . a r e l _ t a b l e [ : i d ] a g e = D o g . a r e l _ t a b l e [ : a g e ] n a m e = D o g . a r e l _ t a b l e [ : n a m e ] D o g . w h e r e i d . g t ( 5 ) . a n d ( n a m e . e q ( ' R o n a l d ' ) . o r ( a g e . e q ( 3 ) ) ) # W H E R E ( # " d o g s " . " i d " > 5 A N D ( # " d o g s " . " n a m e " = ' R o n a l d ' O R # " d o g s " . " a g e " = 3 # ) # )

Slide 19

Slide 19 text

Inner Join d o g s = D o g . a r e l _ t a b l e o w n e r s = O w n e r . a r e l _ t a b l e j o i n = d o g s . i n n e r _ j o i n ( o w n e r s ) . o n ( d o g s [ : o w n e r _ i d ] . e q ( o w n e r s [ : i d ] ) ) D o g . j o i n s j o i n . j o i n _ s o u r c e s # S E L E C T " d o g s " . * F R O M " d o g s " # I N N E R J O I N " o w n e r s " # O N " d o g s " . " o w n e r _ i d " = " o w n e r s " . " i d "

Slide 20

Slide 20 text

Outer Join d o g s = D o g . a r e l _ t a b l e o w n e r s = O w n e r . a r e l _ t a b l e j o i n = d o g s . o u t e r _ j o i n ( o w n e r s ) . o n ( d o g s [ : o w n e r _ i d ] . e q ( o w n e r s [ : i d ] ) ) D o g . j o i n s j o i n . j o i n _ s o u r c e s # S E L E C T " d o g s " . * F R O M " d o g s " # L E F T O U T E R J O I N " o w n e r s " # O N " d o g s " . " o w n e r _ i d " = " o w n e r s " . " i d "

Slide 21

Slide 21 text

Composability c l a s s D o g < A c t i v e R e c o r d : : B a s e s c o p e : o l d , - > { w h e r e ( o l d _ a r e l ) } s c o p e : n a m e d _ f i d o , - > { w h e r e ( n a m e d _ f i d o _ a r e l ) } d e f s e l f . o l d _ o r _ n a m e d _ f i d o w h e r e n a m e d _ f i d o _ a r e l . o r ( o l d _ a r e l ) e n d d e f s e l f . o l d _ a r e l a r e l _ t a b l e [ : a g e ] . g t ( 5 ) e n d d e f s e l f . n a m e d _ f i d o _ a r e l a r e l _ t a b l e [ : n a m e ] . e q ( ' F i d o ' ) e n d e n d

Slide 22

Slide 22 text

Arel can do anything!

Slide 23

Slide 23 text

Aggregates D o g . s e l e c t D o g . a r e l _ t a b l e [ : a g e ] . m a x i m u m # S E L E C T M A X ( " d o g s " . " a g e " ) F R O M " d o g s "

Slide 24

Slide 24 text

Functions D o g . s e l e c t ( A r e l : : N o d e s : : N a m e d F u n c t i o n . n e w ( ' C O A L E S C E ' , [ D o g . a r e l _ t a b l e [ : n a m e ] , A r e l : : N o d e s . b u i l d _ q u o t e d ( ' R o n a l d ' ) ] ) ) # S E L E C T C O A L E S C E ( " d o g s " . " n a m e " , ' R o n a l d ' ) # F R O M " d o g s "

Slide 25

Slide 25 text

Infix D o g . s e l e c t ( A r e l : : N o d e s : : I n f i x O p e r a t i o n . n e w ( ' | | ' , D o g . a r e l _ t a b l e [ : n a m e ] , A r e l : : N o d e s . b u i l d _ q u o t e d ( ' d i d d l y ' ) ) ) # S E L E C T " d o g s " . " n a m e " | | ' d i d d l y ' # F R O M " d o g s " D o g . s e l e c t ( " n a m e | | ' d i d d l y ' " ) # S E L E C T . . . . n e v e r m i n d . . .

Slide 26

Slide 26 text

Am I just wasting your time? (this Arel stuff is pretty verbose)

Slide 27

Slide 27 text

Introducing Baby Squeel

Slide 28

Slide 28 text

Extremely similar to Squeel Under 500 LOC No core exts No monkey patches! As conservative as possible

Slide 29

Slide 29 text

Predications D o g . w h e r e . h a s { a g e > 5 } # W H E R E ( " d o g s " . " a g e " > 5 ) D o g . w h e r e . h a s { n a m e ! = ' J e f f ' } # W H E R E ( " d o g s " . " n a m e " ! = ' J e f f ' ) D o g . w h e r e . h a s { n a m e = ~ ' % i d o ' } # W H E R E ( " n a m e " L I K E ' % i d o ' )

Slide 30

Slide 30 text

Grouping D o g . w h e r e . h a s { ( i d > 5 ) . a n d ( ( n a m e = = ' R o n a l d ' ) . o r ( a g e = = 3 ) ) } # W H E R E ( # " d o g s " . " i d " > 5 A N D ( # " d o g s " . " n a m e " = ' R o n a l d ' O R # " d o g s " . " a g e " = 3 # ) # )

Slide 31

Slide 31 text

D o g . s e l e c t i n g { a g e . m a x i m u m } # S E L E C T M A X ( " d o g s " . " a g e " ) F R O M " d o g s " D o g . s e l e c t i n g { ( i d + 1 0 0 ) / 2 0 } # S E L E C T ( " d o g s " . " i d " + 1 0 0 ) / 2 0 F R O M " d o g s " D o g . s e l e c t i n g { c o a l e s c e ( n a m e , q u o t e d ( ' R o n a l d ' ) ) } # S E L E C T c o a l e s c e ( " d o g s " . " n a m e " , ' R o n a l d ' ) F R O M " d o g s " D o g . s e l e c t i n g { n a m e . o p ( ' | | ' , q u o t e d ( ' d i d d l y ' ) ) } # S E L E C T " d o g s " . " n a m e " | | ' d i d d l y ' F R O M " d o g s "

Slide 32

Slide 32 text

Explicit Joins D o g . j o i n i n g { o w n e r . o n ( o w n e r _ i d = = o w n e r . i d ) } # I N N E R J O I N " o w n e r s " # O N " d o g s " . " o w n e r _ i d " = " o w n e r s " . " i d " D o g . j o i n i n g { o w n e r . o u t e r . o n ( o w n e r . i d = = o w n e r _ i d ) } # L E F T O U T E R J O I N " o w n e r s " # O N " d o g s " . " o w n e r _ i d " = " o w n e r s " . " i d "

Slide 33

Slide 33 text

Implicit Joins c l a s s D o g < A c t i v e R e c o r d : : B a s e b e l o n g s _ t o : o w n e r e n d D o g . j o i n i n g { o w n e r } # I N N E R J O I N " o w n e r s " # O N " o w n e r s " . " i d " = " d o g s " . " o w n e r _ i d " D o g . j o i n i n g { o w n e r . o u t e r } # L E F T O U T E R J O I N " o w n e r s " # O N " o w n e r s " . " i d " = " d o g s " . " o w n e r _ i d "

Slide 34

Slide 34 text

Join like a BO$$ D o g . j o i n i n g { o w n e r . d o g . o u t e r . o w n e r . d o g } # S E L E C T " d o g s " . * F R O M " d o g s " # I N N E R J O I N " o w n e r s " # O N " o w n e r s " . " i d " = " d o g s " . " o w n e r _ i d " # L E F T O U T E R J O I N " d o g s " " d o g s _ o w n e r s " # O N " d o g s _ o w n e r s " . " o w n e r _ i d " = " o w n e r s " . " i d " # I N N E R J O I N " o w n e r s " " o w n e r s _ d o g s " # O N " o w n e r s _ d o g s " . " i d " = " d o g s _ o w n e r s " . " o w n e r _ i d " # I N N E R J O I N " d o g s " " d o g s _ o w n e r s _ 2 " # O N " d o g s _ o w n e r s _ 2 " . " o w n e r _ i d " = " o w n e r s _ d o g s " . " i d "

Slide 35

Slide 35 text

Composability c l a s s D o g < A c t i v e R e c o r d : : B a s e s i f t e r ( : o l d ) { a g e > 5 } s i f t e r ( : n a m e d _ f i d o ) { n a m e = = ' F i d o ' } s c o p e : o l d , - > { w h e r e . h a s { s i f t ( : o l d ) } } s c o p e : n a m e d _ f i d o , - > { w h e r e . h a s { s i f t ( : n a m e d _ f i d o ) } } d e f s e l f . o l d _ o r _ n a m e d _ f i d o w h e r e . h a s { s i f t ( : o l d ) | s i f t ( : n a m e d _ f i d o ) } e n d e n d

Slide 36

Slide 36 text

Thanks. Now, please stop using strings to generate SQL.