Mettre à jour la profondeur de chaque catégorie en base, en une requête ?

Hello !

Je souhaiterais, en une seule requête, pouvoir mettre à jour la profondeur de chaque catégorie d’une table MySQL basée sur le modèle du nested trees.

Comment connaître la profondeur d’une catégorie donnée ?

Alors déjà ça j’ai réussi à trouver. Par définition, il suffit récupérer le nombre de niveaux qui sont enfants directs et indirects d’une catégorie donnée. Dans un nested tree, ça correspond donc à :

SELECT COUNT(*) FROM ma_table WHERE nleft < categorie_nleft AND nright > categorie_nright auquel on ajoute 1 (pour avoir la profondeur). "categorie_" c’est la catégorie dont on veut savoir la profondeur.

Mais comment exécuter ça pour toute catégorie et dans le cadre d’un UPDATE ?

C’est là que je sèche. J’aimerais utiliser ce principe en une seule requête UPDATE qui serait exécutée pour chaque entrée, sans procédure SQL ni rien.

Je pensais éventuellement à un INNER JOIN mais je ne crois pas que ce soit la bonne piste.

Vous auriez qlq chose à me conseiller svp ?

Je pense que cette requête va provoquer l’exécution de la sous-requête SELECT à chaque row qui va être mise à jour car cette dernière fait explicitement référence à t1 qui lui est extérieure, elle y est corrélée.

Pour éviter cela, il faut idéalement que la sous-requête soit auto-suffisante. Je pense que notre cas, c’est possible de décorréler (un peu dans l’esprit de ce que j’ai proposé) et ainsi procéder en deux étapes distinctes : avoir un résultat transitoire auto suffisant, et appliquer l'UPDATE en se basant sur lui.

J’ajoute une précision importante quant à la sémantique, au cas où @HerbeQuiBenchEtSquat se demanderait : Le tout se fait en une seule requête, donc dans une même transaction, ce qui nous évite un éventuel problème d’état transitoire qui deviendrait obsolète avant de procéder à la seconde étape. Quand même la chose devait être faite en deux requêtes distinctes (ce qui est possible avec MySQL et les variables je pense), il suffirait d’isoler le bloc dans une transaction pour éviter de genre de souci.

Oui, et j’en ai bien conscience. Peut-être qu’il y a moyen d’optimiser en partant de cette idée, je ne manipule pas assez le SQL.

Pour se rapprocher de ce que tu proposes, je crois que l’élément précédent est le premier où nleft est plus petit que nleft courant (en imaginant évidemment que l’arbre est correct). En comparant aussi nleft courant avec nright du précédent ainsi trouvé, on peut déterminer s’il faut ajouter, soustraire ou ne rien faire à la profondeur du nœud précédent pour le calcul de celle du nœud qui nous occupe.

Cela implique d’avoir défini la profondeur du (ou des) nœud(s) racine, mais c’est pas le plus difficile à faire, me semble-t’il.

Bonsoir tt le monde,

La solution de la sous-requête fonctionne bien ! Résultats presque instantanés sur ma table de catégories Prestashop à 340 éléments.

A noter que dans MySQL, la requête telle qu’elle a été donnée ici ne marche pas. Franchement les gars… si c’est pour dire de la merde et mal aider, n’aidez pas, hein !!! … (je rigole, merci beaucoup, ça m’a bien débloqué !!! ^_ )

Du coup j’en profite pour clarifier ce point !

La req suivante… :

UPDATE
        ta_table t1
    SET
        t1.depth = (
            SELECT
                    count(*)
                FROM
                            ta_table t2
                WHERE
                        t2.nleft < t1.nleft
                    AND t2.nright > t1.nright
             ) + 1
         )

… doit être changée en :

UPDATE ta_table t1 SET t1.depth = ( SELECT count() FROM (SELECT FROM ta_table) t2 WHERE t2.nleft < t1.nleft AND t2.nright > t1.nright ) + 1 )

(notez le FROM (SELECT * FROM ta_table) )

En effet : https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause/14302701

Après concernant les bénéfices/inconvénients inhérents à l’utilisation de cette requête, j’en ai fichtrement aucune idée, j’ai pas assez de connaissances ni l’envie de me pencher dessus, j’ai déjà trop de "retard" de livraison sur ce projet !

Merci encore tout le monde et @Ymox vu que c’est toi l’auteur de cette requête (somme toute assez simple, j’aurais peut-être dû réfléchir davantage au concept de nested trees vu que ta requête en découle directement, au lieu de partir sur un inner ou une transaction ou quoi).

j’aurais peut-être dû réfléchir davantage au concept de nested trees vu que ta requête en découle directement, au lieu de partir sur un inner ou une transaction ou quoi).

Je ne comprends pas le rapport avec les transactions. J’espère que je ne t’ai pas induit en défaut avec ma remarque parce que là je crois qu’il y a une confusion.

Après concernant les bénéfices/inconvénients inhérents à l’utilisation de cette requête, j’en ai fichtrement aucune idée, j’ai pas assez de connaissances ni l’envie de me pencher dessus, j’ai déjà trop de "retard" de livraison sur ce projet !

Même si la requête est corrélée (suboptimal en terme de performances), on dirait que ça passe tranquillement avec 340 éléments et c’est tant mieux car ça permet en l’occurrence d’avoir une requête plus claire et intuitive.

Cependant, je t’invite quand même à te pencher dessus quand tu auras le temps pour savoir faire le jour où tu en auras non pas 340 mais 34 000

A noter que dans MySQL, la requête telle qu’elle a été donnée ici ne marche pas. Franchement les gars… si c’est pour dire de la merde et mal aider, n’aidez pas, hein !!! … (je rigole, merci beaucoup, ça m’a bien débloqué !!! _ )

Il faut savoir qu’on est souvent amenés à utiliser plusieurs DB pendant notre carrière, tantôt MySQL, tantôt PostgreSQL, tantôt Oracle… même si les principes restent les mêmes, il y a des subtilités de syntaxe qui changent d’un dialecte à un autre et on a parfois du mal à s’y retrouver

Du coup le problème a été résolu mais je réponds quand même à certains messages !

Impossible de parcourir caté par caté car ça met beaucoup trop de temps d’utiliser la fonction Prestashop de mise à jour du level. Ceci dit tu voulais plutôt me proposer de faire moi-même cette màj en incrémentant le bon level_depth de caté en caté, pour chaque caté, un truc récursif. Mais bon ça prendrait du temps vu qu’il y en a 340 et surtout c’est pas assez "net"/"propre" je trouve

Tu as raison, me suis dépêché de répondre car j’allais manger…!