Are you using Oracle? If so, I usually write queries like that using the
CONNECT BY clause. Here's the query I wrote to return the results you are
looking for:
<cfquery name="nested" datasource="#request.maindsn#">
SELECT
p.pageid
, lpad(' ',3*(level-1))||p.pagetitle pagetitle
FROM pages p
start with parentid = 0
connect by parentid = prior pageid
</cfquery>
I created the table with the data you provided and ran the query. Here were
my results: (The number 3 in the lpad function controls the indentation of
each level.
PAGEID PAGETITLE
---------- ------------------------------
1 Home
2 Main 1
3 Sub 1a
4 Sub 2a
5 Main 2
6 Sub 1b
7 Sub 2b
I prefer the CONNECT BY clause over the method in the book. The method in
the fusebox book needs 2 tables. And for each level of nesting you need to
add another join. The CONNECT BY clause will automatically go as deep as
necessary. The problems with the CONNECT BY are: 1) I believe it only works
in Oracle. 2) It can be (even by Oracle's own admission) a little hard to
understand.
Ralph
-----Original Message-----
From: Hans Omli [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 17, 2001 6:12 PM
To: Fusebox
Subject: OT: Nested Trees in a database
I need some help with nested trees in a database, as discussed in chapter 9
of "Fusebox: Methodology and Techniques". The following code, based off the
code in the book, doesn't return a nested tree. Instead, it returns the
root page's pageid and pagetitle several times.
<cfquery name="nested" datasource="#request.maindsn#">
SELECT p.pageid, p.pagetitle
FROM pages p, pages p1, pages p2
WHERE p1.parentid = 0
AND p1.pageid = p.pageid
AND p2.parentid = p1.pageid
ORDER BY p.pagetitle
</cfquery>
My tables are based on:
<cfquery datasource="#request.maindsn#">
CREATE TABLE pages
(
pageid INTEGER NOT NULL,
parentid INTEGER NOT NULL,
pagetitle VARCHAR(50) NOT NULL
);
</cfquery>
I have the following records in the table:
PAGEID PARENTID PAGETITLE
1 0 Home
2 1 Main 1
3 2 Sub 1a
4 2 Sub 2a
5 1 Main 2
6 5 Sub 1b
7 5 Sub 2b
Thus, the output I am expecting is:
Home
Main 1
Sub 1a
Sub 2a
Main 2
Sub 1b
Sub 2b
What am I doing wrong?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
