Monday, October 28, 2013

Using the Ruby Sequel Gem to Build Dynamic Filters

The Ruby Sequel library has become my favorite tool for interacting with relational databases. In my opinion, it has the right blend of abstraction to simplify common database CRUD operations while maintaining plenty of control when you need to perform more complex queries. While powerful, its taken a little time studying the documentation to learn its secrets. There's not a lot of external knowledge available to draw from when you can't figure something out. I know how to write SQL but translating that into the Sequel DSL has been challenging at times. I've spent a lot of time in the Ruby console trying different ways of assembling an equivalent SQL statement with Sequel that I can write in a minute or two in the PostgreSQL console. While Sequel will let you just pass a SQL string to it, when you need to assemble that string with conditional components, it makes less sense. After all, that's what Sequel is doing for you internally. You just have to figure out how to pass in those components so Sequel knows how to build the final SQL.

As a specific problem, consider a simple example where you have a table that has several text columns against which you want to allow a user to perform a search. They can choose to search on one or all of the columns, however, they are bound to the records that they are marked as the "owner" based on their user ID. Here is how the SQL might look if you just wrote it using all four of the possible fields to search:

FROM my_table
   owner_id = :user_id AND
   ( text_field1 ILIKE '%:search_term%' OR
     text_field2 ILIKE '%:search_term%' OR
     text_field3 ILIKE '%:search_term%' OR
     text_field4 ILIKE '%:search_term%' )

Constraining the user is easy by itself with Sequel:

# userid is set, session or otherwise
DB[:my_table].where( :owner_id => userid )

The problem that arises now is how to AND those like conditions onto the statement. If you simply add an or() function to the end of the prior line (below, "term" is from the query string in the request and is normalized to lower case and enclosed in '%'):

   where( :owner_id => userid ).
   or{ text_field1.ilike( term ) }.
   or{ text_field2.ilike( term ) }

You'd get an SQL statement like this:

FROM my_table
   owner_id = 100 OR
   text_field1 ILIKE '%foo%'OR
   text_field2 ILIKE '%foo%'

Which will result in either all the owner records or the ones that match "foo". The non-dynamic solution is to use the and() function with a block that combines those conditions using the "|" operator:
   where( :owner_id => userid ).
   and{ text_field1.ilike( term ) | text_field2.ilike( term ) }

Ok, getting closer but this only works if you always want all the text fields to be searched. Early I stated that user can select which fields to include in the search which means these need to be conditionally added to that "and" block. After some studying, I finally found the solution. It took realizing how two different parts of the documentation were related but involves the fact that you can assemble atomic expressions and pass them around to build more complex expressions. The final solution uses a "scope" variable that was provided in the page request and represents an array of fields to search. We can now build an array of expressions and pass that array to Sequel.| to generate the final query:

filters = []
filters.push( Sequel.ilike( :text_field1,  term ) ) if scope.include?( 'text_field1' )
filters.push( Sequel.ilike( :text_field2,  term ) ) if scope.include?( 'text_field2' )
filters.push( Sequel.ilike( :text_field3,  term ) ) if scope.include?( 'text_field3' )
filters.push( Sequel.ilike( :text_field4,  term ) ) if scope.include?( 'text_field4' )

ds = DB[:my_table].where( :owner_id => userid ).and{ Sequel.|( *filters ) }


Once I start to connect those dots, it became a lot more clear how to utilize Sequel to dynamically build queries. It sure beats the string concatenations I use to do while still maintaining a reasonable amount of similarity to the SQL syntax. As always, there's probably more than one way to solve a problem. This one met my needs and has been flexible enough to adapt to new requirements. New conditions can simply be added to the existing chain of AND'd conditions or pushed into the OR'd array.