LiquidPlanner for 5 years. • I got off in Kansas*, and that's ok! • Github: adamsanderson • Twitter: adamsanderson • Blog: http://monkeyandcrow.com * Seattle 2
cover its use in ActiveRecord. We will use Postgres 9.x, Ruby 1.9 syntax, and ActiveRecord 4.0. If you understand the SQL you can use it in any version of ActiveRecord, 4.0 just makes it easier. 5
ones how can I find him friends? hedgehogs4life Tagging People want to be able to tag their hedgehogs, and then find other hedgehogs with certain tags. “ 7
_ t a b l e : h e d g e h o g s d o | t | t . s t r i n g : n a m e t . i n t e g e r : a g e t . t e x t : t a g s , a r r a y : t r u e e n d ActiveRecord 4.x introduced arrays for Postgres, use a r r a y : t r u e 9
x t instead of t . s t r i n g to avoid casting. Postgres assumes that A R R A Y [ ' c u t e ' , ' c u d d l y ' ] is of type t e x t [ ] and will require you to cast, otherwise you will see errors like this: E R R O R : o p e r a t o r d o e s n o t e x i s t : c h a r a c t e r v a r y i n g [ ] & & t e x t [ ] 10
spiny or prickly: S E L E C T n a m e , t a g s F R O M h e d g e h o g s W H E R E t a g s & & A R R A Y [ ' s p i n y ' , ' p r i c k l y ' ] ; A & & B A overlaps any of B 12
spiny and prickly: S E L E C T n a m e , t a g s F R O M h e d g e h o g s W H E R E t a g s @ > A R R A Y [ ' s p i n y ' , ' p r i c k l y ' ] ; A @ > B A contains all the B 14
c l a s s H e d g e h o g < A c t i v e R e c o r d : : B a s e s c o p e : a n y _ t a g s , - > ( * t a g s ) { w h e r e ( ' t a g s & & A R R A Y [ ? ] ' , t a g s ) } s c o p e : a l l _ t a g s , - > ( * t a g s ) { w h e r e ( ' t a g s @ > A R R A Y [ ? ] ' , t a g s ) } e n d 17
all use HedgeWith.me, if they could show which hogs are members of our selective society. Boston Spine Fancy President Hierarchy Apparently there are thousands of hedgehog leagues, divisions, societies, clubs, and so forth. “ 19
this: • North American League • Western Division • Cascadia Hog Friends • Californian Hedge Society How can we support operations like finding a club's depth, children, or parents? 20
League [1] 2 Eastern Division [1,2] 4 New York Quillers [1,2,4] 5 Boston Spine Fancy [1,2,5] 3 Western Division [1,3] 6 Cascadia Hog Friends [1,3,6] 7 California Hedge Society [1,3,7] ... 22
the length of its path. • a r r a y _ l e n g t h ( a r r a y , d i m ) returns the length of the array d i m will always be 1 unless you are using multidimensional arrays. 23
clubs: S E L E C T n a m e , p a t h , a r r a y _ l e n g t h ( p a t h , 1 ) A S d e p t h F R O M c l u b s W H E R E a r r a y _ l e n g t h ( p a t h , 1 ) < = 2 O R D E R B Y p a t h ; a r r a y _ l e n g t h ( p a t h , 1 ) is the depth of record 24
of the California Hedge Society, ID: 7. S E L E C T i d , n a m e , p a t h F R O M c l u b s W H E R E p a t h & & A R R A Y [ 7 ] O R D E R B Y p a t h A & & B A overlaps any of B 26
Society, Path: A R R A Y [ 1 , 3 , 7 ]. S E L E C T n a m e , p a t h F R O M c l u b s W H E R E A R R A Y [ i d ] & & A R R A Y [ 1 , 3 , 7 ] O R D E R B Y p a t h ; A & & B A overlaps any of B 28
l a s s C l u b < A c t i v e R e c o r d : : B a s e d e f c h i l d r e n C l u b . w h e r e ( ' p a t h & & A R R A Y [ ? ] ' , s e l f . i d ) e n d d e f p a r e n t s C l u b . w h e r e ( ' A R R A Y [ i d ] & & A R R A Y [ ? ] ' , s e l f . p a t h ) e n d 31
query the hierarchy. @ c l u b . p a r e n t s . l i m i t ( 5 ) @ c l u b . c h i l d r e n . j o i n s ( : h e d g e h o g s ) . m e r g e ( H e d g e h o g . a n y _ t a g s ( ' s i l l y ' ) ) These features can all work together. Mind blown? 32
colors, weight, eye color, and shoe sizes! the Quantified Hedgehog Owner If I am forced to enter my hedgehog's shoe size, I will quit immediately! the Unquantified Hedgehog Owner Custom Data Your users want to record arbitrary data about their hedgehogs. “ 33
look like this: c l a s s I n s t a l l H s t o r e < A c t i v e R e c o r d : : M i g r a t i o n d e f u p e x e c u t e ' C R E A T E E X T E N S I O N h s t o r e ' e n d . . . 35
default schema format does not support extensions. Update c o n f i g / a p p l i c a t i o n . r b to use the SQL schema format, otherwise your tests will fail. c l a s s A p p l i c a t i o n < R a i l s : : A p p l i c a t i o n c o n f i g . a c t i v e _ r e c o r d . s c h e m a _ f o r m a t = : s q l e n d 36
E T A B L E h e d g e h o g s ( i d i n t e g e r p r i m a r y k e y , n a m e t e x t , a g e i n t e g e r , t a g s t e x t [ ] , c u s t o m h s t o r e D E F A U L T ' ' N O T N U L L ) ; 37
e is supported in ActiveRecord 4.x as a normal column type: c r e a t e _ t a b l e : h e d g e h o g s d o | t | t . s t r i n g : n a m e t . i n t e g e r : a g e t . t e x t : t a g s , a r r a y : t r u e t . h s t o r e : c u s t o m , : d e f a u l t = > ' ' , : n u l l = > f a l s e e n d 38
lot like a ruby 1.8 hash: U P D A T E h e d g e h o g s S E T c u s t o m = ' " f a v o r i t e _ f o o d " = > " l e m o n s " , " w e i g h t " = > " 2 l b s " ' W H E R E i d = 1 ; Be careful of quoting. 40
the hedgehogs: S E L E C T n a m e , c u s t o m - > ' f a v o r i t e _ f o o d ' A S f o o d F R O M h e d g e h o g s W H E R E d e f i n e d ( c u s t o m , ' f a v o r i t e _ f o o d ' ) ; d e f i n e d ( A , B ) Does A have B? A - > B Get B from A. In ruby this would be A[B] 42
c l a s s H e d g e h o g < A c t i v e R e c o r d : : B a s e s c o p e : h a s _ k e y , - > ( k e y ) { w h e r e ( ' d e f i n e d ( c u s t o m , ? ) ' , k e y ) } s c o p e : h a s _ v a l u e , - > ( k e y , v a l u e ) { w h e r e ( ' c u s t o m - > ? = ? ' , k e y , v a l u e ) } . . . 44
are just hashes: h e d g e h o g . c u s t o m [ " f a v o r i t e _ c o l o r " ] = " o c h r e " h e d g e h o g . c u s t o m = { f a v o r i t e _ f o o d : " P e a n u t s " , s h o e _ s i z e : 3 } 48
e d g e h o g . c u s t o m [ " w e i g h t " ] = 3 h e d g e h o g . s a v e ! h e d g e h o g . r e l o a d h e d g e h o g . c u s t o m [ ' w e i g h t ' ] . c l a s s # = > S t r i n g 49
convert strings into these types: • t o _ t s v e c t o r ( c o n f i g u r a t i o n , t e x t ) creates a normalized t s v e c t o r • t o _ t s q u e r y ( c o n f i g u r a t i o n , t e x t ) creates a normalized t s q u e r y 53
e l e c t t o _ t s v e c t o r ( ' A b o y a n d h i s h e d g e h o g w e n t t o P o r t l a n d ' ) ; - - b o y , h e d g e h o g , p o r t l a n d , w e n t s e l e c t t o _ t s v e c t o r ( ' I n e e d a s e c o n d l i n e t o f i l l s p a c e h e r e . ' ) ; - - f i l l , l i n e , n e e d , s e c o n d , s p a c e 54
E L E C T b o d y F R O M c o m m e n t s W H E R E t o _ t s v e c t o r ( ' e n g l i s h ' , b o d y ) @ @ t o _ t s q u e r y ( ' e n g l i s h ' , ' e n j o y i n g ' ) ; V @ @ Q Searches V for Q 57
loves them • I really enjoy oranges • I am enjoying these photos of your hedgehog's beady little eyes • Can I feed him grapes? I think he enjoys them. Notice how "enjoying" also matched "enjoy" and "enjoys" due to stemming. 58
s q u e r y ( ' e n g l i s h ' , ' c a t : * ' ) Searches for anything starting with cat Such as: cat, catapult, cataclysmic. But not: octocat, scatter, prognosticate 59
"oil", and a word starting with "quil" : S E L E C T b o d y F R O M c o m m e n t s W H E R E t o _ t s v e c t o r ( ' e n g l i s h ' , b o d y ) @ @ ( t o _ t s q u e r y ( ' e n g l i s h ' , ' o i l ' ) & & t o _ t s q u e r y ( ' e n g l i s h ' , ' q u i l : * ' ) ) ; V @ @ ( A & & B ) Searches V for A and B 60
supports wildcards at the end of a term. While q u i l l : * will match "QuillSwill", but * : s w i l l will not. In fact, * : s w i l l will throw an error. 62
t o _ t s q u e r y, it has a strict mini search syntax. The following all fail: • h t t p : / / l o c a l h o s t : has a special meaning • O ' R e i l l y ' s B o o k s Paired quotes cannot be in the middle • A & & B & and | are used for combining terms You need to sanitize queries, or use a gem that does this for you. 63
in a scope. c l a s s C o m m e n t < A c t i v e R e c o r d : : B a s e s c o p e : s e a r c h _ a l l , - > ( q u e r y ) { w h e r e ( " t o _ t s v e c t o r ( ' e n g l i s h ' , b o d y ) @ @ # { s a n i t i z e _ q u e r y ( q u e r y ) } " ) } You need to write s a n i t i z e _ q u e r y, or use a gem that does this for you. 64
oil again, and limit it to 5 results: C o m m e n t . s e a r c h _ a l l ( " q u i l * o i l " ) . l i m i t ( 5 ) Since s e a r c h _ a l l is a scope, we chain it like all the other examples. 65
call t o _ t s v e c t o r ( ' e n g l i s h ' , b o d y ): C R E A T E I N D E X c o m m e n t s _ g i n _ i n d e x O N c o m m e n t s U S I N G g i n ( t o _ t s v e c t o r ( ' e n g l i s h ' , b o d y ) ) ; The g i n index is a special index for multivalued columns like a t e x t [ ] or a t s v e c t o r 66
o _ t s v e c t o r ( ' e n g l i s h ' , b o d y ), we must call it the same way every time. You don't have to use e n g l i s h, but you do need to be consistent. 67
Hstore can be used to model custom data • Postgres supports full text search You can now enjoy the happy hour! S E L E C T * F R O M b e e r s W H E R E t r a i t s @ > A R R A Y [ ' h o p p y ' , ' f l o r a l ' ] 68
instead of using arrays? • Can I see how you implemented s a n i t i z e _ q u e r y? • What is a good gem for full text search? • What about ActiveRecord 2 and 3? • Why hstore instead of JSON? • Can I buy you coffee? 69
Operators • Postgres Hstore Documentation • Postgres Full Text Search • Ruby Gems for Full Text Search • Textacular Supports Active Record 2.x and 3.x • pg_search Supports Active Record 3.x, but has more features • My Blog, Github, and favorite social network • How to draw a hedgehog. 70
_ q u e r y: d e f s e l f . s a n i t i z e _ q u e r y ( q u e r y , c o n j u n c t i o n = ' & & ' ) " ( " + t o k e n i z e _ q u e r y ( q u e r y ) . m a p { | t | t e r m ( t ) } . j o i n ( c o n j u n c t i o n ) + " ) " e n d It breaks up the user's request into terms, and then joins them together. 71
s e l f . t e r m ( t ) # S t r i p l e a d i n g a p o s t r o p h e s , t h e y a r e n e v e r l e g a l , " ' o k " b e c o m e s " o k " t = t . g s u b ( / ^ ' + / , ' ' ) # S t r i p a n y * s t h a t a r e n o t a t t h e e n d o f t h e t e r m t = t . g s u b ( / \ * [ ^ $ ] / , ' ' ) # R e w r i t e " s e a r * " a s " s e a r : * " t o s u p p o r t w i l d c a r d m a t c h i n g o n t e r m s t = t . g s u b ( / \ * $ / , ' : * ' ) . . . 73
n l y r e m a i n i n g t e x t i s a w i l d c a r d , r e t u r n a n e m p t y s t r i n g t = " " i f t . m a t c h ( / ^ [ : * ] + $ / ) " t o _ t s q u e r y ( ' e n g l i s h ' , # { q u o t e _ v a l u e t } ) " e n d 74