php - 为文章选择可用的父项目(不包括子项目)

Question

我有一个文章表(文章树)。假设它有 id、parent_id 和 name。

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

有什么方法可以使用 MySQL 获取该列表吗？

Answer 1

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
)