I have run into a Coldfusion problem that I was hoping you
could help me with.
What I am trying to solve is the dynamic creation of at least
5 levels of categories for a menu built with Coldfusion, and if
possible an infinite amount of levels.
Each of these also if it is at the end of the tree will have
to have products under it. The category will also only show up if
there is products under it otherwise it will not get created.
To accomplish this I have created a database table called
"Categories" which contains the fields shown in the linked
spreadsheet. I am using the "cat_parent" to hold the value of the
parent of the current record. When the parent is a top level menu
item I have assigned this a cat_parent id of "0". Using this method
I am able to do a self join on the table (where "cat_parent" =
"cat_id") to extract the relationship between parent and child
records throughout the table. This works fine. And I have created
output for 5 levels of display.
There are 3 issues left that I could not work out.
- Firstly my coding method loops through each node of the
tree with one query to create each category of the menu. This means
one query per menu category and as the client will have up to 200
categories in the menu and I am concerned that the solution that I
have come up with is too processor intensive.
- Secondly the way that I have created the category menu is
hard coding in the levels needed so there is only 5 not an infinite
amount which would be preferable but is not essential.
- Finally I could not work out how to NOT create "parents" in
the tree if there is no products for the end child category.
Meaning that if there is for example a category > subcategory
> subsubcategory but there is no products associated with the
end - "subsubsubcategory" then it's parent(s) and so on will not
display until one of them has products under it.
is a link to a zip file that includes: the cfc that contains
the query function (categories.cfc), the page that is creating the
categories menu (lefnav.cfm), a simple cfc that contains a products
query just for testing (products.cfc) along with a spreadsheet of
the Categories table's fields.
I would greatly appreciate any suggestions.
Thanks for any time and help you can give me on this