when using fast_page with windowing functions, it repeats all the complex SQL

[luizkowalski]

I think this is intentional to be honest, but I would like to know if there is something I can do about it.

Here is my situation: in our application, a user can have many locales, and locales can have a display code, that can be duplicated, e.g. user can have many locales with display code en-US.

I'm using a window function to fetch only unique locales:

Locale.with(
  unique_locales:
    Locale.select(
      '*, ROW_NUMBER() OVER (PARTITION BY display_code ORDER BY id) as rn'
    ).where(space: organization_spaces)
).joins('JOIN unique_locales rl ON rl.id = locales.id').where('rn = 1')

If I try to use fast_page to paginate these results, it ends up issuing two queries like this:

  Locale Pluck (2.6ms)  WITH `unique_locales` AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY display_code ORDER BY id) as rn FROM `locales` WHERE `locales`.`deleted_at` IS NULL AND `locales`.`space_key` IN (SELECT `spaces`.`key` FROM `spaces` WHERE `spaces`.`deleted_at` IS NULL AND `spaces`.`organization_id` = 15504)) SELECT `locales`.`id` FROM `locales` JOIN unique_locales rl ON rl.id = locales.id WHERE `locales`.`deleted_at` IS NULL AND (rn = 1) LIMIT 11 OFFSET 0


  Locale Load (1.9ms)  WITH `unique_locales` AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY display_code ORDER BY id) as rn FROM `locales` WHERE `locales`.`deleted_at` IS NULL AND `locales`.`space_key` IN (SELECT `spaces`.`key` FROM `spaces` WHERE `spaces`.`deleted_at` IS NULL AND `spaces`.`organization_id` = 15504)) SELECT `locales`.* FROM `locales` JOIN unique_locales rl ON rl.id = locales.id WHERE `locales`.`deleted_at` IS NULL AND (rn = 1) AND `locales`.`id` IN (15136, 15146, 15138, 15140)

Ideally, the second query would go straight to Locale. I get that this is by design but I wonder if I can do something to change this behavior