will_paginate reporting too many entries and pages

Question

I'm using will_paginate to display data returned from a query that includes both a joins and a select statement. When I paginate the data the number of entries is equal to the number of entries before executing the select statement, even though paginate is being called after the query, and the query contains fewer elements than paginate reports.

@sales = Sale.joins(:line_items).where(company_id: company_id, status: ['Complete', 'Voided'], time: (midnight_1..midnight_2)).order('id DESC')
puts @sales.length

14

@sales = @sales.select('distinct sales.*')
puts @sales.length

4

@sales.paginate(:per_page => 4, :page => params[page])
puts @sales.total_entries

14

This leads to displaying links to empty pages.

Burdick

Answer 1

It's always going to be slightly harder to paginate and join in has_many or has_and_belongs_to_many associations with will_paginate, or indeed any pagination solution.

If you don't need to query on the joined in association you can remove it. You lose the benefit of getting the associated line items in one query but you don't lose that much.

If you need to query on it, and presumably you want sales that only have line items, you'll need to pass in a :count option to the paginate call which specifies additional options that are used for the call to count how many items there are. In your case:

@sales.paginate(:per_page => 4, 
    :page => params[page], 
    :count => {:group => 'sales.id' })

Wingate

This :count => {:group option is exactly what we were looking for! Thanks :) Is this in the docs somewhere? – Spellbind

There's some docs here: rubydoc.info/gems/will_paginate/2.3.16/WillPaginate/Finder/… Those are for the last 2.x version of will_paginate, I can't find any explicit docs for the count option in 3.x but it looks like it is still valid. – Wingate

@Shadwell, the docs you referenced truly helped me even though your answer was not quite specific to what I needed. I ended up using the :total_entries option because both ActiveRecord and will_paginate have the same bug when applying a count to an AR query that has an aliased select, producing invalid sql "SELECT COUNT(users.first_name as user_first_name)...", by utilizing the length method from the Array class that AR does not override, I am able to first calculate the length of my total records not utilizing sql to count, and then pass in this total to will_paginate. Thank you for the link! – Nethermost

Answer 2

Assuming that your Sale model has_many :line_items, by joining you're going to get a 'sales' entry for every related 'line_item'.

Syllogize

It does but @sales.select('distinct sales.*') removes sales with repeated ID's So the query initially returns 14 sales that are not unique, the select statement trims it down to 4, but paginate doesn't recognize that. – Burdick