How can I get last ActiveRecord query data?

Question

I'm new to Ruby on Rails, so you forgive myself if the question is silly ...

I have a table and two forms, the first form is to filter (through checkboxes) and the second one is to search into the table.

What I would like is to search into the table with the filters already applied. I do not really know to do... how I can access from the controller search method to "already filtered" data.

Source code:

prices_controller.rb

def filter()
 @prices = Price.where(date: Date.today).order(price: :asc)
  if params[:filter1] != nil 
    @prices = @prices.where('filter1_field IN (?)', params[:filter1])
  end
  if params[:filter2] != nil 
    @prices = @prices.where('filter2_field IN (?)', params[:filter2])
  end
  respond_to do |format|
    format.js
  end
end

def search()
  # Here I would like to use filtered prices, not all prices
  @prices = Price.where(date: Date.today).order(price: :asc)
  if params[:search] != nil
     @prices = @prices.where('name LIKE ?', "%#{params[:search]}%")
  end
end 

It works, but I am not using filtered values... Are there any way to save the last query or something like that. I try to return to the search method the ActiveRecord::Relation but method where does not exists in this class. Other alternative is use the same form to filtering and searching.

I accept any kind of recommendation.

Thanks a lot.

Answer 1

You can't do this directly because

1. The request that applies the filters and the request that triggers the search are 2 different requests.
2. Rails initializes a new controller instance for each request, so the controller for filtering and the controller for searching are 2 different objects. They don't share any instance variables.

You can however store the IDs returned by the filter phase in session, and use those IDs in search phase.

def filter
  @prices = Price.where(date: Date.today).order(price: :asc)
  if params[:filter1] != nil 
    @prices = @prices.where('filter1_field IN (?)', params[:filter1])
  end
  if params[:filter2] != nil 
    @prices = @prices.where('filter2_field IN (?)', params[:filter2])
  end      

  session[:price_ids] = @prices.pluck(:id)

  respond_to do |format|
    format.js
  end
end

def search

  @prices = session[:price_ids].present? ? 
            Price.where(id: session[:price_ids]) :
            Price.all

  @prices = @price.where(date: Date.today).order(price: :asc)
  if params[:search] != nil
     @prices = @prices.where('name LIKE ?', "%#{params[:search]}%")
  end
end

This approach has a drawback that you have to remove price IDs from sessions at the proper time, which can be hard do define.

Another approach is to ensure the browser sending the filter every time it requests a search (unless your Rails app is an API for single page application). This approach requires a redirect whenever filter is changed.

def filter
  redirect_to search_path(params.slice(:filter1, :filter2))
end

def search
  @prices = Price.where(date: Date.today).order(price: :asc)
  if params[:filter1] != nil 
    @prices = @prices.where('filter1_field IN (?)', params[:filter1])
  end
  if params[:filter2] != nil 
    @prices = @prices.where('filter2_field IN (?)', params[:filter2])
  end
  if params[:search] != nil
     @prices = @prices.where('name LIKE ?', "%#{params[:search]}%")
  end
end

And your search form should contain 2 hidden fields for the filters

<%= form_tag '/prices/search', method: :get do %>
  <%= hidden_field_tag :filter1, params[:filter1] %>
  <%= hidden_field_tag :filter2, params[:filter2] %>
  <!-- other form stuff ... -->
<% end %>

Answer 2

There's a very bad bug here: your IN is broken.

ActiveRecord does SQL sanitization (to prevent SQL injection), so when you write:

query.where("a in ?", "hey")

You get WHERE a in "hey" on the SQL side. It's the same in your query: your IN's parameter will get stringified.

If your filter1 is a,b,c, the query will look like this:

WHERE a iN ("a,b,c")

Which is not what you want at all.

ActiveRecord is able to generate IN when you pass an array. So this:

query.where(a: [1, 2, 3])

will generate:

WHERE q IN (1, 2, 3)

which is what you want!

---

Now, with regards to the code itself...

The simplest way to do what you want is to move the code to another method, and reuse it in both places:

def filter()
 @prices = build_filter_query(Date.today, params[:filter1], params[:filter2])
  respond_to do |format|
    format.js
  end
end

def search()
  @prices = build_filter_query(Date.today, params[:filter1], params[:filter2], params[:search]) #pass in an extra param here!
end

private
  def build_filter_query(date, filter1, filter2, search = nil)
    # use a local variable here
    prices = Price.where(date: Date.today).order(price: :asc)
    if filter1
      prices = prices.where(filter_field1: filter1)
    end
    if filter2
      prices = prices.where(filter2_field: filter2)
    end
    if search
       prices = prices.where('name LIKE ?', "%#{search}%")
    end
    return prices
  end