We wanted to add searching and sorting to our paginated tables and came across lots of railscasts on how to do it:
This post will cover getting ransack (search & sorting) and kaminari(pagination) working in my rails 4 app. I also added a “clear search” feature.
Before we go any further I’ll show you what the table will look like at the end
Technology Stack
ruby '2.0.0'
gem 'rails', '4.0.1'
# css
gem 'bootstrap-sass', '2.3.1.0'
gem 'bootswatch-rails', '0.5.0'
gem 'kaminari-bootstrap', '~> 0.1.3'
# table search and sort
gem "ransack", github: "activerecord-hackery/ransack", branch: "rails-4"
# table pagination
gem 'kaminari'
Ransack
As stated earlier Ransack is sick (ie a slick timesaver, not cough cough sick)! it allows you to easily add searching and sorting. Other gems/frameworks allow you to do this but ransack sets itself apart by seamlessly handling associations.
Controller
before_action :clear_search_index, :only => [:index]
def index
@search = Component.search(search_params)
# make name the default sort column
@search.sorts = 'name' if @search.sorts.empty?
@component = @search.result().page(params[:page])
end
Application Controller
For usability, I added a clear search button next to the search button. To clear the search we’re setting the values of the hash in params[:q] to nil & deleting the :search_cancel parameter I added, pretty simple.
def search_params
params[:q]
end
def clear_search_index
if params[:search_cancel]
params.delete(:search_cancel)
if(!search_params.nil?)
search_params.each do |key, param|
search_params[key] = nil
end
end
end
end
index.html.erb
<% provide(:title, Component.model_name.human.pluralize) %>
<h1><%= Component.model_name.human.pluralize %></h1>
<div class="row">
<%= link_to t("basic.create.type", type: Component.model_name.human ), new_component_path, class: "btn btn-success pull-right" %>
</div>
<div id="components"><%= render 'components' %></div>
index.js.erb
$('#components').html('<%= escape_javascript(render("components")) %>');
_components.html.erb
<%= search_form_for @search, :remote=>"true", url: components_path, :method => :get do |f| %>
<table class="table table-striped table-bordered table-condensed table-hover">
<thead>
<tr>
<th class="component_name_header_col">
<%= sort_link @search, :name, get_label("component", "name"), {}, { :remote => true, :method => :get } %>
</th>
<th class="component_component_definition_name_header_col">
<%= sort_link @search, :component_definition_name, get_label("component", "component_definition"), {}, { :remote => true, :method => :get } %>
</th>
<th class="component_manufacturer_name_header_col">
<%= sort_link @search, :manufacturer_name, get_label("component", "manufacturer"), {}, { :remote => true, :method => :get } %>
</th>
<th class="component_source_name_header_col">
<%= sort_link @search, :source_name, get_label("component", "source"), {}, { :remote => true, :method => :get } %>
</th>
<th class="component_level_type_name_header_col">
<%= sort_link @search, :level_type_name, get_label("component", "level_type"), {}, { :remote => true, :method => :get } %>
</th>
<th class="action_col"><%= t("basic.action") %></th>
</tr>
<tr>
<th><%= f.text_field :name_cont %></th>
<th><%= f.text_field :component_definition_name_cont %></th>
<th><%= f.text_field :manufacturer_name_cont %></th>
<th><%= f.text_field :source_name_cont %></th>
<th><%= f.text_field :level_type_name_cont %></th>
<th><%= render partial: 'shared/search_button_group'%></th>
</tr>
</thead>
<tbody>
<% @component.each do |component| %> <% end %>
<table class="table table-striped table-bordered table-condensed table-hover">
<thead>
<tr>
<th class="component_name_header_col"><%= sort_link @search, :name, get_label("component", "name"), {}, { :remote => true, :method => :get } %></th>
<th class="component_component_definition_name_header_col"><%= sort_link @search, :component_definition_name, get_label("component", "component_definition"), {}, { :remote => true, :method => :get } %></th>
<th class="component_manufacturer_name_header_col"><%= sort_link @search, :manufacturer_name, get_label("component", "manufacturer"), {}, { :remote => true, :method => :get } %></th>
<th class="component_source_name_header_col"><%= sort_link @search, :source_name, get_label("component", "source"), {}, { :remote => true, :method => :get } %></th>
<th class="component_level_type_name_header_col"><%= sort_link @search, :level_type_name, get_label("component", "level_type"), {}, { :remote => true, :method => :get } %></th>
<th class="action_col"><%= t("basic.action") %></th>
</tr>
<tr>
<th><%= f.text_field :name_cont %></th>
<th><%= f.text_field :component_definition_name_cont %></th>
<th><%= f.text_field :manufacturer_name_cont %></th>
<th><%= f.text_field :source_name_cont %></th>
<th><%= f.text_field :level_type_name_cont %></th>
<th><%= render partial: 'shared/search_button_group'%></th>
</tr>
</thead>
<tbody>
<tr>
<td class="component_name_body_col"><%= component.try(:name) %></td>
<td class="component_component_definition_body_col"><%= component.component_definition.try(:name) %></td>
<td class="component_manufacturer_body_col"><%= component.manufacturer.try(:name) %></td>
<td class="component_source_body_col"><%= component.source.try(:name) %></td>
<td class="component_level_type_body_col"><%= component.level_type.try(:name) %></td>
<td>
<div class="btn-group"><a class="btn dropdown-toggle btn-primary action-btn" href="#" data-toggle="dropdown">
<%= t('basic.action') %>
</a>
<ul class="dropdown-menu">
<li><%= link_to t('basic.show.simple'), component %></li>
<li><%= link_to t('basic.edit.simple'), edit_component_path(component) %></li>
<li><%= link_to t('basic.destroy.simple'), component, method: :delete, data: { confirm: 'Are you sure?' } %></li>
</ul>
</div>
<!-- button group --></td>
</tr>
</tbody>
</table>
<%= paginate @component %>
<% end %>
Kaminari
A note on using kaminari and will_paginate. I’m not sure this is possible. I was originally using will_paginate but it does not work with ransack, w/o writing java-ick(my alias for javascript). I had removed will_paginate from the Gemfile but had forgotten to run bundle install.
The side affect was the values in config/initializers/kaminari_config.rb were not being used but WillPaginate.per_page was being used by kaminari, pretty weird.
Anyways here’s what the kaminari config file looks like.
Kaminari.configure do |config|
config.default_per_page = 5
# config.max_per_page = nil
# config.window = 4
# config.outer_window = 0
# config.left = 0
# config.right = 0
# config.page_method_name = :page
# config.param_name = :page
end
The query
When we first started looking into this we examined using a combination of join and merges to create one query. It was a great exercise in extending our understanding but it looks like ransack is doing this for us.
The first query is searching on name and level type with default sorting on name. Limit is 5 due to kaminari configuration. We are using postgres so ilike is being used for a case insensitive search.
SELECT "component".* FROM "component"
LEFT OUTER JOIN "generic_type_item"
ON "generic_type_item"."id" = "component"."level_type_id"
WHERE (("component"."name" ILIKE '%e%' AND "generic_type_item"."name" ILIKE '%a%'))
ORDER BY "component"."name" ASC LIMIT 5 OFFSET 0
The next query was generated after clicking the sort icon, notice the only change is the ORDER BY, from ASC to DESC.
SELECT "component".* FROM "component"
LEFT OUTER JOIN "generic_type_item"
ON "generic_type_item"."id" = "component"."level_type_id"
WHERE
(("generic_type_item"."name" ILIKE '%a%'
AND "component"."name" ILIKE '%e%'))
ORDER BY "component"."name" DESC LIMIT 5 OFFSET 0
Routes
If your concerned the request length will be too long with all the search filtering you can use a POST instead of a GET, I didn’t. If you use POST rather than GET you’ll have to update your routes.
tech brown bags 