How to build a query in a controller but only make one db call

Question

My idea for an #index method of a controller is to set things = Thing.all and then if there are filter params, check for them one by one and chain them on so that at the end, you're left with a single query to execute. But the following queries get executed as they are called:

def things_controller
  def index
    things = Thing.all #<-- db call #1

    if params[:color]
      things = things.where(color: params[:color]) #<-- db call #2
    end

    render json: things  #<-- I would like to make a single db call here instead
  end
end

How can I prevent multiple unnecessary db calls? Is there some convention for filter params that I should be using?

kasperite · Accepted Answer · 2019-01-04 09:00:10Z

1

You just need to reorganise the code like this:

  def index
    things = if params[:color]
               Thing.where(color: params[:color])
             # ...else if 
             else
               Thing.all
             end
    render json: things
  end

Updated

If you want to chain where clauses, do this:

def index
  valid_params_keys = %w(color size)

  filtered_keys = valid_params_keys.select { |key| params.keys.include?(key) }

  # Don't need conditional check anymore :).
  @products = filtered_keys.inject(Product.all) do |scope, key|
    scope.where(key => params[key])
  end 
end

edited Jan 4, 2019 at 9:00

answered Dec 21, 2018 at 2:25

kasperite

2,4781 gold badge16 silver badges17 bronze badges

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

4 Comments

wetjosh Over a year ago

But imagine if there were two params: color and size. You couldn’t just add an elsif for the size param. It would stop after the if part. That’s why I want to construct the query first, and then tell it to execute. I might just have to do it with strings and then use ‘connection.execute’, but was hoping to be able to do it by chaining ‘where’s

kasperite Over a year ago

Yea, If you know what params keys are in advance, you can chain where clause using inject. That wouldn’t be too hard

wetjosh Over a year ago

You're not talking about Enumerable#inject are you? Can you give me an example? I'd be very interested if this will do the trick. Tho I'm still thinking maybe theres just some completely other standard way to write a controller that I just don't know about.

kasperite Over a year ago

hiya, updated my answer. Check rails log to see in action :).

Peter Zhu · Accepted Answer · 2018-12-21 01:35:07Z

0

Since things is an array, you can do this, which is only an array operation.

def index
  things = Thing.all

  if params[:color]
    things = things.select!{ |thing| thing.color == params[:color]}
  end

  render json: things 
end

answered Dec 21, 2018 at 1:35

Peter Zhu

4616 silver badges17 bronze badges

1 Comment

kasperite Over a year ago

doing this is less optimal if table is large though

Collectives™ on Stack Overflow

How to build a query in a controller but only make one db call

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related