邻接模型，给定一个 id 返回叶节点 [英] adjacency model , given an id return the leaf nodes

问题描述

这是用于存储类别/子类别站点导航的表:

This is the table for storing a categories/subcategories site navigation :

   $query = "CREATE TABLE categories (
    id int(11) NOT NULL auto_increment,
    name varchar(100) NOT NULL,
    parentid int(11) NOT NULL,
     PRIMARY KEY  (id)
)"  ;

并说这些是表中的条目

id  name    parentid
1   animal  NULL
2   vegetable NULL
3   mineral NULL
4   doggie  1
5   kittie  1
6   horsie  1
7   gerbil  1
8   birdie  1
9   carrot  2
10  tomato  2
11  potato  2
12  celery  2
13  rutabaga    2
14  quartz  3

我想要的是这样一个 sql 查询，对于给定的 id ，返回所有叶节点，如果给定了叶节点的 id，则返回叶节点本身.

What I want is such an sql query that for a given id , all the leaf nodes are returned AND if the id of a leaf node is given then the leaf node is itself returned.

因此，如果设置了 id 2，则返回的行是 - 胡萝卜、番茄、马铃薯、芹菜、大头菜.如果给出 id 为 9 ，则返回的行是 - 9 本身

So if the id 2 is set , the rows returned are - carrot,tomato,potato,celery,rutabaga. If the id is 9 is given , the row returned is - 9 itself

可能吗?

我的子类别不会超过 3 个级别.

my subcategoires won't go more than 3 levels deep.

我尝试了在此页面上的代码，但是如果给出了叶节点 id，它不会给出叶节点.

I tried the code given on this page , but it doesn't give the leaf node , if the leaf node id is given.

谢谢

我尝试了一些查询..

    SELECT distinct t1.name FROM
categories AS t1 LEFT JOIN categories as t2
ON t1.id = t2.parent
 LEFT JOIN categories as t3
ON t2.id = t3.parent
WHERE  t1.parent = ? OR t1.id = ?

但我根本无法理解连接.

but I am simply not able to understand joins.

我可以放弃返回叶节点，如果叶节点 id 给定 part .现在我只需要一个查询，该查询将返回所有叶节点，给定一个类别/子类别节点.再次感谢

I can forgo the return leaf node, if leaf node id given part . Now I just need a query that will return all leaf nodes , given a category/subcategory node. Thanks again

推荐答案

所以我使用的最终查询如下所示:

So the final query that I use looks like this:

SELECT distinct t2.id , t2.name FROM
    categories AS t1 LEFT JOIN categories as t2
    ON t1.id = t2.parent
     LEFT JOIN categories as t3
    ON t2.id = t3.parent
    WHERE  t1.parent = $id OR t1.id = $id and t2.visible = 1

如果返回空结果集，则意味着提供了一个结束节点，我只需返回提供的 $id.它的工作.希望它会继续这样做，因为我在这里猜测.

if an empty result set is returned, it means an ending node was supplied and I simply return the supplied $id. its working. Hopefully it will continue to do so, because I am kind of guessing here.

这篇关于邻接模型，给定一个 id 返回叶节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！