Получение всех родительских строк в одном запросе SQL

У меня есть простая таблица MySQL, которая содержит список категорий, уровень определяется parent_id:

id  name    parent_id
---------------------------
1   Home        0
2   About       1
3   Contact     1
4   Legal       2
5   Privacy     4
6   Products    1
7   Support     1

Я пытаюсь сделать тротуар. Таким образом, у меня есть "id" ребенка, я хочу получить всех доступных родителей (итерируя цепочку, пока мы не достигнем 0 "Главная" ). Там могут быть любые числа или дочерние строки, идущие на неограниченную глубину.

В настоящее время я использую SQL-вызов для каждого родителя, это грязно. Есть ли способ в SQL сделать все это по одному запросу?

Ответ 1

Адаптировано из здесь:

SELECT T2.id, T2.name
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 5, @l := 0) vars,
        table1 h
    WHERE @r <> 0) T1
JOIN table1 T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC

Строка @r := 5 - номер страницы для текущей страницы. Результат следующий:

1, 'Home'
2, 'About'
4, 'Legal'
5, 'Privacy'

Ответ 2

Отличный ответ от Mark Byers!

Может быть, немного опоздал на вечеринку, но если вы также хотите предотвратить бесконечный цикл, когда id = parent_id (т.е. каким-то образом, когда данные были повреждены), вы можете расширить ответ следующим образом:

    SELECT T2.id, T2.name
    FROM (
        SELECT
            @r AS _id,
            @p := @r AS previous,
            (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,
            @l := @l + 1 AS lvl
        FROM
            (SELECT @r := 5, @p := 0, @l := 0) vars,
            table1 h
        WHERE @r <> 0 AND @r <> @p) T1
    JOIN table1 T2
    ON T1._id = T2.id
    ORDER BY T1.lvl DESC

Ответ 3

В дополнение к приведенным выше решениям:

post
-----
id
title
author

author
------
id
parent_id
name


[post]

id  | title | author |  
----------------------
1   | abc   | 3      |


[author]

| id    | parent_id | name  |   
|---------------------------|
| 1     | 0         | u1    |
| 2     | 1         | u2    |
| 3     | 2         | u3    |
| 4     | 0         | u4    |

автор, включая родителей, может иметь доступ к сообщению.

Я хочу проверить, имеет ли автор доступ к сообщению.

Решение:

предоставить идентификатор автора сообщения и вернуть всех его авторов и родителей-авторов

SELECT T2.id, T2.username 
FROM (
    SELECT @r AS _id, 
        (SELECT @r := parent_id FROM users WHERE id = _id) AS parent_id,
        @l := @l + 1
    FROM
        (SELECT @r := 2, @l := 0) vars, 
        users h     
    WHERE @r <> 0) T1 JOIN users T2 
ON T1._id = T2.id;

@r: = 2 = > присвоение значения переменной @r.

Ответ 4

Я думаю, нет простого способа сделать это, используя один запрос.

Я бы рекомендовал взглянуть на Nested Sets, который, по-видимому, соответствует вашим потребностям.

Ответ 5

Если у вас есть идентификатор вместо идентификатора, просто запустите подзапрос, чтобы найти идентификатор дочерней категории.
Таблица - категории
| id | parentId | слизняк |
| ------------------------- |
| 1 | 0 | u1 |
| 2 | 1 | u2 |
| 3 | 2 | u3 |
| 4 | 0 | u4 |

SELECT T2.id, T2.slug
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parentId FROM categories WHERE id = _id) AS parentId,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := (SELECT id FROM categories WHERE slug = 'u3'), @l := 0) vars,
        categories h
    WHERE @r <> 0) T1
JOIN categories T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC

Ответ 6

Я использовал предыдущие ответы в качестве примеров, чтобы сделать что-то более читабельным.

SELECT  @org_id as id,
    (SELECT name FROM test.organizations WHERE id = @org_id) as name,
    (SELECT @org_id := parent_id FROM test.organizations WHERE id = @org_id) AS parent_id
FROM (SELECT @org_id := 4) vars, test.organizations org
WHERE @org_id is not NULL
ORDER BY id;

Результат выполнения выглядит так:

(просто для быстрого), чтобы проверить это самостоятельно, вам нужно ввести значения из вопроса в test базы данных, таблицы organizations

CREATE TABLE organizations(
id        int(11) NOT NULL AUTO_INCREMENT,
name      varchar(45) DEFAULT NULL,
parent_id int(11)     DEFAULT NULL,
PRIMARY KEY (id));

insert into organizations values(1, "home", null);
insert into organizations values(2, "about", 1);
insert into organizations values(3, "contact", 1);
insert into organizations values(4, "legal", 2);
insert into organizations values(5, "privacy", 4);
insert into organizations values(6, "products", 1);
insert into organizations values(7, "support", 1);

Ответ 7

AFAIK no.

Эта статья Sitepoint может вам помочь.

Вы можете получить все элементы с одним запросом, сохранить его в массиве и затем повторить, как описано здесь и здесь