Searchgasm

Searchgasm is orgasmic. Maybe not orgasmic, but you will get aroused. So go grab a towel and let’s dive in.

Searchgasm’s inspiration comes right from ActiveRecord. ActiveRecord lets you create objects that represent a record in the database, so why can’t you create objects that represent searching the database? Now you can! It’s searching, ordering, and pagination all in one.

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

Install and use

sudo gem install searchgasm

For rails

$ cd vendor/plugins
$ sudo gem unpack searchgasm

Or as a plugin

script/plugin install git://github.com/binarylogic/searchgasm.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

User.all(
  :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
    :created_at_hour_gt => 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.created_at_hour_gt = 5
search.per_page = 20
search.page = 3
search.order_as = "ASC"
search.order_by = {:user_group => :name}
search.all

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 searchgasm, integration into rails

Using Searchgasm 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. Searchgasm 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 Searchgasm 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
end

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.calendar_date_select :created_after # nice rails plugin for replacing date_select
    - users.fields_for users.object.orders do |orders|
      = orders.select :total_gt, (1..100)
  = f.submit "Search"

- if @users_count > 0
  %table
    %tr
      %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|
      %tr
        %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: http://www.binarylogic.com/2008/9/7/tutorial-pagination-ordering-and-searching-with-searchgasm

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
@search.all
@search.first

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

Calculations

Using the object from above:

@search.average('id')
@search.count # ignores limit and offset
@search.maximum('id')
@search.minimum('id')
@search.sum('id')
@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.all

Match ANY or ALL of the conditions

As you saw above, the nice thing about Searchgasm 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

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})

search = @current_user.orders.build_search(:conditions => {:total_lte => 500})

Scope support

Not only can you use searchgasm 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
end

or

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

Always use protection…against SQL injections

If there is one thing we all know, it’s to always use protection against SQL injections. That’s why searchgasm 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.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

Modifiers

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. Searchgasm to the rescue! Searchgasm 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 searchgasm.

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
:ceil             :round_up                                   Rounds up to the nearest int
:cos              :cosine                                     The cosine
:cot              :cotangent                                  The cotangent
: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
: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:

www.binarylogic.com/2008/10/5/tutorial-caching-virtual-attributes-in-the-database

Roll your own conditions & modifiers

For more information on this please see Searchgasm::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 Searchgasm

What that rule means is that any options you pass when searching get “sanitized” down into options ActiveRecord can understand. Searchgasm 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, Searchgasm 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 Searchgasm::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.

Credits

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