Requête récursive ?

Version imprimable

Requête récursive ?

Bonjour à toutes et à tous,

je suis en train de concevoir un outil de gestion de contenu (=CMS) assez basique dans l'ensemble.
J'essaye de trouver une méthode pour afficher hiérarchiquement l'historique d'une page (=son contenu).

Voici un exemple (simplifié) de ce que j'aimerais avoir :
Ici, on affiche l'historique des modifs de la page "Présentation" (id:26)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
Présentation (id=26) (créé le 09 septembre 2007 à 09 :00 :00)

    +------->(id=37, v1) modifié le 1 octobre 2007 à 10 :10 :00
    +------->(id=38, v2) modifié le 1 octobre 2007 à 11 :11 :00
         +------->(id=41, v2.1) modifié le 14 octobre 2007 à 14 :14 :00
    +------->(id=39, v3) modifié le 03 octobre 2007 à 12 :12 :00
         +------->(id=40, v3.1) modifié le 03 octobre 2007 à 13:13 :00
         +------->(id=42, v3.2) modifié le 15 octobre 2007 à 15 :15 :00
              +------->(id=46, v3.2.1) modifié le 15 novembre 2007 à 19 :19 :00
         +------->(id=45, v3.3) modifié le 30 octobre 2007 à 18 :18 :00
    +------->(id=43, v4) modifié le 22 octobre 2007 à 16 :16 :00
         +------->(id=44, v4.1) modifié le 23 octobre 2007 à 17 :17 :00

