JasonDaly.name

Dynamically Building Squeel DSL Blocks

Given a partial query such as Jas D I recently needed to search for matches in a users table across multiple fields:

• Email (:email)
• Username (:username)
• First Name (:first_name)
• Last Name (:last_name)

I also wanted this same search to be reusable; flexible enough to let me specify other columns within the User model to search by, such as just the first_name and last_name fields.

The requirements were simple:

• Every ‘chunk’ (jas, d) must be found within 1+ fields for a match to be successful
• Stop words and undesireable characters should be parsed out ahead of time

Squeel is an awesome gem by Ernie that provides a nice DSL for ActiveRecord. The closest example in the docs to meet my requirements is through the user of the like_any predicate method.

User.where{(first_name.like_any words) | (last_name.like_any words) | (email.like_any words) | (username.like_any words)}

There are two issues with this:

1. This doesn’t generate the SQL I want; the above DSL block will match if any word matches any field.
2. This isn’t dynamic; I can’t specify the columns to search dynamically through the DSL.

I had initially hoped there was some combination of parentheses, |’s and &’s I could use to generate a query that met my requirements, but quickly realized I needed to work with the internals of Squeel directly (I could not see a way to specify colum names dynamcially through the provided DSL).

Working with Squeel Internals

The method must be a class method

def self.live_search(words, columns)
  # …
end

To meet my requirements, the method must

1. Loop over each word
   1. Loop over each column
   2. Build a Squeel Node attempting to match the word against each column
2. Join each expression with a boolean OR (|) from the DSL
3. Join each group of OR‘d expressions with a boolean AND (&)
4. Pass the constructed Squeel DSL block to where{}
5. Return

Or in code

def self.live_search(words, columns)
  words.map do |word|
    columns.map do |column|
      Squeel::Nodes::Predicate.new(Squeel::Nodes::Stub.new(column), :matches, word)
    end.inject do |t, expr|
      t | expr
    end
  end.inject do |t, expr|
    t & expr
  end.tap do |block|
    return where{block}
  end
end

Note: The use of the loops, |, & and :matches predicate method are specific to my requirements. This should provide enough of a guide though to build a dynamic Squeel DSL block yourself based on your own requirements.

The above method in the User model can be used from a controller as follows

query       = params[:query].downcase
stop_words  = ['mr', 'mrs', 'ms', 'miss']
# Allows apostrophe for last names like O'Donnel
query_words = (query.downcase.split(/[^\w\']+/) - stop_words).map{ |w| "%#{w}%" }

results = User.active.live_search(query_words, [:first_name, :last_name, :email, :username])

and in other instances of the live-search where :email or :username are irrelevant

results = User.active.live_search(query_words, [:first_name, :last_name])