Query output help: Display self-referencing table

[Guest]

Hello,

I'm trying to display in a cfoutput a self-referencing table. I'll start by showing some of the data in the table.

There are only 3 columns, pl_id (auto increment id pri-key), pl_name and pl_parent_id (it's 0 if it's a parent, otherwise it's the pl_id value for the parent).

pl_id pl_name pl_parent_id
1 Country 0
2 Food 0
3 US 1
4 Japan 1
5 Hamburger 2
6 Idaho 3
7 Florida 3
8 Cheese 2

What I'm trying to output is something like this:
Country - US - Idaho (3 levels here) or
Country - US - Florida (or if there are only 2 levels like the next line)
Food - Cheese

I've tried using a cfoutput with a cfloop as well as grouping but not having any luck. Could someone clear my clouded head on this one?

thanks much,

Joe

ps. Adobe should really use a fixed width font for these forums, it's impossible to line up table info!

[flavio-VYnUM1]

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.

F.

[Guest]

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
Massachucetts
Vermont
Maine
Rhode Island

Food Seafood Fried Clams
Shrimp
Lobster

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
etc.

Could this be done with this type of a custom tag or should I look in another direction?

[flavio-VYnUM1]

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.

F.

[Guest]

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.

Now:
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

Ideal:
Colors - Red
Colors - Blue
Colors - Green
Cars - Dodge - Caravan
Cars - Ford - Mustang
States -

[Guest]

Hello,

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
States
Countries
Sports teams
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.

[Newsgroup_User]

add ORDER BY pl_name to the end of your qryGetAll


--
Azadi Saryev
Sabai-dee.com
Vientiane, Laos
http://www.sabai-dee.com

[Guest]

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(?).

[Newsgroup_User]

JoeNH2k wrote:
> 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)>
<cfloop query="qryGetAll">
<cfset temp = ArrayAppend(myArr, "#getNameWithParent(pl_id)#")>
</cfloop>
<cfset ArraySort(myArr, "textnocase", "asc")>
<cfset myList = ArrayToList(myArr, ",")>

then loop through myList to populate your <select>...

how about that?

--
Azadi Saryev
Sabai-dee.com
Vientiane, Laos
http://www.sabai-dee.com

[Guest · Accepted solution]

Yup, that does do it. After a little tweaking this puppy will be complete. Thanks to the people who chipped in this thread.

[tmschmitt]

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".

[Guest]

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.