Tutorial: Pagination, ordering, and searching with Searchlogic

September 7th, 2008

Paginating, ordering, searching - no longer a pain in the ass

Maybe it's not a pain in the ass for you, maybe it is, or maybe it is and you don't know it. We'll never know. Either way, this tutorial is pain-in-the-ass free. Guaranteed. That's what this is all about, making your life easier when it comes to paginating, ordering, and searching. Things you do multiple times in almost every application.

My solution: Searchlogic. It has saved me a lot time, shortened my code, and ultimately given me the proper tools to paginate, order, and search my data. Hopefully it will do the same for you.

Enough talk, let's dive in.

"Does this really work? Am I about to waste my time?". I hope not. But just for you, I made this live example:

Live example based on this tutorial

Paginating, ordering, and searching doesn't get any easier than this. Before we start I want to make the following assumptions about you:

  1. You have a working rails application on rails edge (rake rails:freeze:edge)
  2. You have the following model structure: UserGroup => User => Order (=> = has_many). Make sure you have the has_many and belongs_to relationships set up. The fields are irrelevant, just change the field names in the view to whatever you have.

1. Install Searchlogic

$ sudo gem install searchlogic

Now add the gem dependency to your rails config:

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

As always you can install searchlogic as a plugin, but plugins are becoming a thing of the past. I recommend the above approach, but here is the plugin installation if you prefer to do it this way:

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

2. Create your controller

Let's create a controller that searches, orders, and paginates users. Like an admin area. Create app/controllers/users_controller.rb with the following content:

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

What did we do here?

It's simple actually, if you haven't looked at Searchlogic I suggest you take a quick glance. I'm not going to get into crazy detail about what it does, because the README in the library covers that, but to make this short it "enhances" searching with ActiveRecord.

Notice that we started a new search with the @search object. This lets you search via an object, which is really handy for your view, which you will see below. It also added in some nifty methods such as page, per_page, order_by, order_as, and a plethora of conditions for each of your columns. The second line does a simple search and counts the results. The thing to remember here is that the .count method ignores pagination. It ignores the limit and offset values. So if you want to know how many users matched the search, use count, not @users.size. If the first page is limited to 10 reconds @user.size will return 10.

3. Create the view and your're done

# app/views/users/index.html.erb
<% if @users_count > 0 %>
    <%= @users_count %> users found

    <table border="1" cellpadding="5">
        <tr>
            <th><%= order_by_link :id %></th>
            <th><%= order_by_link :user_group => :name %></th>
            <th><%= order_by_link :first_name %></th>
            <th><%= order_by_link :last_name %></th>
            <th><%= order_by_link [:email, :first_name] %></th>
        </tr>
        <% @users.each do |user| %>
            <tr>
                <td><%= user.id %></td>
                <td><%= user.user_group ? user.user_group.name : "-" %></td>
                <td><%= user.first_name %></td>
                <td><%= user.last_name %></td>
                <td><%= user.email %></td>
            </tr>
        <% end %>
    </table>

    <br />
    <br />

    Per page: <%= per_page_select %>

    <% if @search.page_count > 1 %>
        <br />Page: <%= page_select %>
    <% end %>
<% else %>
    No users were returned
<% end %>

What did we do here?

All that we did was iterate through our users, list them in a table format, and then add page and per_page controls. Notice any unusual helpers? (order_by_link, per_page_select, and page_select). I won't go into detail about these helpers because its all in the documentation under Searchlogic::Helpers::ControlTypes.

These are only a few ways to use these helpers, this is really just the tip of the iceberg. The sky is the limit: create a select that lets users navigate through the pages, create a list of links (like flickr) that lets you navigate through pages, create a link that lets you order by any number of columns you want, etc. I know ruby has a bad rep when it comes to documentation, but I actually put some decent time into the Searchlogic documentation and I think you will find it helpful. That's your best resource for finding out everything Searchlogic has to offer.

Adding a search form

"This is all great, but you said I could search my data". Searching your data is just as easy. Just add this to the top of your index.html.erb

# app/views/users/index.html.erb
<% form_for @search do |f| %>
    <fieldset>
        <legend>Search Users</legend>

        <% f.fields_for @search.conditions do |users| %>
            <%= users.label :first_name_keywords %><br />
            <%= users.text_field :first_name_keywords %><br />
            <br />

            <% users.fields_for users.object.orders do |orders| %>
                <%= orders.label :total_gt, "Has orders with a total greater than" %><br />
                $<%= orders.text_field :total_gt %><br />
                <br />
            <% end %>

            <% users.fields_for users.object.user_group do |user_group| %>
                <%= user_group.label :name_starts_with, "Belongs to user group with name that starts with" %><br />
                <%= user_group.text_field :name_starts_with %><br />
                <br />
            <% end %>
        <% end %>
    </fieldset>
    <%= f.submit "Search" %>
