|
If that’s SQL 2005, you can use a
recursive SQL query and deal with it as a flat result set instead of having to
use recursion in CF. Then you can create a display page that displays a
treeview of the results without requiring any fancy recursion. After that, it’s
plain old CRUD pages using a single primary key. We do something similar, and
it’s a LOT cleaner than doing the recursion in CF. Here’s a
stripped down example of recursion from our SQL code. It assumes you have a
table with the definition: sections (section_id INT, parent_section_id INT, nm VARCHAR(100), sort_order INT) The fancy sort calculation in this query
is done in order to preserve ordering that we maintain internally. It could
just as easily be replaced with the “lvl” variable if you don’t
care about custom ordering. /* Recursively query the sections */ WITH sections_rec (section_id, parent_section_id, nm,
lvl, sort) AS ( -- Anchor member definition SELECT section_id, NULL, nm, 1 AS lvl, CONVERT(VARCHAR, REPLICATE('0',
6 - LEN(CONVERT(VARCHAR, ISNULL(sort_order, 0))))
+ CONVERT(VARCHAR, ISNULL(sort_order, 0))) FROM sections WHERE parent_section_id IS NULL UNION ALL -- Recursive member definition SELECT s.section_id, s.parent_section_id, s.nm, sr.lvl + 1, CONVERT(VARCHAR, sr.sort
+ '|' +
REPLICATE('0',
3 - LEN(CONVERT(VARCHAR, ISNULL(s.sort_order, 0))))
+ CONVERT(VARCHAR, ISNULL(s.sort_order, 0))) FROM sections s INNER JOIN sections_rec sr ON s.parent_section_id = sr.section_id ) -- Statement that executes the CTE SELECT * FROM sections_rec ORDER
BY sort That will retrieve a flat query with all
sections, ordered perfectly for display in a treeview. HTH, Roland From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Phillip Senn A few months ago, back when I thought I had a handle on
this cfc thing, I posted the code to what I thought would be a model ColdFusion
component. One kind reader (whose name will go unmentioned (Matt)),
replied gently correcting practically every single line of code except
</cfcomponent> and then whimsically stated "The rest looks ok to
me!" So I'm back for more fun and merriment at my own expense,
but this time with what is probably a design pattern. If I were to name it, I would call it the "Russian
Doll" design pattern, after the little dolls that are inside other dolls. OK, so since I think it's better to speak in code instead
of through analogies, I've created a SQL script and posted it to: http://www.aacr9.com/RussianDoll Q: How would you put together a maintenance screen for a
location table, where a location is in another location? Feel free to rename any fields to conform to the new OO
way of thinking. I understand that using the word "Parent" is
passé. My plan is to develop the cfm and cfc pages for this
particular type of table layout. Here's an abbreviated view: LocationID,Type,Name,ParentID 1 Country 2 Region North-East 1 3 Region South 1 4 Region 5 Region West 1 6 State 7 State 8 State 9 State 10 State 11 State 12 State 13 State 14 State 15 City 16 City 17 City 18 City New Haven 7 19 City 20 City 21 City 22 City 23 City 24 City 25 Store myStore 24 26 Floor 1st Floor 25 27 Floor 2nd Floor 25 28 Room Dining 27 29 Room Kitchen 27 So then I would want to show in a dropdown: Dining, 2nd Floor, myStore, Kitchen, 2nd Floor, myStore, Running the SQL code will speak better than me explaining
it. Go to http://www.aacr9.com/RussianDoll And see what I'm trying to do. You are subscribed to cfcdev. To unsubscribe, please follow the instructions at http://www.cfczone.org/listserv.cfm CFCDev is supported by: Katapult Media, Inc. We are cool code geeks looking for fun projects to rock! www.katapultmedia.com An archive of the CFCDev list is available at www.mail-archive.com/[email protected] You are subscribed to cfcdev. To unsubscribe, please follow the instructions at http://www.cfczone.org/listserv.cfm CFCDev is supported by: Katapult Media, Inc. We are cool code geeks looking for fun projects to rock! www.katapultmedia.com An archive of the CFCDev list is available at www.mail-archive.com/[email protected] |
- [CFCDEV] Russian Doll design pattern Phillip Senn
- RE: [CFCDEV] Russian Doll design pattern Roland Collins
- Re: [CFCDEV] Russian Doll design pattern Sammy Larbi
