#176 Searchlogic

Back in episode 111, we created an advanced search form. One of the key parts of the code was a number of methods that we used to build conditions dynamically by building up the SQL for the search terms. For example, to search for a product based on its keywords or price we created methods like these:

# models/search.rb

private
def keyword_conditions
  ["products.name LIKE ?", "%#{keywords}%"] unless keywords.blank?
end

def minimum_price_conditions
  ["products.price >= ?", minimum_price] unless minimum_price.blank?
end

def maximum_price_conditions
  ["products.price <= ?", maximum_price] unless maximum_price.blank?
end

def category_conditions
  ["products.category_id = ?", category_id] unless category_id.blank?
end

def conditions
  [conditions_clauses.join(' AND '), *conditions_options]
end

def conditions_clauses
  conditions_parts.map { |condition| condition.first }
end

def conditions_options
  conditions_parts.map { |condition| condition[1..-1] }.flatten
end

def conditions_parts
  private_methods(false).grep(/_conditions$/).map { |m| send(m) }.compact
end

This isn’t the best way to approach this problem as it can quickly get messy and is prone to bugs. So, if concatenating strings together to build up SQL statements isn’t the way to go, what’s the alternative?

One solution is to use Searchlogic. This works by creating a number of named scopes that can be called on any ActiveRecord model to search against that model’s attributes. Given, say, a User model with a username attribute, Searchlogic lets us use methods such as username_equals, username_does_not_equal, username_begins_with and so on. (For a longer list see the documentation on Searchlogic’s Github page.) Using named scopes has several advantages, not least of which is that we can chain them together to create more complex search conditions.

Installing

Searchlogic is available as a gem and is installed in the usual way. First we need to download and install the gem.

sudo gem install searchlogic

Then, inside our application’s /config/environment.rb file we can add it to the list of the app’s gems.

config.gem "searchlogic"

Using Searchlogic

Before we add Searchlogic to our application we’ll demonstrate how it works in the console. To make the console’s output easier to read we’re going to make use of another gem called hirb. Hirb provides several functions for improving the output from the Rails console, not least of which is that it displays lists of models, such as we’d see returned from a find, as tables. Hirb can be installed with

sudo gem install cldwalker-hirb --source http://gems.github.com

We can also make the console show the appropriate lines from the development log so that we can see which SQL statements were called against the database. To get this all working we need to run some code when script/console starts. First,

ActiveRecord::Base.logger = Logger.new(STDOUT)

will enable the output logging to be shown in the console and to enable hirb we need to run these two lines:

require 'hirb'
Hirb::View.enable

The data we’re going to search against is a list of nine Product models. The products we have are these (note that we’ve removed the default timestamp fields from the tables so that the output is narrow enough to fit).

>> Product.all
  Product Load (0.8ms)   SELECT * FROM "products" 
+----+-------------+--------------------+--------+
| id | category_id | name               | price  |
+----+-------------+--------------------+--------+
| 1  | 5           | Scrabble Delux     | 29.99  |
| 2  | 2           | DVD Player         | 79.99  |
| 3  | 1           | Red T-Shirt        | 12.49  |
| 4  | 4           | Black Leather Sofa | 399.99 |
| 5  | 4           | Oak Coffee Table   | 223.99 |
| 6  | 3           | Milk (1 Gallon)    | 2.99   |
| 7  | 3           | Oh's Cereal        | 3.95   |
| 8  | 2           | Video Game Console | 299.95 |
| 9  | 2           | Video Game Disc    | 29.95  |
+----+-------------+--------------------+--------+

Say we want to find all of the products that have “video” in their name. With Searchlogic we can use name_like, which will generate a SQL LIKE query. If we run Product.name_like "video" we’ll see the two products whose name contains “video”.

>> Product.name_like "video"
  Product Load (0.4ms)   SELECT * FROM "products" WHERE (products.name LIKE '%video%') 
+----+-------------+--------------------+--------+
| id | category_id | name               | price  |
+----+-------------+--------------------+--------+
| 8  | 2           | Video Game Console | 299.95 |
| 9  | 2           | Video Game Disc    | 29.95  |
+----+-------------+--------------------+--------+
2 rows in set

Each named scope that Searchlogic provides has a not variant, so if we want to find the products whose name doesn’t contain “video” we can use name_not_like. Running Product.name_not_like "video" will return the products that weren’t returned before.

>> Product.name_not_like "video"
  Product Load (0.8ms)   SELECT * FROM "products" WHERE (products.name NOT LIKE '%video%') 
+----+-------------+--------------------+--------+
| id | category_id | name               | price  |
+----+-------------+--------------------+--------+
| 1  | 5           | Scrabble Delux     | 29.99  |
| 2  | 2           | DVD Player         | 79.99  |
| 3  | 1           | Red T-Shirt        | 12.49  |
| 4  | 4           | Black Leather Sofa | 399.99 |
| 5  | 4           | Oak Coffee Table   | 223.99 |
| 6  | 3           | Milk (1 Gallon)    | 2.99   |
| 7  | 3           | Oh's Cereal        | 3.95   |
+----+-------------+--------------------+--------+
7 rows in set

