Pagy does not allow using custom selects

[bobbytables]

I'm using this activerecord query:

r = scope.incidents.select('incidents.*, array_position(ARRAY[0, 1], id) AS priority')
r.reorder('priority asc', created_at: :asc).group('incidents.id')

This fails when pagy attempts to get the count for the incidents table though because it includes the order statement but not the custom select:

Query generated by Pagy:

SELECT COUNT(*) AS count_all, incidents.id AS incidents_id FROM "incidents" WHERE "incidents"."organization_id" = $1 GROUP BY incidents.id ORDER BY priority asc, "incidents"."created_at" ASC

IMO order shouldn't matter for a count call right? Probably should just do reorder(nil) on the count call in pagy?

[ddnexus]

As you said, that is a custom select that needs a custom way to get the count (which pagy cannot know).

You have a few options to deal with that. The simples is getting your custom count and pass it as a variable:

your_scope = ...
your_count = ...
@pagy, @records = pagy(your_scope, count: your_count)

If you have to repeat the same algorithm in many actions, you may consider extracting it as a method, or you can even override the pagy_get_vars method in your controller, and change the way it gets the count.

I will add a section in the How to page. Thanks.

[bobbytables]

Great, thank you!