CODEWITHSUNDEEP
ruby-on-rails
Robert Thomas
Robert Thomas

Reputation: 77

Query on Params

I want to get records based on params received. Sometimes I receive 2 params or sometimes 3. I have written a code to get results when all 3 params received but when I receive only 1 param am getting 0 results.

Example 

Office.where(state: params[:state], type: params[:type]).where("name like ?","%#{params[:name]}%")

When i get values like

{state: "KA", type: "private", name: "google"}

But when I get the only {name: "google"} I get no records

I have tried with condition

If params[:name].present? && params[:state].present? && params[:type].present?
query
elsif condition
query
end

Let me know how can I solve this or any better way

Upvotes: 3

Views: 500

Answers (2)

Rajdeep Singh
Rajdeep Singh

Reputation: 17834

You can do something like this

In controller

filter_params = params.slice(:state, :type, :name)
Office.filter(filter_params)

In Office model

scope :state, -> (state) { where(state: state) }
scope :type, -> (type) { where(type: type) }
scope :name, -> (name) { where("name LIKE ?", "%#{name}%") }

def self.filter(filter_params)
  results = where(nil)
  filter_params.each do |key, value|
    results = results.public_send(key, value) if value.present?
  end
  results
end

PS: It runs a single query irrespective of the number of params!

Hope that helps!

Upvotes: 4

Schwern
Schwern

Reputation: 164679

If a parameter is missing, it will probably be blank. If you pass them all in this will result in clauses like type = ''. For example, if only name is passed in you'll get something like...

where name like '%google%' and type = '' and state = ''

You need to strip out the blank fields. There are various ways to do this. Since you have a special case, the name clause, one good way to handle this is to build the query piece by piece.

query = Office.all
query = query.where(state: params[:state]) if params[:state].present?
query = query.where(type: params[:type]) if params[:type].present?
query = query.where("name like ?","%#{params[:name]}%") if params[:name].present?

The query does not execute until you fetch the values from query.

If there's a lot of simple parameters you can make a Hash and remove the pairs with a blank value.

qparams = {
  state: params[:state],
  type: params[:type]
}.select { |k,v|
  v.present?
}

query = Office.where(qparams)
query = query.where("name like ?","%#{params[:name]}%") if params[:name].present?

Or use the handy compact_blank gem.

using CompactBlank

qparams = {
  state: params[:state],
  type: params[:type]
}.compact_blank

query = Office.where(qparams)
query = query.where("name like ?","%#{params[:name]}%") if params[:name].present?

Upvotes: 3

Related Questions