Using Will_Paginate Without :Total_Entries to Improve a Lengthy Query

Using will_paginate without :total_entries to improve a lengthy query

There are many occasions where will_paginate does a really awful job of calculating the number of entries, especially if there are joins involved that confuse the count SQL generator.

If all you need is a simple prev/next method, then all you need to do is attempt to retrieve N+1 entries from the database, and if you only get N or less than you're on the last page.

For example:

per_page = 10
page = 2

@entries = Thing.with_some_scope.find(:all, :limit => per_page + 1, :offset => (page - 1) * per_page)

@next_page = @entries.slice!(per_page, 1)
@prev_page = page > 1

You can easily encapsulate this in some module that can be included in the various models that require it, or make a controller extension.

I've found that this works significantly better than the default will_paginate method.

The only performance issue is a limitation of MySQL that may be a problem depending on the size of your tables.

For whatever reason, the amount of time it takes to perform a query with a small LIMIT in MySQL is proportional to the OFFSET. In effect, the database engine reads through all rows leading up to the particular offset value, then returns the next LIMIT number rows, not skipping ahead as you'd expect.

For large data-sets, where you're having OFFSET values in the 100,000 plus range, you may find performance degrades significantly. How this will manifest is that loading page 1 is very fast, page 1000 is somewhat slow, but page 2000 is extremely slow.

Getting the records count at current page in rails with will_paginate gem

If u need total entries in entire query:

@collection.total_entries

If u need entry count in current page:

@collection.size

Meanwhile u dont need to call last .all scope in pagination. Just try:

@collection = User.paginate(page: params[:page], per_page: 10)

Help interpret will_paginate docs?

By default will_paginate uses count (from ActiveRecord::Calculations) to figure out the total number of objects you're paging through. But if you know better, or straight count won't work, you can calculate and provide :total_entries yourself.

The :count parameter is for extra arguments to the ActiveRecord::Calculations#count method, like :distinct. You can look at the docs for the complete list.

Getting total result count from Rails query using will_paginate

Try <%=@documents.total_entries%>

Cannot get will paginate total_entries to work, rails

The total_entries option just is a shortcut to avoid will_paginate querying the count for the results. So if you really want to limit the number of results shown in will paginate, but not mess up with the pagination results when there are only a few results you can do something like this:

total_records = User.user_search(params[:name], params[:gender]).count
total_entries = total_records > 30 ? 30 : total_records
@users = User.user_search(params[:name], params[:gender]).paginate(page: params[:page], per_page: 20, total_entries: total_entries)
render 'user_results'

You might think that this adds an additional query, but will_paginate was still going to do the count query in order to do its logic, here we're just overriding the total_entries by setting a max number of records to show.

Note however that by doing this you'll still get more records in the last page (if the result of dividing the total_records between the pages is not exact), or it might even be possible to ask for a higher page number and still get the 'hidden' results.

If you really need to avoid showing results over a certain number, you'll be forced to use a subquery like this:

@users = User.where(id: User.user_search(params[:name], params[:gender]).limit(30).map(&:id)).paginate(page: params[:page], per_page: 20)

However, this might cause issues if instead of 30 you use a very large number.

---

Previously I was suggesting using ActiveRecord::QueryMethods.limit, on the search result, but will_paginate overwrites this logic when paginating.

---

Related Topics