db
tri

Après le contenu triable dans une seule table, nous allons nous intéresser maintenant au contenu triable en tenant compte des liaisons possibles avec d’autres tables. Par exemple le classement des meilleurs joueurs au sein de leur équipe.



11 min.

Partager l’article Trier avec PostgreSQL, partie 2 sur les réseaux sociaux

Vous lisez actuellement la suite de Trier avec PostgreSQL, partie 1, et donc de nombreuses références concerneront la première partie.

Prérequis

Les prérequis sont les mêmes que pour la première partie. Nous allons reprendre la même table et en ajouter d’autres.

L’exemple

À la table persons définie dans la première partie de cette série d’articles, nous ajoutons la table teams pour définir les équipes et nous la remplissons avec quelques données. 

CREATE TABLE teams (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL  CHECK(name ~ '[[:alpha:]]+')
);

INSERT INTO teams (name) VALUES ('Les Rouges'),('Les Bleus');

Créons ensuite la table de liaison players. Une équipe ne peut pas avoir deux fois la même personne. Nous allons y adjoindre un champ sort qui nous servira pour le tri.

CREATE TABLE players (
    team_id     INTEGER REFERENCES teams(id),
    person_id   INTEGER REFERENCES persons(id),
    sort        INTEGER NOT NULL DEFAULT 0
);

Pour le côté pratique, une petite vue SQL pour afficher les joueurs dans l’ordre de leurs équipes.

CREATE VIEW v_players AS
    SELECT      pl.team_id,
                tm.name AS team,
                pl.person_id,
                pn.firstname || ' ' || UPPER(pn.lastname) AS player,
                pl.sort AS rank_in_team
    FROM        players AS pl
    LEFT JOIN   persons AS pn   ON pl.person_id = pn.id
    LEFT JOIN   teams AS tm     ON pl.team_id = tm.id
    ORDER BY    tm.name ASC,
                pl.sort ASC
    ;

Avoir un tri par défaut dès l’insertion

Vous l’aurez compris, la table sur laquelle notre attention va particulièrement se porter sera la table de liaison players.

Nous allons reprendre l’idée du trigger de la première partie pour l’adapter à ce genre de cas. Nous l’appellerons cette fois-ci put_at_last_into_container.

CREATE OR REPLACE FUNCTION put_at_last_into_container() RETURNS trigger AS $$
DECLARE
    query TEXT;
    found INTEGER;
BEGIN
    IF TG_OP = 'INSERT' THEN
        query := 'SELECT COALESCE(MAX(sort), 0) + 1 FROM ' || quote_ident(TG_TABLE_NAME);
        query := query || ' WHERE '|| quote_ident(TG_ARGV[0] :: text) ||' = $1.' ||TG_ARGV[0] :: text;
        
        EXECUTE query USING NEW INTO found;

        NEW.sort := found;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

La différence par rapport à la version table seule, c’est qu’ici nous devons spécifier pour quel champ et quelle valeur de ce champs le classement est valable. Dans notre exemple, ce sera le champ team_id, puisque c’est au sein d’une équipe qu’on effectura le classement. Il faut aussi manœuvrer quelque peu pour prendre en compte la valeur à partir de NEW.

Appliquons ce trigger à la table players :

CREATE TRIGGER put_at_last
    BEFORE INSERT ON players
    FOR EACH ROW EXECUTE PROCEDURE put_at_last_into_container('team_id');

Et testons en peuplant cette table de liaison :

INSERT INTO players(team_id, person_id)
VALUES      (1,1),
            (1,2),
            (1,3),
            (1,4),
            (2,5),
            (2,6),
            (2,7)
;

Observons son contenu :

SELECT * FROM v_players;

La sortie est :

 team_id |    team    | person_id |       player        | rank_in_team 
---------+------------+-----------+---------------------+--------------
       2 | Les Bleus  |         5 | Sylvie LEMAIRE      |            1
       2 | Les Bleus  |         6 | Julien NORMAND      |            2
       2 | Les Bleus  |         7 | Antoine PASQUIER    |            3
       1 | Les Rouges |         1 | Laetitia LAUNAY     |            1
       1 | Les Rouges |         2 | Suzanne POISSONNIER |            2
       1 | Les Rouges |         3 | Eugène PAYET        |            3
       1 | Les Rouges |         4 | Juliette GERMAIN    |            4