As we mentioned before we can chain queries together. To return the products that don’t have “video” in their name and cost less than £5 we can run Product.name_not_like("video").price_gt(5).

>> Product.name_not_like("video").price_gt(5)
  Product Load (0.5ms)   SELECT * FROM "products" WHERE ((products.price > 5) AND (products.name NOT LIKE '%video%')) 
+----+-------------+--------------------+--------+
| id | category_id | name               | price  |
+----+-------------+--------------------+--------+
| 1  | 5           | Scrabble Delux     | 29.99  |
| 2  | 2           | DVD Player         | 79.99  |
| 3  | 1           | Red T-Shirt        | 12.49  |
| 4  | 4           | Black Leather Sofa | 399.99 |
| 5  | 4           | Oak Coffee Table   | 223.99 |
+----+-------------+--------------------+--------+
5 rows in set

We could chain another condition to the list to also restrict the items to those costing less than £200 by running Product.name_not_like("video").price_gt(5).price_lt(200)

>> Product.name_not_like("video").price_gt(5).price_lt(200)
  Product Load (0.4ms)   SELECT * FROM "products" WHERE ((products.price < 200) AND ((products.price > 5) AND (products.name NOT LIKE '%video%'))) 
+----+-------------+----------------+-------+
| id | category_id | name           | price |
+----+-------------+----------------+-------+
| 1  | 5           | Scrabble Delux | 29.99 |
| 2  | 2           | DVD Player     | 79.99 |
| 3  | 1           | Red T-Shirt    | 12.49 |
+----+-------------+----------------+-------+
3 rows in set

If we want to search for multiple names we can use name_like_any and pass an number of values. To search for products whose name contains either “sofa” or “table” we can run Product.name_like_any "sofa", "table" and we’ll see the products that have either “sofa” or “table” in their names.

>> Product.name_like_any "sofa", "table"
  Product Load (0.4ms)   SELECT * FROM "products" WHERE (products.name LIKE '%sofa%' OR products.name LIKE '%table%') 
+----+-------------+--------------------+--------+
| id | category_id | name               | price  |
+----+-------------+--------------------+--------+
| 4  | 4           | Black Leather Sofa | 399.99 |
| 5  | 4           | Oak Coffee Table   | 223.99 |
+----+-------------+--------------------+--------+
2 rows in set

We can also perform searches on associated attributes. Our products have a category_id attribute as they belong_to a Category. Our list of categories is

>> Category.all
  Category Load (0.4ms)   SELECT * FROM "categories" 
+----+--------------+
| id | name         |
+----+--------------+
| 1  | Clothing     |
| 2  | Electronics  |
| 3  | Groceries    |
| 4  | Furniture    |
| 5  | Toys & Games |
+----+--------------+
5 rows in set

To list all of the products whose category contains “elect”, we can run Product.category_name_like("elect").

>> Product.category_name_like("elect")
  Product Load (0.4ms)   SELECT "products".* FROM "products" INNER JOIN "categories" ON "categories".id = "products".category_id WHERE (categories.name LIKE '%elect%') 
+----+-------------+--------------------+--------+
| id | category_id | name               | price  |
+----+-------------+--------------------+--------+
| 2  | 2           | DVD Player         | 79.99  |
| 8  | 2           | Video Game Console | 299.95 |
| 9  | 2           | Video Game Disc    | 29.95  |
+----+-------------+--------------------+--------+
3 rows in set

Searchlogic also gives us a method called search that can be called on a model. The arguments you pass to search are the same as the named scopes that are created so to find the products whose category name contains “elect” with a price of below 100 pounds we can use Product.search(:category_name_like => "elect", :price_lt => "100").

>> Product.search(:category_name_like => "elect", :price_lt => "100")
=> #<Searchlogic::Search:0x2311e40 @klass=Product(id: integer, category_id: integer, name: string, price: float), @conditions={:category_name_like=>"elect", :price_lt=>100.0}, @current_scope=nil>

This time we have a Search object returned. To work with the object we’ll have to assign it to a variable, which we can do with this irb trick.

>> s = _

If you assign a variable to the underscore character it will be assigned the value of the object that was returned in the last command. With the search object assigned to s we can call the all method to return a list of the matching products.

>> s.all
  Product Load (0.4ms)   SELECT "products".* FROM "products" INNER JOIN "categories" ON "categories".id = "products".category_id WHERE ((products.price < 100.0) AND (categories.name LIKE '%elect%')) 
+----+-------------+-----------------+-------+
| id | category_id | name            | price |
+----+-------------+-----------------+-------+
| 2  | 2           | DVD Player      | 79.99 |
| 9  | 2           | Video Game Disc | 29.95 |
+----+-------------+-----------------+-------+
2 rows in set

We can call named scopes on the search object too. So to just match the products in the search object that have a name containing “video” we can call s.name_like("video") to get another search object on which we can call all.

>> s.name_like("video").all
  Product Load (0.3ms)   SELECT "products".* FROM "products" INNER JOIN "categories" ON "categories".id = "products".category_id WHERE ((products.price < 100.0) AND ((products.name LIKE '%video%') AND (categories.name LIKE '%elect%'))) 
