Как пробросить значение поля в CTE?

Question

[Алексей Медведев]

Суть вопроса в пробросе значения поля в MariaDB, либо в изменении запроса.

Есть две таблицы, подразделения и пользователи:
users (id, name, structure_id)
structure (id, parent_id, name) - у корневой структуры parent_id = null

Необходимо вывести следующие поля:
users.id, users.name, all_structures (все названия подразделений до корня через разделитель)

Есть запрос:

SELECT
    u.*,
    (
        WITH
            RECURSIVE struct AS (
            SELECT cs.*
            FROM structure cs
            WHERE cs.id = u.structure_id -- ошибка именно тут
            UNION ALL
            SELECT cs.*
            FROM struct s
            JOIN structure cs ON cs.id = s.parent_id
        )
        SELECT GROUP_CONCAT(struct.name SEPARATOR '|')
        FROM struct
        WHERE
                struct.id <> u.structure_id -- тут ошибки нет

        ) all_structures
FROM users u

Пробовал и через JOIN'ы, но проблема всегда в одном и том же месте.

Заранее спасибо за ответ.

Comments

[Akina]

Не страдайте фигнёй. Получите FQPath в рекурсивном CTE, а потом джойните с юзерами.

[Алексей Медведев]

Akina, а можете подробнее про это рассказать? Никогда не сталкивался с подобным.

Написал такой запрос:

WITH RECURSIVE struct AS (
    SELECT
        cs.*,
        CAST(cs.name AS CHAR(65535)) fqpath
    FROM structure cs
    -- WHERE cs.parent_id IS NULL
    UNION ALL
    SELECT
        cs.*,
        CONCAT_WS('|', s.fqpath, cs.name)
    FROM struct s
    JOIN structure cs ON cs.id = s.parent_id
)
SELECT *
FROM struct;

Но на выходе получаю совсем не то, что нужно, а именно:

id | name               | parent_id | fqpath                                           |
11 | Название структуры | 10        | Название 1 дочерней структуры/Название структуры |
11 | Название структуры | 10        | Название 2 дочерней структуры/Название структуры |
11 | Название структуры | 10        | Название 3 дочерней структуры/Название структуры |
11 | Название структуры | 10        | Название 4 дочерней структуры/Название структуры |

Нужно, чтобы в fqpath выводились все родители, вплоть до корня:

Название структуры/Название родителя2/Название родителя 1/Название корня

Если я поменяю в JOIN'е cs.id = s.parent_id на cs.parent_id = s.id это не решит проблему полностью, я буду получать только одного родителя.

[Akina]

Выложите в вопросе ссылку на fiddle с тест-данными, либо CREATE TABLE + INSERT INTO. И требуемый финальный результат для таких данных.

3 уровня иерархии в структуре и десяток юзеров - достаточно, ну и названия не такие длинные...

PS. А что условие WHERE cs.parent_id IS NULL закомментировано-то? Запрос вроде нормальный, только условие связывания в рекурсивной части наоборот должно быть - ты ж там идёшь вниз по дереву... ну и ещё не вижу финального джойна юзеров в полученную структуру.

я буду получать только одного родителя.

Эммм... так у тебя и есть не более одного непосредственного родителя на узел. А прямых получишь всех.

[Алексей Медведев]

Akina, Всё, разобрался, спасибо за наводку )) Если что, вот решение:

WITH RECURSIVE struct AS (
    SELECT
        cs.*,
        0 as level,
        CAST(if(cs.parent_id IS NULL, '', cs.name) AS CHAR(65535)) fqpath
    FROM structure cs
    WHERE
        cs.parent_id IS NULL
    UNION ALL
    SELECT
        cs.*,
        s.level + 1,
        if(s.level = 0, cs.name, CONCAT_WS('|', cs.name, s.fqpath))
    FROM structure cs
    JOIN struct s ON cs.parent_id = s.id
)
SELECT *
FROM struct;

[Akina]

Алексей Медведев,

CAST(if(cs.parent_id IS NULL, '', cs.name) AS CHAR(65535)) fqpath

Это чё за изврат-то? у тебя ж там явно написано:
WHERE cs.parent_id IS NULL
Значит, условие всегда TRUE, соответственно IF() явно лишний, и достаточно
CAST(cs.name AS CHAR(65535)) fqpath

[Алексей Медведев]

Akina, верно, таки оставил: CAST(cs.name AS CHAR(65535)) fqpath.
Просто это я свои эксперименты не убрал за собой )

WITH RECURSIVE struct AS (
    SELECT
        cs.*,
        0 as level,
        CAST(cs.name AS CHAR(65535)) fqpath
    FROM structure cs
    WHERE cs.parent_id IS NULL
    UNION ALL
    SELECT
        cs.*,
        s.level + 1,
        IF(s.level = 0, cs.name, CONCAT_WS('|', s.fqpath, cs.name))
     FROM structure cs
     JOIN struct s ON cs.parent_id = s.id
)
SELECT
    u.*,
    s.fqpath AS full_structure
FROM users u
LEFT JOIN struct s ON s.id = u.structure_id