Query the database with Rails where method with optional parameters

Question

I have index action that get parameters from url like http://localhost:3000/api/budgets?marketplace_id=1&budget_year_id=3&vendor=pepsi&details=sugarfree and use them to query the database with where method. But there are two parameters that are mandatory( marketplace_id and budget_year_id) and two more that are optional (vendor and details).For mandatory parameters i can just do Budget.where("marketplace_id=? and budget_year_id=?",params[:marketplace_id], params[:budget_year_id]) .My question is how would i query for optional parameters since they are might not always be there? Here is index action

def index
   unless params[:marketplace_id].blank? || params[:budget_year_id].blank?
      @budgets = Budget.where("marketplace_id=? and budget_year_id=?",params[:marketplace_id], params[:budget_year_id])# How do i add optional parameters ,vendor and/or details
      respond_with (@budgets)
   else
      render :json=>{:errors =>"Marketplace_id and Budget_year_id must be present",:status=>402}
   end
end

Try @budgets = Budget.where("marketplace_id=? and budget_year_id=?",params[:marketplace_id], params[:budget_year_id]).where("vendor=? or details=?",params[:vendor],params[:details]) — Pavan
– Pavan, Commented Jul 2, 2015 at 16:08

sockmonk · Accepted Answer · 2015-07-02 16:29:43Z

2

You can add additional where clauses to your original query only if the optional parameters are present. The query won't be executed until you try to access the results of the query. For instance:

@budgets = Budget.where(marketplace_id: params[:marketplace_id], 
  budget_year_id: params[:budget_year_id])

@budgets = @budgets.where(vendor: params[:vendor]) if params[:vendor]
@budgets = @budgets.where(details: params[:details]) if params[:details]

answered Jul 2, 2015 at 16:29

sockmonk

4,26526 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

yaswant singh Over a year ago

how to do if more than 15 parameters are there in the query ?

Collectives™ on Stack Overflow

Query the database with Rails where method with optional parameters

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related