Save 37% off PRO during our Black Friday Sale! »

Getting to know Arel: Active Record's nerdy little brother

Getting to know Arel: Active Record's nerdy little brother

Presented at PromptWorks By Ray Zane. https://promptworks.com

99e2a6afab542ba98a9f1d1cae6c9670?s=128

PromptWorks

August 02, 2016
Tweet

Transcript

  1. Getting to know Arel Active Record's nerdy little brother

  2. 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
  3. Active Record is pretty good...

  4. 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 '
  5. 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
  6. 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 ' )
  7. 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 # )
  8. Active Record is pretty good, until it isn't.

  9. 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
  10. 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 '
  11. 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 ' )
  12. 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 ' )
  13. 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
  14. 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
  15. Arel to the rescue!

  16. 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
  17. 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 '
  18. 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 # ) # )
  19. 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 "
  20. 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 "
  21. 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
  22. Arel can do anything!

  23. 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 "
  24. 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 "
  25. 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 . . .
  26. Am I just wasting your time? (this Arel stuff is

    pretty verbose)
  27. Introducing Baby Squeel

  28. Extremely similar to Squeel Under 500 LOC No core exts

    No monkey patches! As conservative as possible
  29. 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 ' )
  30. 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 # ) # )
  31. 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 "
  32. 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 "
  33. 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 "
  34. 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 "
  35. 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
  36. Thanks. Now, please stop using strings to generate SQL.