Count for grouped queries are not correct #331

Open
homerlex opened this Issue Aug 12, 2013 · 5 comments

Comments

Projects
None yet
3 participants

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]

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.

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 commented Mar 14, 2014

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

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

noniq commented Mar 15, 2014

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment