CODEWITHSUNDEEP
ruby-on-railsactiverecord
a9120bb
a9120bb

Reputation: 75

ActiveRecord limit/offset for pagination in rails 3

I'm struggling with a seemingly simple offset/limit pagination query in rails 3.

The class:

class Topic < ActiveRecord::Base

set_primary_key "thread_id"
set_table_name "threads"

belongs_to :forum_board
belongs_to :user

has_many :posts, :foreign_key => "thread_id", :dependent => :destroy

attr_accessible :board_id, :title, :modified_date, :status, :post_count, :user_id, :posts_attributes

accepts_nested_attributes_for :posts, :allow_destroy => :true

attr_accessor :board_title, :admin_mode, :orig_page_number, :page_number,
            :per_page, :last_page, :ban_list

And the query is this:

Topic.includes(:posts, :user).where("threads.thread_id=? and messages.status=2", thread_id).order("messages.pdate").limit(per_page).offset(offset).first

The quandary is that whenever the offset>1, the results are coming back empty. So for example, limit=5, offset=5 (with 20 rows available) fails.

I'm also seeing the following SQL run ahead of the query on the child table, which in fact returns no results due to the DISTINCT clause:

SELECT DISTINCT `threads`.thread_id FROM `threads` LEFT OUTER JOIN `messages` ON `messages`.`thread_id` = `threads`.`thread_id` LEFT OUTER JOIN `users` ON `users`.`user_id` = `threads`.`user_id` WHERE (threads.thread_id='20367' and messages.status=2) ORDER BY messages.pdate LIMIT 1 OFFSET 5

I'm sure I've just made a mess of limit/offset here. Any help appreciated.

Upvotes: 1

Views: 7792

Answers (1)

PaReeOhNos
PaReeOhNos

Reputation: 4428

I'm not 100% sure what's going wrong there as it looks right, but take a look at the Kaminari gem, either for the source or to actually use it.

It essentially handles pagination for you and makes it REALLY easy to use. In your example, with Kaminari you'd just do

Topic.page(1).per(5)

You can still add all of your conditions in as well, but it also comes with view helpers to setup pagination links.

But as I say, take a look through the gem source to see what it's doing and compare with yours, and it looks right. It might be that MySQL works like other DMBS's I've used, in that the limit and the offset don't work how you expect. By limiting the query to 5 results, you're expecting to get just 5 which is fine. If you then offset by 5, after you've limited it to 5 results, there are then no more results to offset by?

I could be COMPLETELY wrong there so I'd suggest looking at the kaminari code but just a hunch :)

Upvotes: 2

Related Questions