<% end %>

As I mentioned above Searchlogic creates default conditions on your columns based on the type. Letting you use a form builder to call those conditions. When it receives these conditions on the back-end it will do its "magic" and creates the proper SQL. Don't worry about SQL injections, Searchlogic has you covered on that (see the documentation for more info).

What's great about this method of searching?

  1. Your search logic is in one place: the view.
  2. You can add conditions by doing f.text_field "[column name]_[condition]". So when your picky client calls up and says "Hey, we need a search field for finding emails that end with..." No prob: f.text_field :email_ends_with. Done!
  3. You can traverse your relationships with fields_for and set conditions on related objects. Saweet!

Ajaxified

So you're saying "this is great, but super old school, where's the AJAX?!?!?" So you're all about AJAX? No problem. In the example I have 3 examples: a non AJAX example, an AJAX example using the built in rails helpers, and a jQuery AJAX example. Check them out. You can view the source of the examples on github (each example if named spaced into its own controller). On a side note, I highly recommend jQuery. I recently started using it and love it. I actually get excited to write javascript because I always find something new when digging through the documentation or plugins and unobtrusive javascript never felt so good.

Some helpful links

This tutorial is really just the tip of the ice berg with Searchlogic. Checkout these links to see all it has to offer:

I am always interested in hearing feedback. Let me know what you think, what you like, what you don't, that you hate the name, etc. I love criticism. Searchlogic is under active development and I am always trying to improve it. I hope this tutorial was helpful to you and ultimately makes your life easier.

Posted by benjohnson Filed in Searchlogic