+----+-------------+-----------------+-------+
| id | category_id | name            | price |
+----+-------------+-----------------+-------+
| 9  | 2           | Video Game Disc | 29.95 |
+----+-------------+-----------------+-------+
1 row in set

If you’ve created our own named scopes in a model you can also use them here with a search object.

We can also pass in sorting options as well as conditions, by using ascend_by_ and descend_by_. So, Product.ascend_by_name will return the products in alphabetical order.

>> Product.ascend_by_name
+----+-------------+--------------------+--------+
| id | category_id | name               | price  |
+----+-------------+--------------------+--------+
| 4  | 4           | Black Leather Sofa | 399.99 |
| 2  | 2           | DVD Player         | 79.99  |
| 6  | 3           | Milk (1 Gallon)    | 2.99   |
| 5  | 4           | Oak Coffee Table   | 223.99 |
| 7  | 3           | Oh's Cereal        | 3.95   |
| 3  | 1           | Red T-Shirt        | 12.49  |
| 1  | 5           | Scrabble Delux     | 29.99  |
| 8  | 2           | Video Game Console | 299.95 |
| 9  | 2           | Video Game Disc    | 29.95  |
+----+-------------+--------------------+--------+
9 rows in set

Integrating Search Into Our Application

That’s enough of the console. We’ll now integrate Searchlogic into an application. Below is a page showing the same list of products we’ve been searching against. There’s also a search field and button on the page, but the form isn’t wired up yet. Entering a search term and clicking the button just reloads the page with the search term in the querystring.

Getting the search working is straightforward. In the controller we’ll split the search term into words (after converting it to a string in case it’s nil) then use name_like_all to return the products whose name contains all of the search words. Lastly we’ll add ascend_by_name to return the products in alphabetical order.

def index
  @products = Product.name_like_all(params[:search].to_s.split).ascend_by_name
end

If we search for “video console” now we’ll see the one product that has a name that matches both “video” and “console” returned.

Now that we’ve got the search working we can add some more functionality to our form, allowing searching by category and a price range.

The search form in the index view currently looks like this:

<% form_tag products_path, :method => 'GET' do %>
  <%= text_field_tag :search, params[:search] %>
  <%= submit_tag "Search", :name => nil %>
<% end %>

This code is based on the simple search form from episode 37. For our more complex search form we’re going to use form_for instead of form_tag. We usually form_for for manipulating resources, but in this case we can think of a search as a resource. If we do so then we can write our form this way:

<% form_for @search do |f| %>
  <ol class="formList">
    <li>
      <%= f.label :name_like, "Name" %>
      <%= f.text_field :name_like %>
    </li>
    <li>
      <%= f.label :category_id_equals, "Category" %>
      <%= f.collection_select :category_id_equals, Category.all, :id, :name, :include_blank => true %>
    </li>
    <li>
      <%= f.label :price_gte, "Price Range" %>
      <%= f.text_field :price_gte, :size => 8 %> - <%= f.text_field :price_lte, :size => 8 %>
    </li>
    <li>
      <%= f.submit "Search" %>
    </li>
  </ol>
<% end %>

Our form is now for a @search instance variable which we’ll create in the controller shortly. We’ll give it fields called name_like, category_id_equals, price_gte and price_lte. These names should look familiar as some of the named scopes we’ve used when searching against our product model. We’re assigning values to them which we can then use in the controller to perform our search.

def index
  @search = Product.search(params[:search])
  @products = @search.all
end

We can use the params from our search form to pass to Product.search to return a search object. What we’re doing is passing a hash of options to the search method in the same way we did earlier when we ran

Product.search(:category_name_like => "elect", :price_lt => "100")

We then get our list of matching products by calling @search.all.

Let’s see if our new form works by trying a search for the products in the electronics category that are less than £50.

It does! We now have a form that lets us search against a number of fields.

Sorting in Views

We’ll finish off by showing one more of Searchlogic’s features: sorting. Searchlogic provides a helper method called order that can be added to our view and which provides links that order the search results. If we add

<p>Sort by <%= order @search, :by => :name %> | <%= order @search, :by => :price %></p>

to the view we’ll have two links on the page that will allow us to sort our search results by name or price either ascending or descending.

Searchlogic provides an impressive range of features to make searching easy in your Rails applications, especially with its clever use of named scopes. Despite that there are a couple of reasons that might make using it in views only suitable for pages that are in admin sections or otherwise protected from public use. The first is aesthetic: the URLs Searchlogic generates can become quite long fairly easily, as all of the search parameters are included in the querystring. Secondly, as the parameters are passed in the querystring users aren’t restricted to the fields in the form, but could enter any named scope which are available in your model. If you want to take this approach on your public-facing forms then filtering the params hash to ensure that only the named scopes that are on the form are being passed is necessary.

Performance

It might seem that adding a large number of named scopes to all of an application’s models might hurt its performance, but Searchlogic makes use of method_missing to only create named scopes are they are called for the first time. This means that the named scopes are created as efficiently as possible. That said, if you’ll be doing a lot of text searching in your application it might be worth looking at a full-text search engine such as Thinking Sphinx.