CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3where-clauseparameters
Rustam Gasanov
Rustam Gasanov

Reputation: 15781

Rails 3 where with multiple params

I need to build a dynamic sql queue with 2 incoming params. It is easy when both params are defined. 

MyClass.where(:column1 => param[:first], :column2 => params[:second])

but when for example param[:first] = 0 I want to select all(not null) fields for this column(so when both params are = 0 it would be equal to select * from tablename). Tried this syntax: 

MyClass.where(:column1 => param[:first], :column2 => !nil)

but it gives me wrong output. Any suggestions how to elegantly resolve this?

Upvotes: 2

Views: 1532

Answers (3)

Stefan
Stefan

Reputation: 114138

You could use the ?: operator inside where:

MyClass.where(params[:first] ? {:column1 => params[:first]} : "column1 IS NOT NULL")
       .where(params[:second] ? {:column2 => params[:second]} : "column2 IS NOT NULL")

Upvotes: 3

Travis Pessetto
Travis Pessetto

Reputation: 3298

I think this may work from what I've found. It appears to work in the rails console and considering active record alows active chaining:

MyClass.where(:column1 => params[:first], :column2 => params[:second]).where("column1 is NOT NULL").where("column2 is NOT NULL")

Upvotes: 1

iblue
iblue

Reputation: 30404

What about MyClass.where('column1 IS NOT NULL AND column2 IS NOT NULL').where({:column1 => params[:first], :column2 => params[:second]}.delete_if{|k,v| v.nil?})?

Upvotes: 1

Related Questions