(7 rows)

Mission accomplie ! Le classement par défaut lors de l’insertion des données fonctionne bien par équipe !

Puisque nous nous sommes bien échauffés, revoyons la requête permettant de connaître les positions relatives vue dans la première partie…

La fonction générique permettant de connaître la position relative de chaque item au sein d’un conteneur

Les nouveaux éléments à prendre en compte par rapport à la version simple, c’est que maintenant nous devons connaitre le champ conteneur (ici team_id) et le champ des items de ce conteneur (ici, person_id). Il faut donc au moins deux arguments supplémentaires à la fonction existante.

Ensuite il suffira de reprendre la construction de la requête pour inclure ces nouvelles variables ainsi que l’ajout de partitionnement par rapport au champ conteneur pour les fonctions de fenêtrage.

Cette nouvelle fonction, dédiée au tri dans des tables de liaison, va s’appeler prev_next_in_container_helper.

Voici ce que cela donne :

CREATE OR REPLACE FUNCTION prev_next_in_container_helper (
    IN arg_table_name       TEXT,
    IN arg_container_field  TEXT,
    IN arg_item_field       TEXT
)
RETURNS TABLE (
    container_id    INTEGER,
    item_id         INTEGER,
    sort            INTEGER,
    prev_item       INTEGER,
    next_item       INTEGER,
    prev_sort       INTEGER,
    next_sort       INTEGER
) 
AS $$
DECLARE
    query TEXT;
BEGIN
    query := 'WITH cte AS (
        SELECT      '|| quote_ident(arg_container_field) || ',
                    '|| quote_ident(arg_item_field) || ',
                    sort  
        FROM        '|| quote_ident(arg_table_name) || '
        ORDER BY    sort ASC
    )
    SELECT 
        *,

        LAG (' || quote_ident(arg_item_field) || ', 1)
        OVER (
            PARTITION BY '|| quote_ident(arg_container_field) || ' 
            ORDER BY sort ASC
        ) AS prev_item,

        LEAD (' || quote_ident(arg_item_field) || ', 1) 
        OVER (
            PARTITION BY '|| quote_ident(arg_container_field) || ' 
            ORDER BY sort ASC
        ) AS next_item,

        LAG (sort, 1)
        OVER (
            PARTITION BY '|| quote_ident(arg_container_field) || ' 
            ORDER BY sort ASC
        ) AS prev_sort,

        LEAD (sort, 1)
        OVER (
            PARTITION BY '|| quote_ident(arg_container_field) || ' 
            ORDER BY sort ASC
        ) AS next_sort

    FROM cte;
    ';

	RETURN QUERY EXECUTE query;
END; $$ 

LANGUAGE 'plpgsql';

Et testons-la avec notre nouvelle table !

SELECT * FROM prev_next_in_container_helper('players', 'team_id', 'person_id');

La sortie donne :

 container_id | item_id | sort | prev_item | next_item | prev_sort | next_sort 
--------------+---------+------+-----------+-----------+-----------+-----------
            1 |       1 |    1 |           |         2 |           |         2
            1 |       2 |    2 |         1 |         3 |         1 |         3
            1 |       3 |    3 |         2 |         4 |         2 |         4
            1 |       4 |    4 |         3 |           |         3 |          
            2 |       5 |    1 |           |         6 |           |         2
            2 |       6 |    2 |         5 |         7 |         1 |         3
            2 |       7 |    3 |         6 |           |         2 |          
(7 rows)

Et c’est exactement l’effet recherché !

Déplacement d’un seul élément

Nous avons les bases, reprenons la fonction nous permettant de déplacer un élément de rang en rang, vue dans la première partie : move_simple.

Appelons la nouvelle version dédiée au classement par conteneur tout simplement move_in_container.

Déjà, première adaptation, la signature de la fonction sera plus fournie. Comme vu précédemment, nous avons besoin de connaître le champ qui sert de conteneur, le champ déterminant les items dans ce conteneur, ainsi que l’ID du conteneur concerné. Le reste de la signature d’origine est conservé.

Les variables déclarées au sein de la fonction seront les mêmes que celles dans la fonction d’origine.

Nous avons donc ceci :

CREATE OR REPLACE FUNCTION move_in_container(
    IN arg_way              TEXT,
    IN arg_table_name       TEXT,
    IN arg_container_field  TEXT,
    IN arg_item_field       TEXT,
    IN arg_container_id     INTEGER,
    IN arg_item_id          INTEGER
)
RETURNS INTEGER AS $$
DECLARE
    v_min           INTEGER;
    v_max           INTEGER;
    v_sort_curr     INTEGER;
    query_curr      TEXT;
    query_other     TEXT;
    v_sort_other    INTEGER DEFAULT 0;
    v_id_other      INTEGER;
BEGIN
    -- Code à venir…

    RETURN v_sort_other;
END;
$$ LANGUAGE plpgsql;

Les garde-fou, la sélection des valeurs minimale et maximale, la valeur de la position actuelle, sont quasi identiques, il faut juste adapter les requêtes pour limiter le résultat à ce qui est inclus dans le conteneur et utiliser notre nouvelle fonction créée avant bien sûr.

CREATE OR REPLACE FUNCTION move_in_container(
    IN arg_way              TEXT,
    IN arg_table_name       TEXT,
    IN arg_container_field  TEXT,
    IN arg_item_field       TEXT,
    IN arg_container_id     INTEGER,
    IN arg_item_id          INTEGER
)
RETURNS INTEGER AS $$
DECLARE
    v_min           INTEGER;
    v_max           INTEGER;
    v_sort_curr     INTEGER;
    query_curr      TEXT;
    query_other     TEXT;
    v_sort_other    INTEGER DEFAULT 0;
    v_id_other      INTEGER;
BEGIN
    IF  NOT(arg_way = 'down' OR arg_way = 'up')
    THEN
        RAISE EXCEPTION 'You must select way as `up` or `down` only.';
    END IF;


    -- Il faut adapter pour utiliser la nouvelle fonction
    -- et tenir compte du contenur
    SELECT  MIN(sort), MAX(sort)
    INTO    v_min, v_max
    FROM    prev_next_in_container_helper(
                arg_table_name,
                arg_container_field,
                arg_item_field
            )
    WHERE   container_id = arg_container_id;

    -- Il faut adapter pour utiliser la nouvelle fonction
    -- et tenir compte du contenur
    SELECT  sort
    INTO    v_sort_curr
    FROM    prev_next_in_container_helper(
                arg_table_name,
                arg_container_field,
                arg_item_field
            )
    WHERE   item_id = arg_item_id
            AND
            container_id = arg_container_id;

    IF  (v_min = v_sort_curr AND arg_way = 'up')
        OR
        (v_max = v_sort_curr AND arg_way = 'down')
        THEN
        RAISE EXCEPTION 'You cannot up or down more this item.';
    END IF;

    -- Code à venir…

    RETURN v_sort_other;
END;
$$ LANGUAGE plpgsql;

L’adaptation du code effectuant le changement du classement sera du même acabit :

CREATE OR REPLACE FUNCTION move_in_container(
    IN arg_way              TEXT,
    IN arg_table_name       TEXT,
    IN arg_container_field  TEXT,
    IN arg_item_field       TEXT,
    IN arg_container_id     INTEGER,
    IN arg_item_id          INTEGER
)
RETURNS INTEGER AS $$
DECLARE
    v_min           INTEGER;
    v_max           INTEGER;
    v_sort_curr     INTEGER;
    query_curr      TEXT;
    query_other     TEXT;
    v_sort_other    INTEGER DEFAULT 0;
    v_id_other      INTEGER;
BEGIN
    IF  NOT(arg_way = 'down' OR arg_way = 'up')
    THEN
        RAISE EXCEPTION 'You must select way as `up` or `down` only.';
    END IF;

    SELECT  MIN(sort), MAX(sort)
    INTO    v_min, v_max
    FROM    prev_next_in_container_helper(
                arg_table_name,
                arg_container_field,
                arg_item_field
            )
    WHERE   container_id = arg_container_id;

    SELECT  sort
    INTO    v_sort_curr
    FROM    prev_next_in_container_helper(
                arg_table_name,
                arg_container_field,
                arg_item_field
            )
    WHERE   item_id = arg_item_id
            AND
            container_id = arg_container_id;

    IF  (v_min = v_sort_curr AND arg_way = 'up')
        OR
        (v_max = v_sort_curr AND arg_way = 'down')
        THEN
        RAISE EXCEPTION 'You cannot up or down more this item.';
    END IF;


    SELECT
        CASE WHEN arg_way = 'up' THEN prev_sort ELSE next_sort END,
        CASE WHEN arg_way = 'up' THEN prev_item ELSE next_item END
    INTO
        v_sort_other,
        v_id_other
    FROM
        prev_next_multiple_helper(
            arg_table_name,
            arg_container_field,
            arg_item_field
        )
    WHERE
        item_id = arg_item_id
        AND
        container_id = arg_container_id;

    query_curr := '
        UPDATE  '|| quote_ident(arg_table_name) ||'
        SET     sort = $1
        WHERE   '|| quote_ident(arg_item_field) ||' = $2
                AND
                '|| quote_ident(arg_container_field) ||' = $3;
    '
    ;
    query_other := '
        UPDATE  '|| quote_ident(arg_table_name) ||'
        SET     sort = $1
        WHERE   '|| quote_ident(arg_item_field) ||' = $2
                AND
                '|| quote_ident(arg_container_field) ||' = $3;
    '
    ;

    EXECUTE query_curr  USING v_sort_other, arg_item_id, arg_container_id;
    EXECUTE query_other USING v_sort_curr, v_id_other, arg_container_id;

    RETURN v_sort_other;
END;
$$ LANGUAGE plpgsql;

Voilà notre fonction adaptée. Il ne nous reste plus qu’à la tester. Voyons ça…

-- doit soulever une erreur car on cherche à monter un élément
-- déjà en première place dans l’équipe 1 (Les Rouges)
SELECT move_in_container('up', 'players', 'team_id', 'person_id', 1, 1);

Et en effet, la bonne erreur est levée :

ERROR:  You cannot up or down more this item.
CONTEXT:  PL/pgSQL function move_in_container(text,text,text,text,integer,integer) line 40 at RAISE
-- doit soulever une erreur également car on cherche à monter un élément
-- déjà en première place dans l’équipe 2 (Les Bleus)
SELECT move_in_container('up', 'players', 'team_id', 'person_id', 2, 5);

Une fois encore, la bonne erreur est soulevée :

ERROR:  You cannot up or down more this item.
CONTEXT:  PL/pgSQL function move_in_container(text,text,text,text,integer,integer) line 40 at RAISE

Testons les bornes dans le sens de la descente maintenant :

-- doit soulever une erreur également car on cherche à descendre un élément
-- déjà en bas du classement dans l’équipe 1 (Les Rouges)
SELECT move_in_container('down', 'players', 'team_id', 'person_id', 1, 4);

Et nous avons :

ERROR:  You cannot up or down more this item.
CONTEXT:  PL/pgSQL function move_in_container(text,text,text,text,integer,integer) line 40 at RAISE

De même pour l’équipe ayant l’ID 2 (Les Bleus) :

SELECT move_in_container('down', 'players', 'team_id', 'person_id', 2, 7);

Donne :

ERROR:  You cannot up or down more this item.
CONTEXT:  PL/pgSQL function move_in_container(text,text,text,text,integer,integer) line 40 at RAISE

Bien ! Tentons d’effectuer un déplacement autorisé. Par exemple le joueur ayant l’ID 2 de l’équipe 1 qu’on monte d’un cran :

SELECT move_in_container('up', 'players', 'team_id', 'person_id', 1, 2);

La sortie nous indique la nouvelle position du joueur :

 move_in_container 
-------------------
                 1
(1 row)

Vérifions :

SELECT * FROM v_players;

Donne en sortie :

 team_id |    team    | person_id |       player        | rank_in_team 
---------+------------+-----------+---------------------+--------------
       2 | Les Bleus  |         5 | Sylvie LEMAIRE      |            1
       2 | Les Bleus  |         6 | Julien NORMAND      |            2
       2 | Les Bleus  |         7 | Antoine PASQUIER    |            3
       1 | Les Rouges |         2 | Suzanne POISSONNIER |            1
       1 | Les Rouges |         1 | Laetitia LAUNAY     |            2
       1 | Les Rouges |         3 | Eugène PAYET        |            3
       1 | Les Rouges |         4 | Juliette GERMAIN    |            4
(7 rows)

C’est bon, nous avons bien adapté la fonction pour les cas avec conteneur !

Maintenons notre allure pour aborder la dernière ligne droite : changer le classement de tous les éléments d’un conteneur d’un coup.

Déplacement de tous les éléments au sein d’un conteneur

Reprenons le code de la fonction sort_simple de la première partie de cette série d’articles. Pour le cas des conteneurs, nous allons l’appeler sort_in_container.

Dans la signature, nous aurons besoin d’ajouter le nom du champ conteneur, le nom du champ identifiant un item et la valeur de l’ID du conteneur visé.

CREATE OR REPLACE FUNCTION sort_in_container(
    IN arg_table_name       TEXT,
    IN arg_container_field  TEXT,
    IN arg_item_field       TEXT,
    IN arg_container_id     INTEGER,
    IN arg_item_ids         TEXT
)
RETURNS VOID AS $$
DECLARE
    ids INTEGER[];
    i   INTEGER;
    id  INTEGER;
    nt  INTEGER;
    d   BOOLEAN;
    qn  TEXT;
    q   TEXT;
BEGIN
    ids := string_to_array(arg_item_ids, ',')::integer[];
    i   := 0;

    -- Pour les deux requêtes, on précise le conteneur…
    qn  := 'SELECT COUNT(*) FROM ' || quote_ident(arg_table_name);
    qn  := qn || ' WHERE ' || quote_ident(arg_container_field) ||' = $1';

    q   := 'UPDATE ' || quote_ident(arg_table_name);
    q   := q || ' SET sort=$1 WHERE ' || quote_ident(arg_item_field) || '=$2';
    q   := q || ' AND ' || quote_ident(arg_container_field) ||' = $3';

    EXECUTE qn INTO nt USING arg_container_id;

    IF  nt <> cardinality(ids)
    THEN
        RAISE EXCEPTION 'IDS amount must be the same as amount of elements inside the container.';
    END IF;

    WITH dq AS (
        SELECT      item
        FROM        unnest(ids) AS item
        GROUP BY    item
        HAVING      count(*) > 1
    )
    SELECT  COUNT(*) > 0
    INTO    d
    FROM    dq;

    IF  d
    THEN
        RAISE EXCEPTION 'IDS must be unique inside the container!';
    END IF;

    FOREACH id IN ARRAY ids
    LOOP
        i := i + 1;
        EXECUTE q USING i, id, arg_container_id;
    END LOOP;
    
END;
$$ LANGUAGE plpgsql;

Testons si le nombre d’IDs fournis est cohérent avec ce qu’il y a en base de données :

SELECT sort_in_container('players', 'team_id', 'person_id', 1, '3,1,2');

Le nombre d’IDs n’étant pas le bon, nous avons bien une erreur levée.

ERROR:  IDS amount must be the same as amount of elements inside the container.
CONTEXT:  PL/pgSQL function sort_in_container(text,text,text,integer,text) line 26 at RAISE

Testons également avec le bon nombre d’IDs mais avec avec des doublons

SELECT sort_in_container('players', 'team_id', 'person_id', 1, '3,1,2,2');

L’erreur levée est bien celle au sujet des doublons.

ERROR:  IDS must be unique inside the container!
CONTEXT:  PL/pgSQL function sort_in_container(text,text,text,integer,text) line 41 at RAISE

Amusons-nous à inverser le classement au sein de l’équipe des Rouges :

SELECT sort_in_container('players', 'team_id', 'person_id', 1, '4,3,2,1');

Voyons le résultat :

 team_id |    team    | person_id |       player        | rank_in_team 
---------+------------+-----------+---------------------+--------------
       2 | Les Bleus  |         5 | Sylvie LEMAIRE      |            1
       2 | Les Bleus  |         6 | Julien NORMAND      |            2
       2 | Les Bleus  |         7 | Antoine PASQUIER    |            3
       1 | Les Rouges |         4 | Juliette GERMAIN    |            1
       1 | Les Rouges |         3 | Eugène PAYET        |            2
       1 | Les Rouges |         2 | Suzanne POISSONNIER |            3
       1 | Les Rouges |         1 | Laetitia LAUNAY     |            4
(7 rows)

Et nous avons bien ce que nous voulions.

Persistons en faisant de même avec l’autre équipe…

SELECT sort_in_container('players', 'team_id', 'person_id', 2, '7,6,5');

Les Bleus ont bien été retournés :

 team_id |    team    | person_id |       player        | rank_in_team 
---------+------------+-----------+---------------------+--------------
       2 | Les Bleus  |         7 | Antoine PASQUIER    |            1
       2 | Les Bleus  |         6 | Julien NORMAND      |            2
       2 | Les Bleus  |         5 | Sylvie LEMAIRE      |            3
       1 | Les Rouges |         4 | Juliette GERMAIN    |            1
       1 | Les Rouges |         3 | Eugène PAYET        |            2
       1 | Les Rouges |         2 | Suzanne POISSONNIER |            3
       1 | Les Rouges |         1 | Laetitia LAUNAY     |            4
(7 rows)

La course étant maintenant finie, effectuons un dernier petit tour d’honneur…

Conclusion

Pour résumer ce que nous venons de voir dans cette partie, quelques exemples commentés avec d’autres tables fictives : 

-- Remonte d’un cran dans le classement de la chansons ayant l’ID 3 appartenant
-- au style_id = 1 (rock) de la table top10
SELECT move_in_container('up', 'top10', 'style_id', 'music_id', 1, 3);

-- Baisse de deux crans dans le classement l’élément de la table top10 ayant
-- l’ID 1 et ayant le style_id=1
SELECT move_in_container('down', 'top10', 'style_id', 'music_id', 1, 1);
SELECT move_in_container('down', 'top10', 'style_id', 'music_id', 1, 1);

-- Réordonne l’ensemble des éléments du style_id = 1 (rock) de la table `top10`
-- en fournissant les IDs dans l’ordre désiré.
-- On a donc :
-- ID 1 en première position,
-- ID 5 en deuxième position,
-- ID 10 en troisième position, etc
SELECT sort_in_container('top10', 'style_id', 'music_id', 1, '1,5,10,3,4,7,8,9,6,2');

Nous sommes au terme de cette petite série d’articles traitant de la façon de classer des données dans une base de données PostgreSQL avec PL/pgSQL.

L’avantage de cette méthode est qu’elle évite l’utilisation de champs prev et next comme nous pouvons le voir parfois (notamment avec des frameworks).

Libre à vous de réadapter ce code qui est sous licence MIT et disponible sur GitHub.

Quelques pistes d’améliorations :

  • Faire en sorte que le nom du champ de tri (sort) puisse être précisé dans les fonctions pour ne pas se baser sur une convention de nommage des champs.
  • Dans les fonctions sort_* tester si les IDs fournis sont bien ceux que l’on a en base.
  • Unifier les fonctions move_simple et move_in_container en une seule fonction move qui jouerait les différents rôles en ayant des signatures différentes. Faire de même avec sort_simple et sort_in_container.

Voilà, amusez-vous avec ces codes, adaptez-les, améliorez-les !

Photo de Jason Leung sur Unsplash