2

I have a view with a huge paginated list of records that need filtering.

Users can filter by records a few different ways (such as 'saved' records, 'read' records, and 'mark deleted' records) and I'd like for them to be able to combine these filters any possible way.

My current, flawed, non-functioning approach. The code below does not produce anything unless all of the params are specified and valid:

#view. Set the 'se' filter to true; leave all others as is 
<%= link_to list_url(:id=>params[:id], :se=>"true", :st=>params[:st], :re=>params[:re]) do %> 
  <div class="button">Toggle SE</div>
<% end %>

#controller query. Add whichever params are passed into the conditions for the new page.
#query is paginated and sorted
@records = Record.where("user_id IN (?) AND see = ? AND star = ? AND delete = ? AND like = ?", @users.select("id"), params[:se], params[:st], params[:re]).paginate :page => params[:page], :order => (sort_column + " " + sort_direction)

What is the best way to create this filtering system?
I imagine a client-side sort would be faster than asking the server to become involved every time - is there a simple AJAX way to accomplish this kind of thing? Imagine filters the user can toggle on and off in any combination.

sscirrus
  • 55,407
  • 41
  • 135
  • 228

2 Answers2

4

Try this:

conditions = {:user_id => @users.select("id")}
{
 :se => :see,
 :st => :star,
 :del => :delete
}.each{|k1, k2| conditions[k2] = params[k1] unless params[k1].blank?}

@records = Record.where(conditions).paginate(...)

The conditions hash will be filled based on the values present in the params hash.

Edit 1

You can combine conditions hash and array.

@records = Record.where(conditions).where(
  ":created_at > ?",  Date.today - 30).paginate(...)

You can change the user_id condition to what ever you want by specifying 

conditions[:user_id] = @user.id

In the above statement, if the RHS is an array, rails automatically generates the IN clause. Otherwise, equality check(=) is performed.

Harish Shetty
  • 64,083
  • 21
  • 152
  • 198
  • This is awesome! One last thing - how do you add the condition :created_at > Date.today - 30? I assume if :user_id => @users.select("id"), the other version :user_id => @user.id would also work? – sscirrus Nov 06 '10 at 07:23
  • 1
    Updated my answer, take a look. – Harish Shetty Nov 06 '10 at 08:01
  • But could you please explain how this works in Rails ``@records = Record.where(conditions).where([":created_at > ?", Date.today - 30])`` - does it mean 2 queries to MySQL? (it isn't effective if so) – Roman May 05 '13 at 08:12
  • OK I found an answer already (no, there are now multiple queries to DB): http://stackoverflow.com/a/10747692/530514 – Roman May 05 '13 at 08:17
  • @oyatek it results in just one SQL. The link that you referred to also implies that. – Harish Shetty May 05 '13 at 19:35
0

Can also use Anonymous scopes: Combine arrays of conditions in Rails

Community
  • 1
  • 1
gamov
  • 3,789
  • 1
  • 31
  • 28