Object based ActiveRecord searching, paginating, ordering, and more!
Pull request Compare This branch is 429 commits behind binarylogic:master.
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Failed to load latest commit information.



Searchlogic's goal is to keep your application free of searching clutter. It's is an ActiveRecord extension that allows you to perform simple and complex searches with a hash. Why is this great? Because GET and POST parameters are a hash. This means if you can execute a search with a hash, you can execute a search using GET or POST parameters. This makes searching in your application dead simple and clutter free:

@search = User.new_search(params[:search])
@users = @search.all

Now think about it this way. How are resources / web services used? Through GET and POST. All of a sudden you have a resource that comes preloaded with a very flexible searching functionality, all with 2 lines of code.

What about your interface? What is an HTML form's sole purpose? It's to send GET or POST parameters to a URI. Now you can build a form that represents a search. Meaning adding a condition to your search is as easy as adding a field to your form. That's what the @search object above is all about. You can pass that right into your form builder and build a form just like you would for an ActiveRecord object. Now you can use that handy form builder that you like you use and get rid of even more searching clutter in your views.

Searching in your application has never been cleaner, more flexible, or easier.

These are just the basics of Searchlogic, it can do a lot more, including pagination, ordering data, etc. All of these things are just as simple, if not simpler. Keep reading to find out everything it can do.

Helpful links

* Documentation: searchlogic.rubyforge.org * Easy pagination, ordering, and searching tutorial: www.binarylogic.com/2008/9/7/tutorial-pagination-ordering-and-searching-with-searchlogic * Live example of the tutorial above (with source): searchlogic_example.binarylogic.com

Install and use

sudo gem install searchlogic

For rails, as a gem (recommended)

# config/environment.rb
config.gem "searchlogic"

Or as a plugin (for older versions of rails)

script/plugin install git://github.com/binarylogic/searchlogic.git

Now try out some of the examples below:

For all examples, let's assume the following relationships: User => Orders => Line Items

