Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    10

    Unanswered: Infinite Category Depth (via reflexive relationship)

    '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).

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Posts
    10
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2004
    Posts
    330
    Just make sure you have infinite storage space and memory to display your result.... and infinite time to generate the result.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •