Complex query on tree structure: Help!
The Natural Philosopher
>show fields in category;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| parent | int(11) | YES | | NULL | |
| name | char(64) | YES | | NULL | |
| display | enum('yes','no') | YES | | NULL | |
| description | text | YES | | NULL | |
+-------------+------------------+------+-----+---------+----------------+
This forms a tree structure under which items are listed. It is never
more than 5 levels deep.
The top level is defined by the parent field being zero or null.
Now, I can generate a list of categories of interest easily enough by a
sub select statement to another table
e.g.
>select distinct category from product where product.supplier='1' and
not isnull(product.category);
+----------+
| category |
+----------+
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 48 |
| 64 |
| 66 |
| 97 |
| 119 |
| 127 |
+----------+
14 rows in set (0.00 sec)
What I want to do, is generate a distinct list of all these *and* their
parents and grandparents etc. up to the top level where the parent is
zero or null.
I'm stumped.
I could do it in code..but I'd rather do it as a single query.
Captain Paralytic
wrote:
I've posted this list here many times. The last one is very good, but
the other 2 are worth reading too.
The Natural Philosopher
I've written the answer to life the universe and everything too.
You should read it.
And how pray, am I supposed to find something you posted a year ago? Google?
Andrew C.
"The Natural Philosopher" <t...@invalid.invalid> wrote in message
news:hem3cf$j0l$1...@news.albasani.net...
>I have a table called 'category'
[..]
> I'm stumped.
>
> I could do it in code..but I'd rather do it as a single query.
I don't have much experience in this area, but I do have a couple of links
in my bookmarks just in case someone springs the requirement for
hierarchical data on me at short notice... ;-)
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
http://articles.sitepoint.com/article/hierarchical-data-database/1
I have read them at (what feels like) some distant point in the past, and I
must've considered them to be of a certain quality otherwise I wouldn't've
bothered bookmarking them.
Hopefully, they'll point you in the right direction.
A.
The Natural Philosopher
No, after 4 hours of beating my brains I ended up doing it the long way,
which took ten minutes to code!
I ended up with a two dimensional matrix, and simply walked through that
inserting numbers that are not null, and not already in, into a PHP array.
$result=mysql_query(sprintf(
"select distinct t1.id as a, t2.id as b,t3.id as c, t4.id as d,
t5.id as e, category.id as f
from category
left join category as t5 on t5.id=category.parent
LEFT join category AS t4 on t4.id = t5.parent
LEFT JOIN category AS t3 ON t3.id = t4.parent
LEFT JOIN category AS t2 ON t2.id = t3.parent
LEFT JOIN category AS t1 ON t1.id = t2.parent
where category.id in
(select distinct category from product where product.supplier='%d' and
not isnull(product.category))
group by a, b, c, d, e, f",$supplier));
$rows=mysql_numrows($result);
$categories=array();
for ($i=0;$i<$rows;$i++)
{
$v=mysql_result($result,$i,'a');
if ($v>0 and !in_array($v,$categories)) $categories[]=$v;
$v=mysql_result($result,$i,'b');
if ($v>0 and !in_array($v,$categories)) $categories[]=$v;
$v=mysql_result($result,$i,'c');
if ($v>0 and !in_array($v,$categories)) $categories[]=$v;
$v=mysql_result($result,$i,'d');
if ($v>0 and !in_array($v,$categories)) $categories[]=$v;
$v=mysql_result($result,$i,'e');
if ($v>0 and !in_array($v,$categories)) $categories[]=$v;
$v=mysql_result($result,$i,'f');
if ($v>0 and !in_array($v,$categories)) $categories[]=$v;
}
$category_string=implode(',',$categories);
Not as elegant as I would like, but heck, I am an engineer, not a poet!
That gave me all the categories that contained a product whose supplier
was the one in question, and allowed me to use that as a qualifying
string to walk through the tree on a supplier-specific basis.
Unless anyone can do it better, that's my solution for now!
Brian Cryer
news:hem3cf$j0l$1...@news.albasani.net...
This might not count as a single query, but if you know its never more than
5 deep (which you said) then you could do it in 5 queries (one for each
level of depth) and use union to combine the results.
--
Brian Cryer
www.cryer.co.uk/brian