Paginator sqlalchemy cu window function

Limbaje de Programare
,
1

Mi-a fost adus în atenție faptul că unele ORM-uri includ metode de paginare ce se bazează pe OFFSET, lucru ce poate fi problematic. Cu cât tabela este mai mare cu atât paginarea se face mai lent din cauza faptului că rândurile sărite sunt totuși citite.

Încerc să folosesc sqlalchemy pentru a implementa o metodă alternativă OFFSET-ului. Această metodă se bazează pe window function și chiar am găsit un exemplu de implementare: WindowedRangeQuery

Urmând acest exemplu:

q = self.q.session.query(
       self.column,
       func.row_number().over(order_by=self.column).label('rownum'),
).from_self(self.column)

se genereaza următorul query:

SELECT anon_1.playlists_id AS anon_1_playlists_id 
FROM (SELECT playlists.id AS playlists_id, row_number() OVER (ORDER BY playlists.id) AS rownum 
FROM playlists) AS anon_1 
WHERE rownum %% 8=1

Însă am nevoie ca cel de-al doilea FROM să se facă dintr-un join, practic reprezentarea acestui query:

SELECT anon_1.playlist_id, 
FROM (SELECT playlists.id as playlist_id, row_number() OVER (ORDER BY playlists.id) AS rownum
FROM users_playlists, users, playlists WHERE playlists.id = users_playlists.playlist_id AND users.id = users_playlists.user_id AND users.email = '[email protected]'
)AS anon_1 
WHERE rownum % 8=1;

îmi bat capul de ceva vreme și nu reușesc să folosesc ORM-ul pentru a obține acest query. Careva dintre voi are experiență cu sqlalchemy? :smiley:

notă adițională: folosesc Postgres iar relația FROM users_playlists, users, playlists este un many to many bidirecțional

1 Like
2

Pentru viitor, un query cu window function dintr-un join poate fi generat prin orm astfel:

query = self.db_session.query(
  self.id_column.label('item_id'),
  func.row_number().over(order_by=self.id_column).label('rownum')
  ).join(self.relation_on).filter(self.join_over_column == self.join_condition)\
  .from_self(self.id_column)\
  .filter(text("rownum %% %d=1" % self.per_page))

Problema mea era că nu foloseam metoda.label() pentru coloane iar ca efect query-ul generat era incomplet. Sqlalchemy este super flexibil și îmi pare rău că nu am început din start să îl folosesc (am început cu Flask-Sqlalchemy).

3

Chiar acum lucrez la crearea modelelor pt un proiect cu Flask si sqlalchemy. I’ll keep this in mind. :slight_smile: