20

In my Rails application I want to use the will_paginate gem to paginate on my SQL query. Is that possible? I tried doing something like this but it didn't work:

@users = User.find_by_sql("
    SELECT u.id, u.first_name, u.last_name, 
     CASE 
      WHEN r.user_accepted =1 AND (r.friend_accepted =0 || r.friend_accepted IS NULL)
       .........").paginate(
                  :page => @page, :per_page => @per_page, 
                  :conditions => conditions_hash,
                  :order => 'first_name ASC')

If not, can you recommend a way around this? I don't want to have to write my own pagination.

Makoto
  • 104,088
  • 27
  • 192
  • 230
Tam
  • 11,872
  • 19
  • 69
  • 119

3 Answers3

52

Use paginate_by_sql, i.e.

sql = " SELECT * 
        FROM   users
        WHERE  created_at >= ?
        ORDER  BY created_at DESC "

@users = User.paginate_by_sql(
  [sql, 2.weeks.ago],
  page: @page,
  per_page: @per_page
)

As a general rule any finder can be paginated by replacing the find* with paginate*.

Harish Shetty
  • 64,083
  • 21
  • 152
  • 198
-2

User.paginate_by_sql(sql, :page => params[:page], :per_page => 10)

Sobin Sunny
  • 1,121
  • 10
  • 14
-3

Try this:

@users = User.find_by_sql 
@users = @users.paginate

What will_paginate and rails version are you using?

Joshua Partogi
  • 16,167
  • 14
  • 53
  • 75
  • 5
    In your solution, pagination is applied on the result set returned by the `find_by_sql`. So you might have to deal with a large result-set and hence the system has to perform redundant computing. – Harish Shetty Mar 29 '10 at 07:30