ColdFusion 9
SQL Server 2005
Hi friends. Do you have time to consider a problem? I am trying to display a
list of Parent and Child pages, with children arranged under their
respective parents, thus:
Parent
Child
Child
Child
Parent
Child
Child
Child
Grandchild
Grandchild
...etc. There will be no more than three levels of pages: Parent, child,
grandchild.
If possible, I would rather not get into CTE recursion, And it would be very
onerous and forbidding to change my existing data tables. Instead, I am
wondering, is there simply a way to do this in ColdFusion, using a query and
some JOINs, and the correct cfoutput? Here is what I have so far:
<!--- get table columns for parent, child, grandchild records--->
<cfquery name="getPages" datasource="#APPLICATION.dataSource#">
SELECT p.PageID
,p.PageTitle
,p.DateCreated
,p.DateModified
,p.SortOrder
,c.PageID
,c.PageTitle
,c.DateCreated
,c.DateModified
,c.SortOrder
,c2.PageID
,c2.PageTitle
,c2.DateCreated
,c2.DateModified
,c2.SortOrder
,p.parentID
,c.parentID
,c2.parentID
FROM #REQUEST.contentTable# p
left join #REQUEST.contentTable# c on c.parentID = p.pageID
left join #REQUEST.contentTable# c2 on c2.parentID = c.PageID
ORDER BY p.SortOrder, p.PageTitle, c.SortOrder, c.PageTitle, c2.SortOrder,
c2.PageTitle
</cfquery>
And the output:
<!--- display record information --->
<cfoutput query="getPages" group="PageTitle">
<tr>
<td>#getPages.PageID#</td>
<td>#getPages.PageTitle#</td>
<td>#DateFormat(getPages.DateCreated, "mmmm d, yyyy")#</td>
<td>#DateFormat(getPages.DateModified, "mmmm d, yyyy")#</td>
<td>#getPages.SortOrder#</td>
</tr>
</cfoutput>
But, that gets me just a list of the pages, sorted in no particular order:
http://www.hardrockteam.org/managepages2.cfm
Does anyone have any ideas to sort these pages as I noted above?
Thank you for any advice.
Eric
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:350519
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
