Infinite Category Loop

Hi guys!

Im currently writing a category-system that can handle infinite levels of subcategories.

If the category has a parent = 0, its a main category, anything else and its a subcategory.

That way i can nest the categories in as many levels i need.

My problem is how i can list all the categories. I need a loop that can manage any number of levels, and preferably with a level-counter so i can get the number of parent categories this category have.

I’ve experimented with while()-loops and functions calling themselfs to try to create a loop that only ends when the category has no sub-categories.

Anyone know of a smart solution to this?

Appriciate all help, guys.

If needed i can post my experimental code, but im guessing there is a best practise to this problem.

I think what you are really looking for is called nested sets. I wouldn’t know of any other (efficient) way to solve this problem in the db.

I do, check out this article about implementing adjacency list in PostgreSQL with a helper table for efficient queries. If you’re still interested, there is a perl implementation that generates all necessary db structures. It should work in PostgreSQL and SQLite.

I find this better than nested set because it’s easier to debug and the efficiency is comparable.

I will read up on the information you provided.

I started thinking about generating a json for the structure thats rewritten when changes to the categories are made, and that might be easier to work with. Havent had time to test i yet, but ill get back to you.

Thanks for the help, guys.

Every solutions includes some form of cache, because usually there are a lot of reads (expensive) from the tree and very few changes to the tree. This is the same argument that makes nested set a valid solution.

Yeap. I downloaded the NestedSetBehaviour ext, and that was brilliant.

So far its extremly quick, and well written. But i’ll see about that json fix laters, im gonna use APC on this project anyways.

Thanks for the input, guys.