Filtering Rails 3 database query through multiple conditions/filters

Question

I'm in a real predicament here and I'm pretty sure that what I'm trying to do is relatively simple. Basically I have a database (migration to follow) that lists out a bunch of students and information about them. There are four columns, seeking_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 figure out how to do is show a page with all of the users listed on it. I'm be no means looking for a handout here, I've already gone through everything I know and even tried stuff I didn't really understand to try and get this working. What seems to be tripping me up is finding a way to filter by multiple things at the same time. For example, show all students with a seeking_position of "internship", a min_hourly of "7", a max_hourly of "10", and a start_weeks of "2 to 4". Any ideas? I'm on Rails 3.0.3 using ActiveRecord without scaffolding. 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

Solution 1

This is a fairly straightforward set of conditions for your query. You should also check out searchlogic. It's awesome and frees you from writing a lot of SQL.

Set up a few variables

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

Then write some code like this:

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)

Solution 2

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

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

Solution 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