Rails: Building Complex Search Filters with ActiveRecord and ez_where – Part 3
(No Ratings Yet)
Loading ...
The source code for this tutorial is available from github:
git clone git://github.com/cblunt/blog-complex_search_filters_with_rails.git
This series of posts shows how to use the ez_where plugin to build a complex search filters into a Rails model. Last time we built a User model with a search method that allowed it to perform complex searches on terms and attributes. In this post, you’ll see how to tie everything together using a controller and search form.
Populating the Database
Before we get started, it would be handy to populate our database with a lot of data to see the search controller in action. To create fixture data, I use Ryan Bates’ Populator and Benjamin Curtis’ Faker gems. Once installed, a short rake task is all that’s needed to populate the database with some pseudo-random data. Alternatively, you can manually create some user records, e.g. by using script/console.
Lets add a populator rake task. First, make sure you have the faker and populator gems installed:
sudo gem install faker sudo gem install populator
Then add a populator task to your project, in a new file lib/tasks/populate.rake:
# lib/tasks/populate.rake namespace :db do namespace :populate do desc "Populate the users table with 1000 random users" task :users => :environment do require 'populator' require 'faker' # Destroy existing data User.destroy_all # Repopulate User.populate(1000) do |user| user.first_name = Faker::Name.first_name user.last_name = Faker::Name.last_name user.email_address = Faker::Internet.email user.status = rand(5) + 1 user.admin = (rand(10) < 5 ? true : false) end end end end
With that in place, run the rake task to populate your database with some users:
rake db:populate:users(For a more thorough tutorial of Populator and Faker, see Ryan’s screencast).
Generating the Controller
With plenty of data to work with, it’s time to start building our users controller. In this example, I’ve only included the index and show actions. For a real-world app, you would want to include the remaining RESTful actions (edit, create, etc.).
ruby script/generate controller users index show
You also need to add a route to your config/routes.rb file so Rails can access the users controller RESTfully:
# config/routes.rb map.resources, :users
With some routes set up, it’s a good idea to check that everything is connected properly. To do this, we’ll just ask the users controller index action to fetch a list of users. Remember that, without any parameters, our User.search method just wraps the User.find base method:
# app/controllers/users_controller.rb def index @users = User.search(:all) end
We’ll then output these user’s on the index page:
# app/views/users/index.html.erb
<h1>Users</h1>
<table>
<tr>
<th>Name</th>
<th>Email Address</th>
<th>Administrator</th>
<th>Status</th>
</tr>
<% @users.each do |user| %>
<tr>
<td><%= h [user.first_name, user.last_name].join(' ') %> </td>
<td><%= h user.email_address %></td>
<td><%= user.admin? ? 'Yes' : 'No' %></td>
<td><%= user.status %></td>
</tr>
<% end %>
</table>A quick visit to http://localhost:3000/users will check everything’s hooked up (make sure your server is running; script/server will start the development server). If all has gone well, you will have a list of several hundred user names.
The Search Form
Next, we’ll add a text box so that we can search for users by terms. These terms will be passed on to the User.search method and used to filter users by first or last name, or email address. See part 1 for more information on how this works.
In your index view, add the following form just above the results table:
# app/views/users/index.html.erb <% form_tag users_path, :method => :get do %> <%= text_field_tag "search[terms]", params[:search][:terms] %> <%= submit_tag "Search" %> <% end %> # ...
Notice that we need to make the form submit a GET request; if we used the default POST request, Rails’ RESTful routes would assume we wanted to create a new user. If you now reload the page in your browser, entering some search terms should direct you to a URL similar to:
http://localhost:3000/users?search[terms]=joe&commit=Search
So now all we need to do is pass on those search parameters from our controller to User.search. In your controller, change the index action to:
# app/controllers/users_controller.rb def index params[:search] ||= {} @users = User.search(:all, :filters => params[:search]) end
Now reload your page, and enter some search terms in the text field. You’ll notice that any terms you enter are matched against the users’ names or email addresses.
The Final Touches
With the general search terms working, it would be useful to narrow down our search according to some attribute filters. To do this, we’ll add a checkbox and dropdown menu that allows us to filter results by role and admin status. In index.html.erb, update your form with two new tags:
# app/views/users/index.html.erb <% form_tag users_path, :method => :get do %> <%= text_field_tag "search[terms]", params[:search][:terms] %> <br /> Only Admins <%= check_box_tag "search[admin]", true, params[:search][:admin] %> <br /> Status: <%= select_tag "search[status]", options_for_select([["- All -", nil], ["Viewer", "1"], ["Member", "2"], ["Subscriber", "3"], ["Publisher", "4"], ["Editor", "5"]], params[:search][:status]) %> <br /> <%= submit_tag "Search" %> <% end %>
Your controller will now automatically pass on the :admin and :status values to User.search. However, if you try searching for administrators by checking the box, you’ll see that no users are returned!
The reason for this is how Rails passes parameters into your controller. The check_box_tag returns a string value depending on its status. Our User.search method expects an :admin filter to be a Boolean value, or nil. So all we need to do is tell Rails to convert the string “true” to a boolean true, or set the :admin filter to nil.
This is easy to solve by adding a single line in your controller to map the value of the checkbox to true or nil:
# app/controllers/users_controller.rb def index params[:search] ||= {} # Ensure that params[:search][:admin] is either true or nil. params[:search][:admin] = (params[:search][:admin] == "true" ? true : nil) User.search(:all, :filters => params[:search]) end
Now when you reload the page, you’ll be able to search for users and drill down your search results using your new form!
Next Steps
I hope you’ve found this mini-tutorial series useful and interesting. It’s been a great learning experience for me in writing tutorials, and forcing me to refine my ruby coding. I’ve also found the search functionality useful in my own projects, and have decided to develop it into a generic ActiveRecord plugin. I’ll continue to post updates about progress on this blog, and on my github account.
In the near future, I’ll post some supplementary information on using the search methods with the will_paginate plugin, and how to AJAXify your seach forms.
In the meantime, if you have any comments or feedback, please let me know in the comments. Thanks!
Rails: DRY controllers with ResourceController
(No Ratings Yet) Loading ...Rail’s generated RESTful controllers are great for quickly building up an interface to your models, but I quickly grew tired of writing the same code for simply managing resources through REST. This was compounded once I started using Rspec to test my controllers, and found myself writing endless virtually identical tests. So, inspired by some reading, I set about DRYing up all this boilerplate code and built a ResourceController class.
ResourceController extends the normal ActionController, but provides a series of hooks for fetching, creating, updating and deleting resources. By default, these hook methods are called automatically by the default REST actions (index, show, new, create, edit, update, and destroy). This means creating a standard controller is as simple as:
# app/controllers/apples_controller.rb class ApplesController < ResourceController end
The hook methods are protected, so their default functionality can be overridden:
fetch_resources – called before index
fetch_resource – called before show, edit, update, destroy
create_resource – called before new
save_resource – called by create, update
destroy_resource – called by delete
ResourceController infers the name of the resource it is managing from the name of the subclass, so an ApplesController will manage Apple models. Where appropriate, the name will be pluralized, so in this example, the index action will automatically generate an instance variable array called @apples.
The code comes with RSpec controller examples.
Caveats
Currently, ResourceController cannot handle resources that are nested.
Installation and Use
Assuming a new Rails project with a users model:
rails demo cd demo script/generate rspec script/generate model user email_address:string password_hash:string password_salt:string active:boolean rake db:migrate
ResourceController depends on RESTful routes, so add an entry to your config/routes.rb:
# config/routes.rb # ... map.resources :users # ...
Next, in your project folder, generate a controller in the normal way
script/generate rspec_controller users index show new create edit update destroy
Modify the generated controller to extend ResourceController:
# app/controllers/users_controller.rb class UsersController < ResourceController # Remove any generated stub methods end
Copy the ResourceController files into your project:
cp resource_controller.rb {app_path}/lib/ cp resource_controller_spec.rb {app_path}/spec/controllers/
Next, modify the UsersController’s spec file, removing RSpec’s auto-generated examples:
# spec/controllers/users_controller_spec.rb require File.expand_path(File.dirname(__FILE__) + '/../spec_helper') require File.expand_path(File.dirname(__FILE__) + '/resource_controller_spec') # Be sure to add this line describe UsersController do it_should_behave_like "a resource controller" end
You can now check that the controller works as expected by running:
rake spec:controllers
Please feel free to make use of and modify this code as you wish; if you do improve it, please leave a comment below or email me and I’ll update here. I’ll also post the code on either Launchpad or github. I’m preferring bazaar’s simplicity to git at the moment.
Resources
resource_controller.tar.gz
resource_controller_demo.tar.gz
Rails: Building Complex Search Filters with ActiveRecord and ez_where – Part 2
(2 votes, average: 5.00 out of 5) Loading ...In the first part of this tutorial, we used the ez_where plugin to build a more complex search filter into a User model class. In this tutorial, we’ll extend the search filters with additional criteria, and in part 3 we’ll build a controller that ties all the functionality together.
Searching Email Addresses for Terms
Currently, our User class’ search method accepts a :terms key as part of its options hash that is used to filter first and last names. For searches, I prefer a single text box that searches all the text data in a model – Google style – rather than separate boxes for first name, last name, email address, etc. To make the :terms filter search email addresses, just add the highlighted line to your code:
# app/models/user.rb unless filters[:terms].nil? filters[:terms].each do |term| term = ['%', term, '%'].join condition = Caboose::EZ::Condition.new :users condition.append ['first_name LIKE ?', term], :or condition.append ['last_name LIKE ?', term], :or condition.append ['email_address LIKE ?', term], :or # << find users by email address combined_conditions << condition end end
You could now search for all users named Mary with an email address at company.com using:
User.search :all, :filters => { :terms => %w(mary company.com)}
Filtering Additional Criteria
For large data sets, you’ll probably need to add more granular filters, search as searching for active or inactive clients, or searching for users who are only admins. Our User.search method can be extended to do that by adding more options to the :filters hash:
# app/models/user.rb # Apply the :admin filter unless filters[:admin].nil? condition = Caboose::EZ::Condition.new :users do admin == filters[:admin] end combined_conditions << condition end
Notice here I’ve used ez_where’s block notation to build the condition. Within the do…end, you can make use of ez_where’s ruby-like syntax for conditions. For example,
Caboose::EZ::Condition.new :users do :first_name ~= '%' + term + '%' # ['first_name LIKE ?', '%' + term + '%'] :level <=> (5..10) # ['level BETWEEN ? AND ?', 5, 10] :authorised == true # ['authorised = ?', true] :expired_at < 30.days.from_now # 'expired_at < ?', 30.days.from_now] :permissions === [1, 5, 8] # ['permissions IN (?), [1, 5, 8'] end
There are other operators as well (see the documentation), and you can even nest conditions within a block for complex queries. However, each of these conditions is joined with an AND clause, which is why we couldn’t use the block notation for the :terms filter.
Finally, we’ll add the :status option to our User.search:filters hash. In our User model, status is an integer representing the user’s state or level of authorisation. This could be represented in a settings hash, for example:
:normal => 0, :author => 1, :editor => 2
Our :status filter will take an array of states and use the SQL IN clause to filter the appropriate users:
# app/models/user.rb # Apply the :status filter unless filters[:status].nil? condition = Caboose::EZ::Condition.new :users do status === [*filters[:status]] # use [*obj] rather than obj.to_a as Object.to_a is depracated end combined_conditions << condition end
So, we can now filter users by their status and/or admin attributes using:
User.search :all, :filters => { :admin => true, :status => 2} User.search :first, :filters => { :admin => false, :status => [0, 2]}
The next post will show how to build a controller and search form that lets users filter perform complex searches using the new User.search method that we’ve built. In the meantime, please discuss in the comments.
Resources
Full source code for the user model (user.rb.tar.gz)
Rails: Building Complex Search Filters with ActiveRecord and ez_where
(3 votes, average: 4.33 out of 5) Loading ...Rails’ out-of-the box ActiveRecord is great for doing simple searches, but today’s web-apps often require more complex filters that are not so easy to achieve using the basic condition-builders provided by ActiveRecord.
Recently, I’ve been experimenting with the ez_where plugin to allow complex searches to be performed on a model. Unfortunately, from what I can tell, there’s not been much development on this plugin for a while.
This is a shame, because ez_where adds some great functionality to ActiveRecord. It allows you to build up complex SQL conditions using a very Ruby-like syntax. For example, you can build a fuzzy (I)LIKE condition using:
first_name =~ ['%', search_terms, '%'].join
Ez_where’s real power, though, lies in its ability to build complex search criteria by combining conditions with both AND or OR operators. This is similar to my experience with other frameworks, notably symfony’s depracated Propel library, and a custom ORM framework I built in PHP.
Complex conditions are built in ez_where with the Caboose::EZ::Condition class. For example, we can build the following SQL query fragment:
... WHERE (first_name LIKE "%search_terms%" OR last_name LIKE "%search_terms%") AND (STATUS = 1 OR is_admin = TRUE) ...
Using ez_where, you break each condition into a Condition class:
combined_conditions = Caboose::EZ::Condition.new :users search_terms = ['%', search_terms, '%'].join # Build conditions for the search terms for the users table condition = Caboose::EZ::Condition.new :users condition.append ['first_name LIKE ?', search_terms], :or condition.append ['last_name LIKE ?', search_terms], :or combined_conditions.append condition # Build conditions for the status or is_admin flag condition = Caboose::EZ::Condition.new :users condition.append ['status = 1'], :or condition.append [is_admin = TRUE], :or combined_conditions.append condition # Expand the combined conditions into an ActiveRecord query User.find(:all, :conditions => combined_conditions.to_sql)
You can install the latest version of ez_where with the following command:
ruby script/plugin install http://opensvn.csie.org/ezra/rails/plugins/dev/ez_where
Build a Search Method Example
Once installed, we can add a search method to a User ActiveRecord class. Like ActiveRecord’s find method, search will take a hash of options. One of the options will be another hash called :filters, which will be used to build up a set of conditions. These conditions, along with any other options, will then be passed on to the find method.
Begin by stubbing the method:
class User < ActiveRecord::Base def self.search(*args) options = args.extract_options! self.find(args.first, options) end end
This stub just forwards our options hash onto the normal find method, so User.search is the same as User.find.
Next, we need to add the code to build our filters – but first, it would help to know what attributes the User model has. For a dynamic method (e.g. in a plugin), we could use User.column_names; however, for now we’ll code the attributes in to search for a first and last name.
def self.search(*args) options = args.extract_options! # Extract filters from the options, or default to empty filters = options.delete(:filters) || {} # Create an empty condition clause, into which we can append filters combined_conditions = Caboose::EZ::Condition.new :users # Use filter terms to search by first_name OR last_name. Terms are supplied as an array of strings (e.g. ["joe", "bloggs"]) unless filters[:terms].nil? filters[:terms].each do |term| term = ['%', term, '%'].join condition = Caboose::EZ::Condition.new :users condition.append ['first_name LIKE ?', term], :or condition.append ['last_name LIKE ?', term], :or combined_conditions << condition end end # Convert the combined set of filter conditions to a SQL fragment, and store in the options hash for User.find options[:conditions] = combined_conditions.to_sql self.find(args.first, options) end
We can now use the User.search and a :filters hash to perform more complex searches for user records based on the supplied search terms, e.g:
# SELECT * FROM users WHERE (first_name LIKE '%joe%' OR last_name LIKE '%joe%') User.search(:all, :filters => {:terms => %w(joe)}) => [User<"Joe Bloggs">, User<"Joe Smith">] # SELECT * FROM users WHERE (first_name LIKE '%joe%' OR last_name LIKE '%joe%') AND (first_name LIKE '%bloggs%' OR last_name LIKE '%bloggs%'); User.search(:all, :filters => {:terms => %w(joe bloggs)}) => [User<"Joe Bloggs">]
Furthermore, because our code ends up using the standard User.find method, we can make use of other ActiveRecord extensions, e.g. will_paginate:
# user.rb def self.paginate_search(*args) # ... options[:conditions] = combined_conditions.to_sql options[:page] ||= 1 self.paginate(args.first, options) end
In the next post, I’ll show how to add more complex criteria to the search filters, and build a search form to make use of the new method. In the meantime, if you are using ez_where or another plugin for complex search criteria, please let me know and/or discuss in the comments.
Read: Part 2
Rails: Retaining select_tag’s Selected Option
(1 votes, average: 3.00 out of 5) Loading ...Working with Rails’ select_tag helper to build a search form, I needed to retain the user’s selected value after a form get, and was using the options_for_select helper:
# Doesn't retain value <%= select_tag 'search[status]', options_for_select([['- All -', nil]] + Settings[:states].collect { |name, id| [name.to_s.titlecase, id] }, params[:search][:status]) %>
Whenever the form was posted, the select popup would always revert to its default value.
The reason? params[:search][:status] is by default a String, whereas the option values are integers, and so don’t match (”1″ != 1). The simple fix is to convert it to an integer using to_i:
# Convert the given params[:search][:status] to an integer using to_i <%= select_tag 'search[status]', options_for_select([['- All -', nil]] + Settings[:states].collect { |name, id| [name.to_s.titlecase, id] }, params[:search][:status].to_i) %>
Update: Fixed missing bracket in code (25 May 2009)
I am a software developer based in South West England. After studying at the