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.
Helpful links
* 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
-
Bugs / feature suggestions: binarylogic.lighthouseapp.com/projects/16601-searchgasm
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
: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}
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:
-
Passing a search object right into form_for and fields_for
-
The built in conditions for each column and how you can traverse the relationships and set conditions on them
-
The order_by_link helper
-
The page_select and per_page_select helpers
-
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 = 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