I would select the entire table in a query sorted by sort order, then loop over that query and do a query of queries to get the children and grandchildren for each record.
On Sat, Mar 24, 2012 at 1:56 PM, Eric Bourland <[email protected]> wrote: > > 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:350523 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
