Creating queries

Rails
#1

Hi, I am trying to implement a few queries now. What are good ways to do
this?

Right now, I have a list page that does sorting and can carry out a
generic pagination request.
@user_result_pages, @user_results = paginate(:user_results,
:per_page => 20,
:conditions => @condition,
:order_by => @sort_order)

I create @sort_order earlier in the list function. The problem I am
having is with ‘@condition’ I’m not sure what’s the best way to create
this. There are a number of options that my user may select:

  1. date > …
  2. range of dates
  3. specific dates
  4. name is LIKE …
    and so on…

I’m therefore having trouble conceptualizing how to create this
condition string. All the examples I have seen so far talk about it as
:conditions => [?created_on > ??, time] and so on. I can’t find an
example that helps me formulate this string earlier.

Another example I saw uses ‘sanitize’ which seems to be available in the
model but not the controller, so I can’t seem to use that either.

Help would be greatly appreciated :smiley:
Thanks
Mohit.

#2

A good way to start is to remember that :conditions is not a String but
an Array.
The first index of the :conditions is the sqlish query which can be
built anyway you
see fit and the arguments can then be added (+) to it to create the
final :conditions
Array. :conditions => [query string with ?] + [arugment1]

#3

Thanks, Todd! That helped me to get started :slight_smile:
Cheers
Mohit.

#4

Hi again! I am able to create the query string correctly now for the
conditions. I’d like to use my common list function to show the
records. So, what I’d like to do is call a set of different functions
that create the correct search conditions based on what is entered in
the forms.

But, although I’m able to construct the query in a controller function
called ‘query1’ - unfortunately this does not work:

def q2
id = params[:id]

  qa = ['name like ?']
  sid = '%' + params[:name] + '%'
  @qp = [sid]
  qa << 'test_date >= ?'
  @qp << params[:start]
  @condition = [qa.join(' and ')] + @qp

  params[:condition] = @condition
  redirect_to :action => 'list2'

end

I’m confused whether I should use:
redirect_to :action => ‘list2’
or
render :action => ‘list2’

On the other side, list2 picks it up and uses it for the pagination (if
I put the above code into list2 it works fine)
@condition = params[:condition]
@kx21_result_pages, @kx21_results = paginate(:kx21_results,
:per_page => 20,
:conditions =>
@condition,
:order_by =>
@sort_order)

How do I pass the conditions array as a parameter to the redirect?

It does work if I ‘render :action => list2’ into the function that
creates the query (q2 above) - but, then I must duplicate the code that
checks and creates the strings for the sort order, etc… I guess I
could DRY it up?

Is it a good idea to break up my queries in such a manner?
Thanks
Mohit.