Filtering Rails 3 Database Queries Through Multiple Conditions / Filters

Question

I have a real predicament here, and I'm sure that what I'm trying to do is relatively simple. Basically, I have a database (go to the next), which lists a group of students and information about them. There are four columns, seek_position, min_hourly, max_hourly and start_weeks, and I need to be able to filter on the front end of the site. Right now, all I can understand how to do this is to show the page with all the users indicated on it. I'm not going to look for handouts here, I already went through everything I know, and even tried things that I really didn't understand in order to try to get this to work. What seems to turn me off is to find a way to filter multiple things at once. For example, show all students the “internship” search position, min_hourly from "7", max_hourly from "10" and start_weeks "from 2 to 4". Any ideas? I am working on Rails 3.0.3 using Unlined ActiveRecord. Thanks:)

My migration:

class CreateStudents < ActiveRecord::Migration def self.up create_table :students do |t| t.string :name t.string :email t.integer :phone t.text :bio t.text :resume t.string :seeking_position t.integer :min_hourly t.integer :max_hourly t.integer :start_weeks t.string :pic_uid t.timestamps end end def self.down drop_table :students end end 

Answer 1

This is a fairly simple set of conditions for your request. You can also check searchlogic . This is awesome and frees you from writing a lot of SQL.

Set up several variables

 position = "internship" min_hourly = 7 max_hourly = 18 start_weeks = 2..4 

Then write the following code:

 Student.find(:all, :conditions => ["seeking_position = ? AND min_hourly = ? AND max_hourly = ? AND start_weeks between ?", position, min_hourly, max_hourly, start_weeks]) 

In Rails 3 you can use the new where() function

 Student.where(:seeking_position => position, :min_hourly => min_hourly, :max_hourly => max_hourly, :start_weeks => start_weeks) 

Answer 2

Replace min / max hourly fields with one: hourly field, since everyone wants their max to be infinite:

 Student.where(:seeking_position => 'internship', :hourly => 7..10, :start_weeks => 2..4) 

Answer 3

In Rails 3, you can use something like this:

Student.where(:seeking_position => 'internship')

to define different queries. Here's a screencast with more details: http://railscasts.com/episodes/202-active-record-queries-in-rails-3