à partir de :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE `t_page` (
  `id` MEDIUMINT unsigned NOT NULL auto_increment,
  `origine_id` MEDIUMINT unsigned,
  `parent_id` MEDIUMINT unsigned,
  `html` TEXT NOT NULL,
  `date_creation` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=47;
 
INSERT INTO `t_page` (`id`, `origine_id`, `parent_id`, `date_creation`, `html`) VALUES 
(37, 26, 26, "2007-10-01 10:10:00", "modif v1"), 
(38, 26, 26, "2007-10-01 11:11:00", "modif v2"), 
(39, 26, 26, "2007-10-03 12:12:00", "modif v3"), 
(40, 26, 39, "2007-10-03 13:13:00", "modif v3.1"), 
(41, 26, 38, "2007-10-14 14:14:00", "modif v2.1"), 
(42, 26, 39, "2007-10-15 15:15:00", "modif v3.2"), 
(43, 26, 26, "2007-10-22 16:16:00", "modif v4"), 
(44, 26, 43, "2007-10-23 17:17:00", "modif v4.1"), 
(45, 26, 39, "2007-10-30 18:18:00", "modif v3.3"), 
(46, 26, 42, "2007-11-15 19:19:00", "modif v3.2.1");

Donc vous comprendrez que l'affiche n'est pas classé "bêtement" par date mais par version.
Est-il possible de faire cela sachant que la version n'est pas enregistrée en dure ?
Merci d'avance. ;)

Quelque chose comme ça ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
WITH RECURSIVE  levels
    (   id
    ,   origine_id
    ,    parent_id
    ,   id_level
    ,    id_version
    ,    version    
    )
AS  (   SELECT  page.id
            ,   page.origine_id
            ,    page.parent_id
            ,   1   AS id_level
            ,    TRIM(CAST(COUNT(*) AS CHAR(3)))    AS version
        FROM    t_page  AS page
            INNER JOIN
                t_page    AS prec
                ON    page.origine_id = prec.origine_id
                AND    page.parent_id  = prec.parent_id
                AND page.datecreation >= prec.datecreation
        WHERE   page.parent_id = page.origine_id
        GROUP BY page.id 
            ,    page.origine_id
            ,    page.parent_id
    UNION ALL
        SELECT  page.id
            ,   page.origine_id
            ,    page.parent_id
            ,   levl.id_level + 1   AS id_level
            ,    levl.version || '.' || TRIM(CAST(COUNT(*) AS CHAR(3)))    AS version
        FROM    t_page  AS page
            INNER JOIN
                levels  AS levl
                ON  page.origine_id = levl.origine_id
                AND page.parent_id  = levl.id
            INNER JOIN
                t_page    AS prec
                ON    page.origine_id = prec.origine_id
                AND    page.parent_id  = prec.parent_id
                AND page.datecreation >= prec.datecreation
        GROUP BY page.id 
            ,    page.origine_id
            ,    page.parent_id
    )
SELECT  *
FROM    levels  AS levl
    INNER JOIN
        t_page  AS page
        ON  levl.id = page.id
ORDER BY    
        page.origine_id
    ,   levl.version
    ,   page.date_creation
;

A vérifier, c'est un premier jet, non testé

15/11/2007, 14h37
Tchupacabra

8O merci beaucoup al1_24 pour cette requête venue d'une autre planète !;)

Cependant, mon simple test dans MySQL 5 derrière phpMyAdmin 2.9 ne semble pas fonctionner.... :cry:
Apparemment, MySQL ne connait cette directive "RECURCIVE" / syntaxe :

Citation:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WITH RECURSIVE levels
( id
, origine_id
, parent_id
' at line 1

une idée ?
15/11/2007, 15h12
al1_24

Citation:

Envoyé par Tchupacabra

Cependant, mon simple test dans MySQL 5 derrière phpMyAdmin 2.9 ne semble pas fonctionner.... :cry:
Apparemment, MySQL ne connait cette directive "RECURCIVE" / syntaxe

une idée ?

Changer de SGBD ? :aie:

Faire la boucle de traitement par programme, pas en SQL pur.
15/11/2007, 15h24
Tchupacabra

Citation:

Envoyé par al1_24

Changer de SGBD ? :aie:

ma seule autre possibilité est POSTGRE, que je ne connais pas... ça serait jouable ?

Citation:

Envoyé par al1_24

pas en SQL pur.

c'est bien ce que je craint.... je vais voir ça avec PHP...

Par programmation, c'est équivalent à ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
CREATE TABLE levels
    (   id            MEDIUMINT
    ,   origine_id    MEDIUMINT
    ,    parent_id    MEDIUMINT
    ,   id_level    MEDIUMINT
    ,    version        VARCHAR(50)
    )
;
INSERT INTO    levels
    (    id            
    ,   origine_id    
    ,    parent_id    
    ,   id_level    
    ,    version        
    )
SELECT  page.id
    ,   page.origine_id
    ,    page.parent_id
    ,   1   AS id_level
    ,    TRIM(CAST(COUNT(*) AS CHAR(3)))    AS version
FROM    t_page  AS page
    INNER JOIN
        t_page    AS prec
        ON    page.origine_id = prec.origine_id
        AND    page.parent_id  = prec.parent_id
        AND page.datecreation >= prec.datecreation
WHERE   page.parent_id = page.origine_id
GROUP BY page.id 
    ,    page.origine_id
    ,    page.parent_id
;
repeter
    INSERT INTO    levels
        (    id            
        ,   origine_id    
        ,    parent_id    
        ,   id_level    
        ,    version        
        )
    SELECT  page.id
        ,   page.origine_id
        ,    page.parent_id
        ,   levl.id_level + 1   AS id_level
        ,    levl.version || '.' || TRIM(CAST(COUNT(*) AS CHAR(3)))    AS version
    FROM    t_page  AS page
        INNER JOIN
            levels  AS levl
            ON  page.origine_id = levl.origine_id
            AND page.parent_id  = levl.id
        INNER JOIN
            t_page    AS prec
            ON    page.origine_id = prec.origine_id
            AND    page.parent_id  = prec.parent_id
            AND page.datecreation >= prec.datecreation
    GROUP BY page.id 
        ,    page.origine_id
        ,    page.parent_id
    ;
jusqu'à : aucun enregistrement ajouté
 
SELECT  *
FROM    levels  AS levl
    INNER JOIN
        t_page  AS page
        ON  levl.id = page.id
ORDER BY    
        page.origine_id
    ,   levl.version
    ,   page.date_creation
;

15/11/2007, 16h55
Tchupacabra

merci beaucoup al1_24 !

j'essaye de tester ça rapidement... ;)