Using Paginate with constructed query in active record

Question

I'm trying to do something along the following lines:

if start_date?
  query = where("datetime > :start_date", :start_date => start_date)
end

if end_date?
  query = where("datetime <= :end_date", :end_date => end_date)
end

if client
  query = where("client.name LIKE :client", :client => client)
end

if staff
  query = where("staff.name LIKE :staff", :staff => staff)
end

paginate :per_page => 20, :page => page,
           :conditions => query,
           :order => 'datetime'

I can't find any examples though of how to build up a query and use it along with paginate plugin. Of course the query isn't correct yet as I've got to figure out how to do the joins so I can query against the other properties in other tables, but other than that, how do you build up queries to use in the conditions?

Answer 1

You want to put the .paginate call on the end of the chain of where calls, like this (all one line, split up for readability)

SomeClass.where("datetime > :start_date", :start_date=>start_date).
  where("datetime < :end_date", :end_date=>end_date).
  paginate(:per_page=>20,:page=>page)

If you want to conditionally add the conditions, you can do that too, like:

x = SomeClass
x = x.where("datetime > :start_date", :start_date=>start_date) if start_date
x = x.where("datetime < :end_date", :end_date=>end_date) if end_date
x.paginate(:per_page=>20,:page=>page)

Answer 2

You can try this if you installed kaminari gem :

ModelName.where( your_query ).page(params[:page]).per(50)