23 Responses to “Tutorial: Pagination, ordering, and searching with Searchlogic”

  1. September 7th, 2008 at 05:27 PM Zigga Says

    Hey thanks for putting this together. Found your post via RubyFlow.

    I think the “show all” option under the “Per Page:” drop down is not working.

  2. September 7th, 2008 at 06:17 PM Ben Johnson Says

    Thanks for letting me know about that, I really appreciate it.

    I fixed the issue, added some tests in to check for that problem, and released a new version of the gem. All should be good now.

  3. September 10th, 2008 at 11:27 AM D. Rothlisberger Says

    This looks awesome, thanks for making it available.

    How easy would it be to implement “saved searches”, i.e. saving into the database the advanced search criteria entered by the user into the form?

    (I admit I haven’t looked into Searchgasm in any depth, but the “new_search” method in your examples doesn’t seem to exist in the API docs—I assume it instantiates Searchgasm::Search::Base).

  4. September 10th, 2008 at 12:59 PM Ben Johnson Says

    Hello Mr. Rothlisberger,

    Before I released this I had a “personal” version of searchgasm that had a “dump” method, that allowed you to save all of the pertinent details of the search and then load it back up. I took it out because I wanted to do a little more research on Marshalling an object or “serializing” it and see if that would be a better solution. Regardless, all that searchgasm is doing is building the options you pass into ActiveRecord::Base.find(). If you do @search.sanitize you will see all of the options right there. Maybe you can do something with that? If you want, send me an email, or write in this comment about what you are trying to do with a little more details and I’ll come up with a solution and have it added in this week.

    Lastly, I took the ActiveRecord extensions out of the docs. I apologize. I forgot I did that and will add them back in. A lot of what I did for ActiveRecord were alias_chain_methods so I didn’t think the documentation on that would be 100% clear. I’ll work on that today.

    Thanks for the feedback and suggestion.

  5. September 18th, 2008 at 03:19 AM seb Says

    Hi, Nice work.

    Are has_one/belongs_to relationships supported? I don’t get ordering records working with those associations.

    Example: has_one :admin, :class_name => “User” order_by_link(:admin => :login) => click on the order results in a NameError (uninitialized constant Admin):

    Thanks for help

  6. September 19th, 2008 at 12:15 AM Ben Johnson Says

    Sorry for the delayed reponse. The best way to get ahold of me is through email, create a ticket on lighthouse, or send me a message on github. The issues you described have been fixed. Try updating from the repository on github. I also release the fixes as an update through rubygems. Let me know if you still have any other issues. Thanks!

  7. September 23rd, 2008 at 10:52 PM roadburn Says

    love it! thanks for releasing it :)

    Would be even cooler if we could choose which conditions to join with “OR” and “AND”, instead of only being able to join all the conditions with “OR” or “AND”.

    or can that be done already?

  8. September 29th, 2008 at 12:02 AM Ben Johnson Says

    roadburn, honestly that is not easy, because when you start joining conditions with “and” and “or” the order of the conditions matter and you need a way to group conditions. It gets really complicated. If you need to do complicated searching SQL is perfect for that. I never intended for searchgasm to replace SQL, I just wanted it to assist with those mundane search forms.

  9. October 2nd, 2008 at 09:54 PM Ben Johnson Says

    Hey Jeff, I sent you off an email with the resolution to your problem. For any other having the same issue, it works exactly like resources work. When you pass an AR object it decides what url to use, searchgasm assumes since you are searching feeds that you want to call feed_url. You can fix it by form_for @search, :url => your_url.

  10. October 24th, 2008 at 02:22 AM Jacob Says
    Hello, I have this code which is very similar to your UserController:
    class DescriptionsController < ApplicationController def index @search = Description.new_search(params[:search]) end
    But somehow I hit an error and it says new_search is missing. Would be able to tell me what has gone wrong? Thanks. Appreciate your prompt reply. 
    @descriptions, @descriptions_count = @search.all, @search.count
    end
  11. October 24th, 2008 at 05:58 PM Ben Johnson Says

    It looks like your code is correct. I would need more information, like a backtrace, etc to solve this. Also, head over to lighthouse and create a ticket there. I don’t check the comments on here as often. Thanks.

  12. October 27th, 2008 at 04:37 AM mcbigelo Says

    Nice Plugin!

    is there any way to search on all fields include ID fields? i wrote a helper that create for all fields a inputbox and creates a javascript to fill all fields with the same values.. but it is not very nice and dont work with id fields..

    any idea?

    sincerly mcbigelo

  13. October 27th, 2008 at 01:58 PM Alberto Says

    Very nice Plugin. But I have a problem in remote and remote_order_by_link why?

  14. October 27th, 2008 at 08:03 PM Ben Johnson Says

    Thanks for the comments. mcbigelo, have you considered doing that in your controller instead of javascript? Alberta, I’m not sure. I can’t really help out without more info. Create a ticket in lighthouse with more information and I’ll try and help you out.

  15. October 28th, 2008 at 02:49 AM mcbigelo Says

    Hi and thanks for your answere.

    ..the “and” and “or” problem.. i need to search in a table where i want to filter special rows out… for expample i want to search in any fields for “test” but only in rows where status_id = 4… ...where x = ‘test’ or y = ‘test’ or r = ‘test’ and (status_id = 4 and ….) so my question: where i can add my conditions? any idea? or what would be the best solution ?

  16. October 28th, 2008 at 05:32 AM mcbigelo Says

    i rewrote some code of the plugin to add the ability to use and and or conditions at the same time..

    i crated some conditions like andeqauls where i add an And to the conditions ( and= ) in the function merge_conditions then i change the conditions array, that all conditions with and= to append at the end and replace and= with =....

    (i hope you can understand my english ;-) )

    if you interested mail me and i send it to you..

  17. October 28th, 2008 at 11:01 PM Ben Johnson Says

    Yes, I would absolutely love to look at the code. You should consider forking the project on github, making changes to it there, and then sending me a pull request.

  18. October 29th, 2008 at 10:26 PM John Burmeister Says

    Great plugin, thanks for putting this out there!

  19. November 16th, 2008 at 10:24 PM Allan L. Says

    Since it’s so easy to use searchlogic, I’ve stopped using will_paginate in my current project.

    However, I noticed that searching using japanese characters isn’t working (at least against my sqlite3 dev db).

    Is it just my setup (i.e. rails 2.1.2, searchlogic 1.5.4), or just a trade-off for searchlogic being so easy to use in a project?

  20. November 17th, 2008 at 12:04 AM Ben Johnson Says

    Hi Allan,

    It could be a number of things, character sets, etc. Are you trying to search via a keywords condition? Feel free to start a ticket on lighthouse and we can work on it there. Thanks!

  21. December 2nd, 2008 at 08:23 PM frank Says

    Hi,

    I tried this with my own tables but am getting an error message on my index page: NameError in ProductsController#index

    undefined local variable or method `product_nm’ for #<class:0×476f378>

    product_nm is a column in my products table

    Any help would be much appreciated

    Thanks

    Frank

  22. December 3rd, 2008 at 08:16 PM Tim Says

    Thanks for the code… I however, get an error message when trying this: undefined method `new_search’

    Thanks for the help

  23. December 3rd, 2008 at 08:27 PM Ben Johnson Says

    Tim and frank, it is probably something small. Checkout my tests / example and see if you can find out what you are doing different. If you still cant figure it out create a ticket on light house with some more information and I will be happy to help you out.

Leave a Reply