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.

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' })

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'.