Paginacja, filtrowanie, sortowanie wraz z inner join

Mam takie 2 tabele:

CREATE TABLE account (
  id INT
);

CREATE TABLE device (
  id INT,
  account_id INT,
  token text
);

W ten sposób mogę wyświetlić konta wraz z ich wszystkimi urządzeniami:

select *
from account a
inner join device d on a.id = d.account_id

https://www.db-fiddle.com/f/vZCNfgvppXhmnz8sHbaWda/15

Nie ma też problemu, by wprowadzić paginację:

select *
from 
(
  select *
  from account a
  order by a.id
  offset 0 rows
  fetch first 3 rows only
) a
inner join device d on d.account_id = a.id

https://www.db-fiddle.com/f/vZCNfgvppXhmnz8sHbaWda/14

Mogę też dodać filtrowanie - wyświetlać konta (wraz z ich wszystkimi urządzeniami), ale tylko wtedy, gdy zawierają urządzenie z tokenem 'BIZ':

select *
from 
(
  select *
  from account a
  where exists (select 1 from device d where d.account_id = a.id and d.token like 'BIZ%')
  order by a.id
  offset 0 rows
  fetch first 3 rows only
) a
inner join device d on d.account_id = a.id

https://www.db-fiddle.com/f/vZCNfgvppXhmnz8sHbaWda/13

Teraz chciałbym dodać coś szalonego - do powyższego zapytania dodać sortowanie po tokenie. Dla paginacji:

offset 0 rows
fetch first 3 rows only

i sortowaniu rosnąco powinienem otrzymać:

| account_id | token   |
|------------|---------|
| 1          | BIZ-111 |
| 1          | BIZ-333 |
| 1          | ONZ-555 |
| 4          | BIZ-222 |
| 8          | BIZ-444 |

a przy sortowaniu malejąco:

| account_id | token   |
|------------|---------|
| 3          | BIZ-999 |
| 7          | BIZ-888 |
| 2          | BIZ-777 |
| 2          | BIZ-666 |

Niestety polegam na tym tworząc coraz to większe spahetti. Aktualnie mam coś takiego:
Dla sortowania rosnąco:

with cte as
(
  select
    a.id
    from account a
  inner join device d on d.account_id = a.id
  where d.token like 'BIZ%'
  group by a.id
  order by max(d.token) asc
  offset 0 rows
  fetch first 3 rows only
)

select
  cte.id,
  d.token
from cte
inner join device d on d.account_id = cte.id
order by d.token asc

https://www.db-fiddle.com/f/vZCNfgvppXhmnz8sHbaWda/17

I malejąco:

with cte as
(
  select
    a.id
    from account a
  inner join device d on d.account_id = a.id
  where d.token like 'BIZ%'
  group by a.id
  order by max(d.token) desc -- tutaj zamiana z 'asc' na 'desc'
  offset 0 rows
  fetch first 3 rows only
)

select
  cte.id,
  d.token
from cte
inner join device d on d.account_id = cte.id
order by d.token desc -- tutaj zamiana z 'asc' na 'desc'

https://www.db-fiddle.com/f/vZCNfgvppXhmnz8sHbaWda/16

Co prawda kolejność kont nie jest dokładnie tak jak chciałem, ale ja i tak potem muszę grupować wynik zapytania, a kolejność względem kont, jak i tokenów względem jednego konta jest już zachowana.

Macie propozycje, czy da się to zrobić zwięźlej? Generalnie problem polega na tym, że chciałbym dodać sortowanie po innych rzeczach niż token i robi się straszne spaghetti.. Mam to rozwiązane (chyba niestety) w taki sposób, że w zależności co zaznaczy użytkownik (filtrowanie, sortowanie), to edytuję ten kod sql.. Nie chcecie tego wiedzieć..

A to ma jakiś front czy wszystko to usz w bazie? Po co jest ten with? Jak masz problem z paginacją i sortowaniem to poczytaj o funkcji row number.

Po pierwsze: żaden to tasiemiec
Po drugie: skoro masz gdzieś jakiś kod, który w zależności od potrzeby (akcji użytkownika) generuje różne zapytania, to w czym problem?
Po trzecie: Jak już @Adin wspomniał, row_number() zapewne Ci się spodoba.

Polecam key set pagination jako, że to industry standard https://www.cockroachlabs.com/docs/stable/pagination . IMO jest to prostsze koncepcyjnie, do tego działa szybciej i dużo lepiej rozwiązuje problem a co jak w międzyczasie ktoś coś zmienił w przeszukiwanym zbiorze

@Adin Tak, front strzela do backendu, backend strzela do bazy, a otrzymane dane jeszcze musi zgrupować po account_id. Ten WITH to tak dla czytelności, żeby nie robić zagnieżdzonych select

@slsy limit i offset mi się nie sprawdziło, bo np. gdy jest 1 użytkownik z 3 urządzeniami, a ja chcę uzyskać 3 użytkowników, to baza mi zwróci 1 użytkownika z 3 urządzeniami:

select *
from account a
inner join device d on d.account_id = a.id
limit 3 offset 0;

Dlatego robię to tak:

select *
from 
(
  select *
  from account a
  order by a.id
  offset 0 rows
  fetch first 3 rows only
) a
inner join device d on d.account_id = a.id

Mylne jest w tym słowo paginacja, bo z reguły dzielimy na równe paczki, a u ciebie ilość będzie zależeć o tabeli device, ale w teori możesz zwrócić pustą "stronę", jeżeli dla account nie będziesz miał żadnego wpisu w device

Skoro tak to ja bym to zrobił joinem (cte tylko dla czytelności)

with cte as
(
  select
    a.id as pageids
  from account a
  order by a.id
  offset 0 rows
  fetch first 3 rows only
)

select *
from
    account a
    --join tylko aby ponrać odpowiednią stronę
    inner join cte on cte.pageids = a.id
    inner join devices d on d.account_id = a.id
WHERE
    --tu jakie chcesz warunki
ORDER BY
    --tu jakie chcesz sortowanie