# Pagination

## La pagination

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

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

```sql
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 :

<figure><img src="https://2009031458-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FRDoAPzYMcmOsCu8dxxWU%2Fuploads%2Fgit-blob-14367bb856ec2a58a4b438d2c19ef87c012246ee%2Fpagination.png?alt=media" alt=""><figcaption><p>Exemple de la pagination</p></figcaption></figure>

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 !

```sql
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 :

```sql
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 :

```sql
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.

{% hint style="success" %}
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 !
  {% endhint %}

### 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 :

```sql
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.](https://dev.glassworks.tech:18081/courses/sgbdr/sgbdr-supports/-/tree/main/src/samples/sakila)

### 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.

<details>

<summary>Solution</summary>

Résultat : 15 lignes

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

</details>

### Question 2

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

<details>

<summary>Solution</summary>

Résultat : 16 lignes

```sql
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;

```

</details>