Requêtes récursives avec WITH RECURSIVE

Une CTE récursive (Common Table Expression) parcourt une arborescence en une seule requête SQL. Utilisez-la pour lire tous les niveaux d'une hiérarchie : sous-tâches, nomenclatures à plusieurs niveaux, comptes d'un plan comptable.

Dans la version next de Dodock, frappe.qb.with_() accepte l'argument recursive=True. Dodock produit alors WITH RECURSIVE sur MariaDB (10.2 et plus), PostgreSQL et SQLite. Sans cet argument, with_() produit un WITH simple. Cet argument n'existe pas en v5.

Principe

Une CTE récursive a deux parties, réunies par + (UNION) :

1. La partie initiale sélectionne les lignes de départ.
2. La partie récursive joint la table à la CTE elle-même, pour ajouter le niveau suivant.

La base répète la partie récursive tant qu'elle trouve de nouvelles lignes. Dans la partie récursive, désignez la CTE avec AliasedQuery("<nom>"), le même nom que dans with_().

      
graph TD
    A[Partie initiale : lignes de départ] --> B[Partie récursive : enfants du niveau précédent]
    B --> C[Niveau suivant]
    C --> D[Arrêt quand aucune nouvelle ligne]

    

Exemple : toutes les sous-tâches d'une tâche

Le champ parent_task de Tâche (Task) pointe vers la tâche parente. Cette requête renvoie la tâche TASK-2026-00001 et toutes ses sous-tâches, à tous les niveaux. Le nom de la tâche est fictif.

import frappe
from pypika import AliasedQuery

Task = frappe.qb.DocType("Task")
arbre = AliasedQuery("arbre")

depart = (
    frappe.qb.from_(Task)
    .select(Task.name, Task.subject, Task.parent_task)
    .where(Task.name == "TASK-2026-00001")
)

enfants = (
    frappe.qb.from_(Task)
    .join(arbre)
    .on(Task.parent_task == arbre.name)
    .select(Task.name, Task.subject, Task.parent_task)
)

taches = (
    frappe.qb.with_(depart + enfants, "arbre", recursive=True)
    .from_(arbre)
    .select(arbre.name, arbre.subject, arbre.parent_task)
    .run(as_dict=True)
)

Exemple : composants d'une nomenclature à plusieurs niveaux

Une ligne de nomenclature (Article de nomenclature, BOM Item) peut porter sa propre nomenclature dans le champ bom_no. Cette requête suit ces liens et renvoie chaque composant avec son niveau. Le nom de la nomenclature est fictif.

import frappe
from pypika import AliasedQuery

BOMItem = frappe.qb.DocType("BOM Item")
composants = AliasedQuery("composants")

depart = (
    frappe.qb.from_(BOMItem)
    .select(BOMItem.parent, BOMItem.item_code, BOMItem.qty, BOMItem.bom_no)
    .where(BOMItem.parent == "BOM-CHAISE-001")
    .where(BOMItem.parenttype == "BOM")
)

sous_composants = (
    frappe.qb.from_(BOMItem)
    .join(composants)
    .on(BOMItem.parent == composants.bom_no)
    .select(BOMItem.parent, BOMItem.item_code, BOMItem.qty, BOMItem.bom_no)
    .where(BOMItem.parenttype == "BOM")
)

lignes = (
    frappe.qb.with_(depart + sous_composants, "composants", recursive=True)
    .from_(composants)
    .select(composants.parent, composants.item_code, composants.qty)
    .run(as_dict=True)
)

Bonnes pratiques

• Évitez les boucles. Si les données peuvent former un cycle (A parent de B, B parent de A), la requête ne s'arrête pas. Ajoutez une colonne de profondeur et une condition qui la limite dans la partie récursive.
• Les deux parties doivent renvoyer le même nombre de colonnes, dans le même ordre.
• Ces requêtes ne contrôlent pas les autorisations de l'utilisateur. Contrôlez-les avant d'exposer le résultat, par exemple avec frappe.has_permission.

Ressources

• CTE récursives dans PostgreSQL
• CTE récursives dans MariaDB