Exploration SQL

Article technique aujourd’hui. Lorsque j’ai développé la page des profils de Flus, j’ai fait face à un bug qui m’a bien occupé. Pas le genre de bug très grave, mais suffisamment pénible pour que je veuille le corriger.

Contexte : un profil liste les six liens publiés le plus récemment par une personne. Pour qu’un lien soit publié, il doit faire partie d’une collection publique et ne pas être masqué. Un lien peut faire partie de plusieurs collections et une collection peut contenir plusieurs liens ; on a donc une relation « many-to-many ». La date de publication est associée à une collection ; il peut donc y avoir plusieurs dates de publication pour un même lien. Mon problème : les liens publiés dans plusieurs collections pouvaient apparaître plusieurs fois.

Je vais vous illustrer tout ça par des requêtes SQL. Pour simplifier le problème, on va considérer que toutes les collections sont publiques et les liens tous visibles.

CREATE TABLE links (
    id SERIAL PRIMARY KEY,
    url TEXT NOT NULL
);

CREATE TABLE collections (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE links_to_collections (
    link_id INTEGER NOT NULL REFERENCES links,
    collection_id INTEGER NOT NULL REFERENCES collections,
    published_at TIMESTAMP NOT NULL
);

INSERT INTO links (url)
VALUES ('flus.fr'),
       ('marienfressinaud.fr'),
       ('framasoft.org');

INSERT INTO collections (name)
VALUES ('Mes favoris'),
       ('Mes partages');

INSERT INTO links_to_collections (link_id, collection_id, published_at)
VALUES (1, 1, '2022-01-17'),
       (2, 2, '2022-01-18'),
       (3, 2, '2022-01-19'),
       (1, 2, '2022-01-20');

Que fait-on dans ces quelques lignes de SQL ? On commence par créer les trois tables qui vont contenir les données (links, collections et la table faisant la jonction links_to_collections). On insère ensuite trois liens et deux collections. Enfin, on fait le lien entre les liens et les collections :

1. flus.fr dans « Mes favoris » le 17 janvier ;
2. marienfressinaud.fr dans « Mes partages » le 18 janvier ;
3. framasoft.org dans « Mes partages » le 19 janvier ;
4. enfin, à nouveau flus.fr dans « Mes partages » le 20 janvier.

Commençons par récupérer les trois liens les plus récents avec leur date de publication :

SELECT l.id, l.url, lc.published_at
FROM links l

INNER JOIN links_to_collections lc
ON lc.link_id = l.id

ORDER BY lc.published_at DESC, l.id
LIMIT 3;

Cette requête nous retourne le résultat suivant :

id	url	published_at
1	flus.fr	2022-01-20
3	framasoft.org	2022-01-19
2	marienfressinaud.fr	2022-01-18

On a donc les lignes que l’on souhaite. Seulement, qu’arrive-t-il si on change la limite de trois à six ?

id	url	published_at
1	flus.fr	2022-01-20
3	framasoft.org	2022-01-19
2	marienfressinaud.fr	2022-01-18
1	flus.fr	2022-01-17

Mince, flus.fr apparaît désormais deux fois car il se trouve dans deux collections, avec deux dates de publication différentes. On ne le veut qu’une seule fois à sa date la plus récente ! Essayons d’ajouter une clause DISTINCT :

SELECT DISTINCT l.id, l.url, lc.published_at
FROM links l

INNER JOIN links_to_collections lc
ON lc.link_id = l.id

ORDER BY lc.published_at DESC, l.id
LIMIT 6;

id	url	published_at
1	flus.fr	2022-01-20
3	framasoft.org	2022-01-19
2	marienfressinaud.fr	2022-01-18
1	flus.fr	2022-01-17

Même résultat : normal, DISTINCT s’applique à toutes les colonnes de la sélection, published_at inclus. Heureusement, PostgreSQL propose une autre syntaxe :

SELECT DISTINCT ON (l.id) l.id, l.url, lc.published_at
FROM links l

INNER JOIN links_to_collections lc
ON lc.link_id = l.id

ORDER BY lc.published_at DESC, l.id
LIMIT 6;

Aïe, cette fois on obtient une erreur : « ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions ». Elle indique que la colonne du DISTINCT ON (l.id) doit se trouver à gauche dans la liste de l’ORDER BY. Mais si l’id se trouve à gauche, nos liens ne seront plus triés dans le bon ordre !

À ce niveau-là, j’arrive au bout de ma créativité en termes de SQL et il me faut faire appel à StackOverflow. Heureusement, je ne suis pas le premier à m’être posé la question. La piste était bonne¹, mais il nous faut maintenant déplacer tout ça dans une sous-requête qui sera triée à nouveau :

SELECT l2.* FROM (
    SELECT DISTINCT ON (l.id) l.id, l.url, lc.published_at
    FROM links l

    INNER JOIN links_to_collections lc
    ON lc.link_id = l.id

    ORDER BY l.id, lc.published_at DESC
) l2

ORDER BY l2.published_at DESC, l2.id
LIMIT 6;

Et…

id	url	published_at
1	flus.fr	2022-01-20
3	framasoft.org	2022-01-19
2	marienfressinaud.fr	2022-01-18

Tadam ! C’est ce qu’il nous fallait !

Maintenant ajoutez à ça toute la complexité d’un vrai système, avec le calcul du nombre de commentaires par lien, l’état de lecture pour l’utilisateur courant, d’autres trucs à calculer dynamiquement, etc. et vous obtenez une requête des plus agréables (non). N’hésitez pas à me faire savoir si vous avez une solution plus élégante ! 😄

---

Mise à jour du 12 février.

Heureusement, j’ai des copains plus malins que moi et qui apprécient particulièrement le SQL. Merci à François pour cette solution bien meilleure à base de MAX — pour récupérer le published_at le plus récent — et de GROUP BY, tout simplement.

SELECT l.id, l.url, MAX(lc.published_at) AS published_at
FROM links l

INNER JOIN links_to_collections lc
ON lc.link_id = l.id

GROUP BY l.id
ORDER BY published_at DESC, l.id
LIMIT 6;