Simple Searching Example

  :conditions => {
    :first_name_contains => "Ben",          # first_name like '%Ben%'
    :email_ends_with => "binarylogic.com",  # email like '%binarylogic.com'
    :created_after => Time.now,             # created_at > Time.now
    :hour_of_created_at => 5                # HOUR(created_at) > 5 (depends on DB type)
  :per_page => 20,                        # limit 20
  :page => 3,                             # offset 40, which starts us on page 3
  :order_as => "ASC",
  :order_by => {:user_group => :name}     # order user_groups.name ASC

same as above, but object based

search = User.new_search
search.conditions.first_name_contains = "Ben"
search.conditions.email_ends_with = "binarylogic.com"
search.conditions.created_after = Time.now
search.conditiona.hour_of_created_at = 5
search.per_page = 20
search.page = 3
search.order_as = "ASC"
search.order_by = {:user_group => :name}

In both examples, instead of using the “all” method you could use any search method: first, find(:all), find(:first), count, sum, average, etc, just like ActiveRecord.

The beauty of Searchlogic, integration into rails

Using Searchlogic in rails is the best part, because rails has all kinds of nifty methods to make dealing with ActiveRecord objects quick and easy, especially with forms. So let's take advantage of them! That's the idea behind this plugin. Searchlogic is searching, ordering, and pagination all rolled into one simple plugin. Take all of that pagination and searching cruft out of your models and controllers, and let Searchlogic handle it. Check it out:

# app/controllers/users_controller.rb
def index
  @search = User.new_search(params[:search])
  @users, @users_count = @search.all, @search.count

Now your view:

# app/views/users/index.html.haml
- form_for @search do |f|
  - f.fields_for @search.conditions do |users|
    = users.text_field :first_name_contains
    = users.date_select :created_after
    - users.fields_for users.object.orders do |orders|
      = orders.select :total_gt, (1..100)
  = f.submit "Search"

- unless @users_count.zero?
      %th= order_by_link :account => :name
      %th= order_by_link :first_name
      %th= order_by_link :last_name
      %th= order_by_link :email
    - @users.each do |user|
        %td= user.account? ? user.account.name : "-"
        %td= user.first_name
        %td= user.last_name
        %td= user.email

  == Per page: #{per_page_select}
  == Page: #{page_select}
- else
  No users were found.

Things to note in this view:

  1. Passing a search object right into form_for and fields_for

  2. The built in conditions for each column and how you can traverse the relationships and set conditions on them

  3. The order_by_link helper

  4. The page_select and per_page_select helpers

  5. All of your search logic is in 1 spot: your view. Nice and DRY.

See my tutorial on this example: www.binarylogic.com/2008/9/7/tutorial-pagination-ordering-and-searching-with-searchlogic

Exhaustive Example w/ Object Based Searching (great for form_for or fields_for)

# Start a new search
@search = User.new_search(
  :conditions => {
    :first_name_contains => "Ben", 
    :age_gt => 18,
    :orders => {:total_lt => 100}
  :per_page => 20,
  :page => 2,
  :order_by => {:orders => :total},
  :order_as => "DESC"

# Set local conditions
@search.conditions.email_ends_with = "binarylogic.com"

# Set conditions on relationships
@search.conditions.oders.line_items.created_after = Time.now  # can traverse through all relationships

# Set options
@search.per_page = 50                     # overrides the 20 set above
@search.order_by = [:first_name, {:user_group => :name}]  # order by first name and then by the user group's name it belongs to
@search.order_as = "ASC"

# Set ANY of the ActiveRecord options
@search.group = "last_name"
@search.readonly = true
# ... see ActiveRecord documentation

# Return results just like ActiveRecord

Take the @search object and pass it right into form_for or fields_for (see above).


Using the object from above:

@search.count # ignores limit and offset
@search.calculate(:sum, 'id')
# ...any of the above calculations, see ActiveRecord documentation on calculations

Or do it from your model:

User.count(:conditions => {:first_name_contains => "Ben"})
User.sum('id', :conditions => {:first_name_contains => "Ben"})
# ... all other calcualtions, etc.

Different ways to search, take your pick

Any of the options used in the above example can be used in these, but for the sake of brevity I am only using a few:

User.all(:conditions => {:age_gt => 18}, :per_page => 20)

User.first(:conditions => {:age_gt => 18}, :per_page => 20)

User.find(:all, :conditions => {:age_gt => 18}, :per_page => 20)

User.find(:first, :conditions => {:age_gt => 18}, :per_page => 20)

search = User.new_search(:conditions => {:age_gt => 18})  # build_search is an alias
search.conditions.first_name_contains = "Ben"
search.per_page = 20

search = User.new_search(:conditions => {:age_gt => 18}) do |s|
  s.conditions.first_name_contains = "Ben"
  s.per_page = 20

Match ANY or ALL of the conditions

As you saw above, the nice thing about Searchlogic is it's integration with forms. I designed the “any” option so that forms can set this as well, just like a condition.

@search = User.new_search(:conditions => {:age_gt => 18})
@search.conditions.first_name_contains = "Ben"
@search.conditions.any = true # can set this to "true" or "1" or "yes"
@search.all # will join all conditions with "or" instead of "and"
# ... all operations above are available

What if you want to mix and match?

@search = User.new_search(:conditions => {:age_gt => 18})
@search.conditions.or_first_name_contains = "Ben"
@search.conditions.or_last_name_contains = "Johnson"
@search.conditions.and_id_gt = 5 # the and_ is optional, calling just id_gt is the same thing
@search.all # will join conditions in the orders they were set with their specified join condition
# => age > 18 OR first_name like '%Ben%' OR lsat_name like '%Johnson%' AND id > 5

The order the conditions is set is relevant, as the SQL will be built in the same order.

Grouping conditions

In more complex searching situations you might want to group conditions. Just like you use parenthesis in raw SQL. Searchlogic's “group” function is basically a way to implement parenthesis in your conditions. It's simple:

Group off an object:

search = User.new_search
search.id_gt = 2
group1 = search.conditions.group
group1.first_name_like = "Ben"
group2 = search.conditions.group
group2.last_name_like = "Johnson"
group21 = group2.group
group21.email_ends_with = "binarylogic.com"
# => id > 2 AND (first_name like '%Ben%') AND (last_name like '%Johnson%' AND (email like '%binarylogic.com'))

Group with a block:

search = User.new_search
search.id_gt = 2
search.conditions.group do |group|
  group.first_name_like = "Ben"
search.conditions.or_group do |group|
  group.last_name_like = "Johnson"
  group.group do |sub_group|
    sub_group.email_ends_with = "binarylogic.com"
# => id > 2 AND (first_name like '%Ben%') OR (last_name like '%Johnson%' AND (email like '%binarylogic.com'))

Group with a hash:

search = User.new_search(:conditions => [
  {:id_gt => 2},
  {:group => {:first_name_like => "Ben"}},
  {:group => [
    {:last_name_like => "Johnson"},
    {:group => {:email_ends_with => "binarylogic.com"}}
# => id > 2 AND (first_name like '%Ben%') AND (last_name like '%Johnson%' AND (email like '%binarylogic.com'))

I want to end this by saying Searchlogic was never meant to replace SQL, name_scopes, etc. If you need to perform complex searching there is nothing wrong with resorting to a named scope or using the traditional search methods. In fact, search logic plays nice with named_scopes anyways, so you can combine the 2 if needed:

@search = User.my_awesome_scope.another_cool_scope.new_search

You decide when a named scope makes the most sense. If you are creating a named scope that is specific to the search form you are building, you should consider adding the conditions right into the form. But if you are creating a named scope that you are using throughout your application, then it probably makes sense for it to be a named scope.

Scoped searching

@current_user.orders.find(:all, :conditions => {:total_lte => 500})
@current_user.orders.count(:conditions => {:total_lte => 500})
@current_user.orders.sum('total', :conditions => {:total_lte => 500})
@current_user.orders.build_search(:conditions => {:total_lte => 500})

Scope support

Not only can you use searchlogic when searching, but you can use it when using scopes.

class User < ActiveRecord::Base
  named_scope :sexy, :conditions => {:first_name => "Ben", email_ends_with => "binarylogic.com"}, :per_page => 20


class User < ActiveRecord::Base
  def self.find_sexy
    with_scope(:find => {:conditions => {:first_name => "Ben", email_ends_with => "binarylogic.com"}, :per_page => 20}) do

Protection against SQL injections

SQL injections are not fun, so let's make sure they don't happen. That's why searchlogic protects you by default. The new_search methods protect mass assignments by default (instantiation and search.options = {}). This means that various checks are done to ensure it is not possible to perform any type of SQL injection during mass assignments. But this also limits how you can search, meaning you can't write raw SQL. If you want to be daring and search without protection, all that you have to do is add ! to the end of the method: new_search!.

Protected from SQL injections

search = Account.new_search(params[:search])

NOT protected from SQL injections

accounts = Account.find(params[:search])
accounts = Account.all(params[:search])
account = Account.first(params[:search])
search = Account.new_search!(params[:search])

I'm sure you already knew this, but it's tempting to do this when you can pass the params hash right into these methods.

Lesson learned: use new_search when passing in params as ANY of the options.

Available Conditions

The conditions are pretty self explanitory, but if you need more information checkout the docs or the source. The code is very simple and self explanatory.

Column conditions

Each column can be used with any of the following conditions

Name                          Aliases                             Description
:begins_with                  :starts_with, :sw, :bw, :start      col LIKE 'value%'
:ends_with                    :ew, :ends, :end                    col LIKE '%value'
:equals                       :is, ""                             Lets ActiveRecord handle this
:greater_than                 :gt, :after                         col > value
:greater_than_or_equal_to     :at_least, :least, :gte             col >= value
:ilike                        :icontains, :ihas                   PostgreSQL specific, makes LIKE case insensitive
:less_than                    :lt, :before                        col < value
:less_than_or_equal_to        :at_most, :most, :lte               col <= value
:keywords                     :kwords, :kw                        Splits into each word and omits meaningless words, a true keyword search
:like                         :contains, :has                     col LIKE '%value%'

:not_begin_with               :not_bw, :not_sw, :not_start_with, :not_start, :beginning_is_not, :beginning_not
:not_end_with                 :not_ew, :not_end, :end_is_not, :end_not
:not_equal                    :does_not_equal, :is_not, :not
:not_have_keywords            :not_have_keywords, :not_keywords, :not_have_kw, :not_kw, :not_have_kwwords, :not_kwwords
:not_ilike                    :not_icontain, :not_ihave
:not_like                     :not_contain, :not_have

Class level conditions

Each model comes preloaded with class level conditions as well. The difference is that these are not applied to each column, but instead to the model as a whole. Example: search.conditions.child_of = 2

Name                          Description
:child_of                     Returns all children of value
:descendant_of                Returns all descendants (children, grandchildren, grandgrandchildren, etc)
:inclusive_descendant_of      Same as above but also includes the root
:sibling_of                   Returns all records that have the same parent

The above conditions are for a nested set data structure, not a tree. Since we need to traverse through the tree a nested set is required for performance reasons. Traversing through a large tree is very bad for performance, a nested set solves this problem.


What are modifiers?

ActiveRecord does a great job when it comes to keeping your code database agnostic. But I feel like it neglected searching when it came to that goal. What if you want to find all records that were created after 7am? Depending on your database you would have to do something like the following:

MySQL:        HOUR(created_at)
PostgreSQL:   date_part('hour', created_at)
SQLite:       strftime('%H', created_at)

All of a sudden your app is not database agnostic. Searchlogic to the rescue! Searchlogic creates what I like to call “modifiers” to handle this nonsense for you. A modifier modifies a column. For example, the hour modifier modifies a datetime column to return the hour.

The last thing to keep in mind is that not all modifiers are available for every database. MySQL and PostgreSQL support all of these, but SQLite does not. SQLite is nice, in the sense that its really is “lite”. The only modifiers it supports are the datetime modifiers. If you want support for the other modifiers you have to write the SQLite function yourself and register the modifier in searchlogic.

Here are all of the available modifiers:

Available modifiers

Name              Aliases                                     Description
:microsecond      :microseconds, :microsecs, :microsec        Extracts the microseconds
:millisecond      :milliseconds, :millisecs, :millisec        Extracts the milliseconds
:second           :sec                                        Extracts the seconds
:minute           :min                                        Extracts the minute
:hour                                                         Extracts the hour
:day_of_week      :dow                                        Extracts the day of week (1-7)
:day_of_month     :dom                                        Extracts the day of month (1-31)
:day_of_year      :doy                                        Extracts the day of year (1-366)
:week                                                         Extracts the week (1-53), 53rd week can be a "run-over" week to the next year
:month            :mon                                        Extracts the month (1-12)
:year                                                         Extracts the year

:md5                                                          Converts to a MD5
:char_length      :length                                     The length of the string (integer)
:lower            :downcase, :lcase                           Converts the string to all lower case characters
:ltrin            :lstrip                                     Strips off spaces from the beginning of the string
:trim             :strip                                      Strips off spaces from the beginning and end of the string
:rtrim            :rstrip                                     Strips off spaces from the end of the string
:upper            :upcase, :ucase                             Converts the string to all upper case character

:absolute         :abs                                        The absolute value (-1 => 1)
:acos                                                         The arc cosine
:asin                                                         The arc sine
:atan                                                         The arc tangent
:avg              :average                                    The average
:ceil             :round_up                                   Rounds up to the nearest int
:cos              :cosine                                     The cosine
:cot              :cotangent                                  The cotangent
:count                                                        The count
:degrees                                                      Converts radians to degrees
:exp              :exponential                                Returns the value of e (the base of natural logarithms) raised to the power of X
:floor            :round_down                                 Rounds down to the nearest int
:hex                                                          Converts the number to a hex
:log              :ln                                         The natural logarithm
:log10                                                        Returns the base-10 logarithm
:log2                                                         Returns the base-2 logarithm
:octal            :oct                                        Return an octal representation of a decimal number
:radians                                                      Converts to radians
:round                                                        Rounds the number
:sign                                                         The sign of the number
:sin                                                          The sine of the number
:square_root      :sqrt, :sq_rt                               The square root of the number
:sum                                                          The sum of the number
:tan              :tangent                                    The tangent of the number

How to use modifiers

Here's what the above table means. Let's take the created_at column. The created_at column is a datetime column, laet's apply modifiers that make sense for a datetime column.

search.conditions.second_of_created_at = 2
search.conditions.sec_of_created_at_greater_than = 3
search.conditions.year_of_created_at_most = 5
search.conditions.year_of_created_at_lt = 2000
# ... any of the modifiers that apply to datetime columns

Here's the cool part. Chaining modifiers:

search.conditions.ceil_of_cos_of_sec_of_created_at_greater_than = 3

As long as the modifier chain makes sense the possibilities are endless.

Modifiers are not indexed

Depending on your database you can create complex indexes. But chances are you probably didn't or don't plan to. So keep in mind that once you use a modifier it will not be using an index, meaning the query will be slower. One of the ways to get the best of both worlds is to cache virtual attributes in the database. Checkout my tutorial:


Roll your own conditions & modifiers

For more information on this please see Searchlogic::Conditions::Base

Under the hood

I'm a big fan of understanding what I'm using, so here's a quick explanation: The design behind this plugin is pretty simple and I had 1 main rule when developing this:

ActiveRecord should never know about Searchlogic

What that rule means is that any options you pass when searching get “sanitized” down into options ActiveRecord can understand. Searchlogic serves as a transparent filter between you and ActiveRecord. It doesn't dig into the ActiveRecord internals, it only uses what is publicly available. It jumps in and helps out only when needed, otherwise it sits back and stays completely out of the way.

Lastly, Searchlogic is lazy. It only creates objects, methods, and classes when needed. Once it creates them it caches them. For example, all of the nifty conditions are created via meta programming. The first time you execute something like User.new_search all of that method creation gets cached into Searchlogic::Cache::UserSearch. The next time you execute User.new_search it will be over 50 times faster because it uses the cached class.

Between that and the extensive tests, this is a solid and fast plugin.


Author: Ben Johnson of Binary Logic

Credit to Zack Ham for helping with feature suggestions.

Copyright © 2008 Ben Johnson of Binary Logic, released under the MIT license