This content has been marked as final. Show 11 replies
You could write a recursive loop. I've done this before by creating your own custom tag that will display a given node, then display its children. Create a custom tag <cf_getChildren id=0>
The custom tag will have logic displayed below.
If you have the option, I'd re-design the table structure. Your data looks like it should be stored in multiple related tables, which simple JOIN's could handle.
My experience is limited to simple parent/child in a self-referencing table, but I think I'd look towards a SQL server solution rather then a CF solution for an unknown depth in your table. Do a quick Google and I'm sure that you'll find lots to read.
http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm is one site that I just looked at and could have an answer for you. I don't know what SQL platform you are on, but it sounds like SQL 2005 may make it easier. Search on "Recursive Common Table Expressions".
I gave this a shot and it does group them but not how they need to be displayed. I'm creating a drop-down for each parent and it's children. This displays the parent and child like this:
Countries United States New Hampshire
Food Seafood Fried Clams
The difficulty is creating one row for each relationship, like this:
Countries - United States - New Hampshire
Countries - United States - Massachucetts
Countries - United States - Vermont
Food - Seafood - Fried Clams
Food - Seafood - Lobster
Could this be done with this type of a custom tag or should I look in another direction?
I'm not against re-designing the table(s) for this. The way the data was given to me seemed like it would fit well into a db setup like this and would be easy to maintain. That's not the case so if there aren't any magic potions here I'll re-design this over the weekend and hopefully can move on to something else next week.
I'm using MS Access by the way, so it's pretty limited.
Get rid of the break beside <cf_getChildren id=#pl_id#>
At the end of the loop, check the ID, if it is 0, put a break.
That corrected the breaks, thank you. I don't believe there's a way to write it out the way I had hope (a line for each parent and it's children). I'll change the table(s) around and have another go at this.
Colors Red Blue Green
Cars Dodge Caravan Stratus Intrepid Ford Mustang Bronco Echo Chevy
Countries United States New Hampshire Massachucetts Vermont Maine Rhode Island United Kingdom Japan
Colors - Red
Colors - Blue
Colors - Green
Cars - Dodge - Caravan
Cars - Ford - Mustang
Trying to revisit this a bit. Now I just need to sort the select drop-down but can't seem to find a way. Because it's created on the fly I can't sort it from the query or function (at least I can't think of a way). I'm trying to output this in alphabetical order but it's just outputting in the order the function runs.
It's being displayed like this:
Operating Systems - Vista
Colors - Red
Colors - Blue
I imagine it could be something simple but I've been looking at it to long and can't think straight.
It would be nice if it were that easy, I (of course) tried that. The sort order of the query doesn't matter. The data is not sorted once the function runs. The sort has to come after the select box is populated by the function. I need to get the data at this point and sort it, probably somehow creating a structure or array from this data and sorting that(?).
> It would be nice if it were that easy, I (of course) tried that. The sort order
> of the query doesn't matter. The data is not sorted once the function runs. The
> sort has to come after the select box is populated by the function. I need to
> get the data at this point and sort it, probably somehow creating a structure
> or array from this data and sorting that(?).
<cfset myArr = ArrayNew(1)>
<cfset temp = ArrayAppend(myArr, "#getNameWithParent(pl_id)#")>
<cfset ArraySort(myArr, "textnocase", "asc")>
<cfset myList = ArrayToList(myArr, ",")>
then loop through myList to populate your <select>...
how about that?
Yup, that does do it. After a little tweaking this puppy will be complete. Thanks to the people who chipped in this thread.