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.

Monday, October 21, 2013

Simulate Bootstrap Focus Effect on Non-Form Controls

While working on the MultiSearch contact demo, I wanted the widget to show the nice blue focus outline like other input boxes that have the Bootstrap form-control class. However, I did not want that effect on the input box used in the search. Instead, I wanted it on the DIV with the panel class which was defining the bounds of the widget. That means both focusing on the input and any interaction inside the panel's bounds should trigger the focus effect. It turns out that managing any clicks inside the widget were pretty easy to handle. However, since there is an input box inside the widget, it has a tab stop which allows a user to use the keyboard to move the focus on and off the input. I was trying really hard to keep this as simple as possible but focus/blur events are inherently difficult since they don't bubble up through the DOM. While jQuery solves this by mapping those events to focusin/out, listening to multiple event types (click and focusin) really caused a lot of headaches.

The solution I eventually settled on uses one click event handler on the MultiSearch widget container and then two handlers on the BODY element to catch click and focusin events. The former is used to turn off the focus when the click occurs outside of the widget and the latter is used to detect tabbing into an element that triggers a focus event. That focusin handler simply triggers a click event on the target so, if the target is inside a MultiSearch widget, it will cause the click handler to fire which enable the focus effect and, if applicable, remove it from another widget.

That's the description of the solution, here's the actual implementation. The MultiSearch widgets markup on the contacts demo looks like this:

There are three of these on the page and each will need the click handler to enable the focus effect. For simplicity, let's assume those three elements are already selected into a variable named $fields. The handler can be bound on that set:

      $fields.on( 'click', function simulateFocus( event ) {

            // $me is the widget container element.
            // $panel is what we want to add/remove the focus styling

            var $me = $( this ),
                $panel = $me.find( '.panel' );

            // Only if focus hasn't already been activated.  We don't need
            // multiple handlers listening to remove the focus class. 
            if ( !$ '.focus' ) ) {

               $panel.addClass( 'focus' );

               // Several things are happening here:
               //  1) This click event is still bubbling, listen to
               //     click now, and it will be caught before the popover
               //     ever appears.  Deferring it pushes the execution outside
               //     of the current call stack
               //  2) Clicks inside the popover are fine.  Use the $.has() function
               //     to see if any part of the target is or is inside the popover
               //     element.  Only remove if that is not true.
               _.defer( function() {
                  $( document.body ).on( 'click.focus', function( e ) {
                     if ( $me.has( ).length === 0 ) {
                        $panel.removeClass( 'focus' );
                        $( document.body ).off( 'click.focus' );


That takes care of everything except the focus event triggered when a user tabs into a field. The simplest solution is to just listen to every focusin event on the page and turn that into a click event on that element:

   $( document.body ).on( 'focusin', 'input, textarea', function( event ) {
      $( ).trigger( 'click' );

As a final note, here are the styles required to enable the effect on the panel class. These are copied from the form-control class:

.panel {
   margin: 0;
   transition: border-color 0.15s ease-in-out 0s, box-shadow 0.15s ease-in-out 0s;

.panel.focus {
   border-color: #66AFE9;
   box-shadow: 0 1px 1px rgba(0, 0, 0, 0.075) inset, 0 0 8px rgba(102, 175, 233, 0.6);
   outline: 0 none;

This example can be easily adapted as needed to turn a panel that contains several elements that, as a group, you want to consider a form control into something that looks like one complete entity when interacting with its content.

Monday, October 14, 2013

Entering Recipients from an Address Book using jQueryUI MultiSearch

This might be one of the most common use cases (apart from entering tags) for this type of widget. Compose an email and start typing someone's name. Yahoo, GMail, all of them will offer suggestions from your address book. Select someone and start typing the next one. You can very quickly build a list of recipients using this UI pattern. GMail goes a step further by enhancing the experience - when you hover over an entered contact, you get a popover box with more details on the contact (a picture, Google+ circles, etc). Creating this type of user experience was one of the design goals I had when working on the MultiSearch widget. While the widget won't just provide all of this functionality, it does make it pretty easy to build.


Besides wiring up the source data, there's really only two parts you, as the developer, need to do to make this kind of UI work. The MultiSearch widget will handle the typing, searching, and rendering. You need to provide the markup and styling plus a little validation logic. If you're interested in a popover box, you'll need to define that content and some control logic. Here's a summary of the features we're going to build:

  • Searching - As the user types in the contact, we're probably going to search both the name and email. Returned results need to be rendered into a suggestion box. The content should probably include both fields so the user can see what they're matching and pick accordingly.
  • Selected - When an contact is selected, we need to render something into the list of recipients. This can be styled in several ways and probably should account for a contact not found in the address book. If its not found, we should probably ensure its a valid email.
  • Details - Hovering over the list of entered recipients triggers an event that we can render a popover. So far, the widget has been rendering everything for us. However, the only thing the widget provides here is a item select event with the specifics about the contact under the mouse. We'll need to manage the popover and rendering its content based on the the context provided in that event.

Let's walk through each feature and see how it can be implemented. If you want to skip the details and tinker with demo, its one of the examples on the project pages. A link is included to jump to the full source code. If you'd like a break down of the specific areas of functionality, keep reading.

Sunday, October 6, 2013

Removing Markup from UI Widgets to Build Reusable and Flexible Components

I've been quite impressed with the simplicity and flexibility of the Twitter Bootstrap CSS framework. Its becoming the basis of many of the projects I'm starting. Since it does 90% or more of what I need anyways, why start from scratch? That said, the accompanying widget library is not always as robust as I need and I generally have to step outside of the Bootstrap world to find something that provides the required functionality. However, most of those widgets tend to have their own style definitions that don't always play well with Bootstrap. I started moving away from widget libraries like jQuery UI which come with their own CSS framework and have been turning instead to widgets that offer hooks to define the content and styling that is rendered by the widget. This approach makes a lot of sense if you're accustom to a MVC framework where the logic and presentation are generally decoupled to enable easy restructuring and styling to promote reuse and modularization.