Pagination

La pagination

Je cherches juste ce qu’il me faut, pas plus

Nous sommes faignants ! Il est trop facile de simplement exécuter :

select * from action;

... et ensuite effectuer une logique de tri ou de filtrage coté application (php, nodejs) etc.

Mais, dans notre base, il y a 32334 interventions dans la table action ! Cela peut créer des très grands problèmes en production :

• La bande passante entre notre SGBDR et notre API risque d'être saturée

• Si le SGBDR n'est pas sur le même réseau de notre API, on risque d'être facturé pour le transfert de ces données

• On risque de saturer la mémoire de notre API en php ou nodejs. Le système d'exploitation pourrait arrêter le processus afin de ne pas planter !

• Si les données arrivées coté front : on risque de figer le navigateur pendant longtemps, saturer la RAM de notre utilisateur. Et, en plus, l'expérience utilisateur est très dégradée ! Il ne peut pas consommer 32k lignes, de toute façon, donc on ne va afficher qu'une dizaine de lignes dans tous les cas !

Donc, on utilise les clauses limit afin de limiter la quantité de lignes retournée par le SGBDR, et offset dans le but de récupérer des blocs différents de données : un processus qu'on appelle la pagination.

On a déjà vu la pagination partout sur le web, avec l'interface typique suivante :

Exemple de la pagination

Ici, on n'affiche que 10 lignes d'une requête qui devrait normalement retourner 1137 lignes. On peut avancer page par page en utilisant l'opérateur offset.

Les clauses limit et offset

Testons !

select familyName, givenName
from user
order by familyName, givenName
limit 10
offset 0;

Ici, on retourne la première page de 10 lignes de la table user. On peut procéder à la page suivante en augmentant le offset par le nombre de lignes déjà vues :

select userId, familyName, givenName
from user
order by familyName, givenName
limit 10
offset 10;

Testez de votre côté. Essayez avec offset 9 ? Vous avez compris le fonctionnement de offset ?

Chaque « page » est donc définie par une limit et un offset :

• La taille de la page = limit

• le offset pour la page X = limi * X

À noter, la première page commence à zéro pour que les math fonctionnent !

Exemple, pour des pages de taille 5 :

• Page 1: $offset = 5 * 0 = 0$

• Page 2: $offset = 5 * 1 = 5$

• Page 5: $offset = 5 * 4 = 20$

La clause order by

Bien sûr, déterminer la bonne page dépend de l’ordre des données retournées. Il est donc essentiel de préciser l’ordre quand on fait de la pagination avec la clause order by.

Dans l'exemple suivant, on trie par le nom de famille, ensuite pas le prénom de chaque utilisateur :

select familyName, givenName
from user
order by familyName, givenName
limit 10
offset 0;

Par défaut, le tri se fait dans l'ordre croissant. On ajoute desc après le nom de l’attribut afin de préciser un tri décroissant.

Essayez de toujours spécifier l’ordre de vos requêtes ou en ajoutez par défaut dans vos APIs.

• Les données auront l’air plus « stables » d’un point de vue utilisateur.

• Les tests automatiques seront plus stables aussi !

Nombre total de pages

Pour l'ergonomie d'une interface front, on aimerait afficher les pages possibles (comme dans l’exemple). Il est facile de le calculer :

$pages = ceil(nombre de lignes / taille de la page)$

Par exemple, si on a 21 lignes, avec une limit de 5, il y aura 5 pages en total. Pour la dernière page, il y a < 5 résultats, mais celle là compte comme une page, donc on arrondit vers le haut avec ceil.

On peut l'exprimer en SQL aussi :

select ceil(count(*) / 5) as pages
from user;

Traitement des données

Pour le traitement de données, il est toujours préférable de traiter les données par petit morceau :

• On évite de saturer la RAM du SGBDR

• On évite de saturer la bande passante entre le SGBDR et le client

• On évite de saturer la RAM du client (php, node, navigateur)

La pagination est une bonne solution !

Imaginez que vous écrivez un script qui doit passer par chaque intervention et mettre à jour son budget, en ajoutant 6% pour compenser l'inflation. Il y a deux solutions :

• Soit on fait simplement select actionId, budget from action; et puis on traite le tout dans la RAM de notre ordinateur. Le problème est quand il y a des centaines de milliards de lignes, cela devient plus faisable !

• Soit, on passe progressivement sur les données, page par page avec select actionId, budget from action order by actionId limit 100 offset 0;. On récupère 100 lignes à la fois. À chaque itération, on augmente le offset par 100, jusqu'à le select ne retourne plus de résultats.

Un autre solution est l’utilisation des cursor ... je vous laisse vous renseigner sur le sujet !

Exercices

Les exercices suivants sont basés sur la base de données SAKILA, une base de données de test fourni par MySQL.

Récupérez la base SAKILA ici.

Question 1

Affichez la liste de films moins chers que 2€, en ordre croissant de prix. Afficher la 5ème page, si on utilise des pages de taille 15.

Solution

Résultat : 15 lignes

select title, rental_rate 
from film 
where 
  rental_rate < 2
  order by rental_rate, title
  limit 15
  offset 75;

Question 2

Affichez les genres du film qui ont plus que 50 films, triés par le nombre de films décroissant.

Solution

Résultat : 16 lignes

select 
  c.category_id, 
  c.name, 
  count(f.film_id) as count
from category as c
inner join film_category as fc
  on c.category_id = fc.category_id
inner join film as f
  on f.film_id = fc.film_id
group by c.category_id, c.name
order by count desc;