've setup a table called category:

id - int, primary key, yadda yadda
parent_id = int
depth = int
cattitle = varchar(50)

And I've written a query that will take each record, and via a reflexive relationship, join each record to its parent:

SELECT category.cattitle, subcat.cattitle FROM category INNER JOIN category subcat ON subcat.parent_id = category.id

And this all works fine and dandy, except that there's no useful and efficient way to output the results. It simply looks at each record and joins it to its parent.

What would be the proper grouping/ordering to get a nice clean outputtable (is that a word?) result set?

I'd like
Category1
--SubCat1
----SubSubCat1
Category2
--SubCat2
----SubSubCat2
----SubSubCat2
Category3
--SubCat3
--SubCat3

Etc, etc.

Anyone ever pulled this off before? Is there a better way to do this? I'd really like to do it with one query, and output it in a single pass, without having to loop over the result set several times.

(mysql and php by the way).

you can do this in one query but only if you can set a maximum on the number of levels that you want to go (up or down the hierarchy)

for true infinite depth, you need to call the database inside a loop (i.e. recursively), with all that this implies in the way of performance problems

but i always ask people, are you really planning to go N levels deep? (where N is some number like 8 or 11)

when was the last time you went to a web site and saw 8 levels deep at one time?

for example, if you go to dmoz or yahoo, they have categories, and it is entirely possible that they might even go 8 levels deep, but you just flat out never get to see a tree exploded to 8 levels

with me so far?

anyhow, i tell people to pick some number like 3 or 4 as the absolute maximum number of levels that you want to go down the tree starting from any given node

then, once those 3 or 4 levels have been displayed, and the user clicks on one of the lower nodes, you can go back to the database and get 4 more levels down from that node

but you never see nested categories 8 levels deep (or, if you do, would you please kindly post the url here, as i will use it as an example in future threads of this nature)

I agree, and you're probably right. But if there was a simple and straight forward way to properly sort this output, it would be a non-issue. It could be the first site to go more than 8 levels deep ever, and it wouldn't be a problem.

As a programmer, its just one those things I hate: making assumptions.

Assuming that no one will ever do X, or click on Y, etc.

If its an impossibility, or extremely difficult, then I'll look for some other options.

Worst case scenario, I'll leave the query as is, and write a complex looping scenario to sort it into an array, and then output it. Which, compared to subqueries for each category, is incredibly more efficient. At the very least I could just build a balanced b-tree from the result, then output it. And there might be even more efficient ways to do it than that.

you don't have to make assumptions, you just design your application so that it only shows 4 levels down from any given node at a time

of course, you can do it for 8 if you so desire

and you can do it with one query, properly sorted

i think you can go 15 levels in one query with no performance problem

but infinite? unlimited? you have write recursive code

have fun

Just make sure you have infinite storage space and memory to display your result.... and infinite time to generate the result.