php - 选择文章的可用父项(子项除外)

我有一张文章表（文章树）。假设它具有id，parent_id和name。



对于文章的编辑形式，我想获取可用父文本的列表。我不能只列出所有文本，因为文章不能是它自己的子项目的子项目。

有没有办法使用MySQL获取该列表？

最佳答案

SELECT * FROM articles WHERE id NOT IN (

SELECT DISTINCT articles.id FROM
(
    SELECT
        @row_num := @row_num + 1 AS row_num,
        IF(@last_id <> my_list.id, @id := my_list.id, @id) AS path_id,
        @last_id := my_list.id AS id,
        @id := (SELECT DISTINCT parent_id FROM articles WHERE id = @id) AS parent_id
    FROM
        (SELECT @id := 0, @last_id := 0, @row_num := 0) AS vars,
        (SELECT id FROM articles) AS my_loop,
        (SELECT id FROM articles) AS my_list
) AS t

INNER JOIN articles
ON t.id = articles.id
WHERE path_id = :id
)

关于php - 选择文章的可用父项(子项除外)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47134950/