Count for grouped queries are not correct

[homerlex]

Rails 3.2.13, will_paginate 3.0.4

When I have a query that does grouping the result count coming from will_paginate is incorrect due to the way the query is built up for counts.

I'm thinking this may possibly be an AREL or ActiveRecord problem but I bring it up here because I would think that the problem would have been experienced here and I'm wondering if there are potentially any fixes or workarounds.

I found that if I replace the rel.count call in the WillPaginate::ActiveRecord::RelationMethods count method with rel.all.count I get the proper count.

Does anyone have any thoughts on this issue?

The AREL I have that duplicates this issue look something like:

records = MyModel.select('sum(something)').group[:user_id, :category_id]

[ZenCocoon]

Same here with the query:

SELECT  "activities".* FROM "activities" INNER JOIN "activity_orders" ON "activity_orders"."activity_id" = "activities"."id" WHERE "activities"."destination_id" IN (45) AND "activities"."published" = 't' GROUP BY activities.id, activity_orders.id, activity_orders.position  ORDER BY "activity_orders"."position" ASC LIMIT 9 OFFSET 0

count is being used to calculate the total_entries while I believe it should have relied on size.

Note: A temporary solution that I use, is to force total_entries in the paginate call.

[homerlex]

Just curious why this has not been fixed. Is there something more complicated than what I did as a workaround in the count method (as described in my first comment).

[noniq]

Using relation.all.count does work, but it will load the complete result set into memory. This is something you normally want to avoid.

[homerlex]

So, what would be a proper, more performant fix to will_paginate?

[noniq]

I don’t think it is fixable – ActiveRecord will always do a “count per group” if the query contains a group statement (see rails/rails#7121 and similar issues).

My workaround in a similar case recently: I did a second, manually crafted SQL query to get the correct total number of rows, then I used WillPaginate::Collection.create to create the paginated collection (see http://rdoc.info/gems/will_paginate/WillPaginate/Collection.create for an example).