asked on

Display a list of Parent and Child pages by Sort Order, with children arranged under their respective parents

ColdFusion 9
SQL Server 2005

Hi. I am trying to display a list of Parent and Child pages, with children arranged under their respective parents. metropc1 suggested CTE recursion, and so I have been reading up on CTE recursion here:

http://msdn.microsoft.com/en-us/library/ms186243.aspx

This also seems like a good way to build navigation menus and breadcrumbs.

That looks like exactly what I need to do to set up CTE recursion. I looked in my Forta CF9 books for some mention of CTE recursion -- but did not find any.

(Do I need to make adjustments in SQL Server 2005 to get CTE recursion to work? If so, I might not be able to use CTE, because I have a shared, hosted SQL Server database.)

So, I'm going to start on this. Here is the basic query that requests ParentID and ChildID:

<cfquery name="getPages" datasource="#APPLICATION.dataSource#">
SELECT c.PageID
,c.PageTitle
,c.DateCreated
,c.DateModified
,c.ParentID
,c.SortOrder
,p.PageTitle AS ParentPageTitle
FROM #REQUEST.contentTable# c
LEFT JOIN #REQUEST.contentTable# p
ON p.PageID = c.ParentID
<!--- order by parent title, child title --->
ORDER BY p.PageTitle, c.PageTitle
</cfquery>

And the output:

 <!--- display record information --->
 <cfoutput query="getPages">
 
 <!--- if ParentID is 0, then web page is in top-level navigation --->
   
   <cfif ParentID IS 0>
   This page is in Top Level Navigation
   
   <!--- if ParentID is 100000, then web page is omitted from navigation --->     
   <cfelseif ParentID IS 100000>
   This page is not in Navigation   
     
   <!--- otherwise, display ParentPageTitle from query getPages --->     
   
   <cfelse>
   #getPages.ParentPageTitle#
   </cfif>
   
 </cfoutput>

Open in new window

My first stab at a CTE query is:

<cfquery name="getPages" datasource="#APPLICATION.dataSource#">
SELECT c.PageID
,c.PageTitle
,c.DateCreated
,c.DateModified
,c.ParentID
,c.SortOrder
,p.PageTitle
FROM #REQUEST.contentTable# c
INNER JOIN #REQUEST.contentTable# p AS ParentPageTitle
ON p.PageID = c.ParentID
<!--- order by parent title, child title --->
ORDER BY p.PageTitle, c.PageTitle
</cfquery>

Open in new window

But that gets me this error:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'AS'.

The error occurred in C:/websites/www.hardrockteam.org/admin/managePages.cfm: line 10
8 : ,p.PageTitle
9 : FROM #REQUEST.contentTable# c
10 : INNER JOIN #REQUEST.contentTable# p AS ParentPageTitle
11 : ON p.PageID = c.ParentID
12 : 

ColdFusion did not want to JOIN a table #REQUEST.contentTable# AS a column name ParentPageTitle.

Is CTE Recursion the best way to display a list of Parent and Child pages by Sort Order, with children arranged under their respective parents? If so, does anyone have a suggestion to edit my CTE query?

Thank you for your advice!

Eric

SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eric Bourland

ASKER

I see that now. So, I start with this corrected query:

<cfquery name="getPages" datasource="#APPLICATION.dataSource#">
SELECT c.PageID
,c.PageTitle
,c.DateCreated
,c.DateModified
,c.ParentID
,c.SortOrder
,p.PageTitle
FROM #REQUEST.contentTable# c
INNER JOIN #REQUEST.contentTable# p
ON p.PageID = c.ParentID
<!--- order by parent title, child title --->
ORDER BY p.PageTitle, c.PageTitle
</cfquery>

Open in new window

Then I need to change the format of the output to display:

Parent Page
Child
Child
Child
Parent Page
Child
Child
Child
(Could I add a third level?)
Parent Page
Child
Child
Child
Child
Child

Trying this as a first stab:


<cfoutput query="getPages">

<cfif ParentID IS 0> 
Parent: #getPages.PageTitle#

<cfelseif ParentID IS 100000> 
Page is not in navigation

<cfelse>
Child: #getPages.PageTitle#
</cfif>
</cfoutput>

Hmmm. That gets me only a list of Child Pages; Parent pages are omitted.

I think I need to change my query. =) Looking at this again....

SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eric Bourland

ASKER

I use SQL Server 2005. Currently I have only two levels. I usually try to talk clients into using just two levels.

But I would like to build something that would accommodate three levels. If that means adding one more JOIN, I think that would be OK.

It is feasible for me to upgrade to SQL Server "Express" 2012, though I would rather not undertake that task right now, unless I absolutely have to. =)

I am guessing I need to change my query in order to get the output that I want.

_agx_

If there's a hard limit of 3 levels, it'd probably be simpler to stick w/joins as gd suggested.

Coast Line

my suggestion will be to use the recursion

here is the way to do it

http://nstree.riaforge.org/

SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eric Bourland

ASKER

Dear randhawa,

The nstree application looks very useful, and I read through a lot of its documentation just now. I think I will implement that application in a future project. I don't have time right now to set up new tables, populate them with the correct data, and change my content management system and display pages. That's too ambitious for me to handle right now. But, thank you for the idea.

I am hoping to just simply manage and display up to three levels of navigation:

parent p
child1 c1
child2 c2

without changing any data tables. =) I realize this might not be possible.

gdemaria,

So, my query becomes:

<cfquery name="getPages" datasource="#APPLICATION.dataSource#">
SELECT c.PageID
,c.PageTitle
,c.DateCreated
,c.DateModified
,c.ParentID
,c.SortOrder
,p.PageTitle

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.PageTitle, c.PageTitle, c2.PageTitle
</cfquery>

Open in new window

Hmmm. No. That gives weird results. I think I am missing a basic concept here.

Can I do this with only one contentTable? Meaning, all records (web pages) are in one table, with these columns:
PageID
PageTitle
PageContentLeft
PageContentRight
DateCreated
DateModified
ParentID
SortOrder

A parent page has ParentID=0. A child page has ParentID=(whatever is the PageID of the Parent page).

Will this work for three levels of pages? It seems to me that it should work, and I just need to get the right query and output to display the three levels. Am I at least thinking about this correctly?

Thanks as always. =)

Eric

ASKER CERTIFIED SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gdemaria

You may want the where clause

where p.parentID = 0

Eric Bourland

ASKER

I get it. Of course.

I have to include the other variables too: DateCreated, DateModified, and SortOrder, or else they will be undefined in the output:

<!--- get variables 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

FROM #REQUEST.contentTable# p
left join #REQUEST.contentTable# c on c.parentID = p.pageID
left join #REQUEST.contentTable# c2 on c2.parentID = c.PageID
where p.parentID = 0
ORDER BY p.PageTitle, c.PageTitle, c2.PageTitle
</cfquery>

Open in new window

Using this cfoutput:

 <cfoutput query="getPages">
 #getPages.PageTitle#
</cfoutput>

Open in new window

Gets me a list of records where p.parentID = 0. The result is here:

http://www.hardrockteam.org/managepages2.cfm

OK, I need to distingush among parents, children, and grandchildren. So I need to edit the cfoutput. I'm working on this next.

I'll let you know what I come up with. I think I need to use something besides the where p.parentID = 0 clause. More soon....Thanks again.

Eric

Eric Bourland

ASKER

So far I have:

<!--- get variables 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

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.PageTitle, p.SortOrder, c.PageTitle, c.SortOrder, c2.PageTitle, c2.SortOrder
</cfquery>

Open in new window

But I am not clear how I can order the Parents, then the Children in the output:

<table>
  <tr style="background-color:#ebebeb;">
  <td><strong>PageID</strong></td>
  <td><strong>Page Title</strong></td>
  <td><strong>Date Created</strong></td>
  <td><strong>Date Modified</strong></td>
  <td><strong>Sort Order</strong></td>
  </tr>

 <!--- 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>
</table>

Open in new window

You can see the result here: http://www.hardrockteam.org/managepages2.cfm

I don't think the Group attribute is going to help -- though I have been trying it. I have a feeling I need to change the ORDER BY clause in my query. gdemaria, I'm not sure why you used where p.parentID = 0?

Thanks again for your help.

Eric

Eric Bourland

ASKER

I made progress on this task. =)

I revised the query and the output:

<!--- get table columns for parent, child, grandchild records--->
<cfquery name="getPages" datasource="#APPLICATION.dataSource#">
SELECT
       p.PageID     AS PageIDLevel1
      ,p.PageTitle  AS PageTitleLevel1
      ,p.SortOrder  AS SortOrderLevel1
      ,p.ParentID   AS ParentIDLevel1
      ,c.PageID     AS PageIDLevel2
      ,c.PageTitle  AS PageTitleLevel2
      ,c.SortOrder  AS SortOrderLevel2
      ,c.parentID   AS ParentIDLevel2
      ,gc.PageID    AS PageIDLevel3
      ,gc.PageTitle AS PageTitleLevel3
      ,gc.SortOrder AS SortOrderLevel3
      ,gc.parentID  AS ParentIDLevel3
      ,p.DateCreated AS DateCreated
      ,p.DateModified AS DateModified
FROM #REQUEST.contentTable# p
                  LEFT JOIN #REQUEST.contentTable#  c ON c.parentID = p.pageID
                  LEFT JOIN #REQUEST.contentTable#  gc ON gc.parentID = c.PageID
WHERE   ISNULL(p.parentID, 0) = 0
ORDER BY p.SortOrder, c.SortOrder, gc.SortOrder
</cfquery>

Open in new window

<table>
  <tr style="background-color:#ebebeb;">
  <td><strong>PageID</strong></td>
  <td><strong>Page Title</strong></td>
  <td><strong>Date Created</strong></td>
  <td><strong>Date Modified</strong></td>
  </tr>

 <!--- display record information --->
 <cfoutput query="getPages" group="PageTitleLevel1">
 
  
  <tr>
   <td>#getpages.PageIDLevel1#</td>   
   <td><cfoutput group="SortOrderLevel1">
    #PageTitleLevel1#<br>
    <cfoutput group="SortOrderLevel2">
        <cfif len(pageTitleLevel2)>
            ---#PageTitleLevel2#<br>
        </cfif>
        <cfoutput group="SortOrderLevel3">
        <cfif len(PageTitleLevel3)>
            -----#PageTitleLevel3#<br>
        </cfif>
        </cfoutput>
    </cfoutput>
</cfoutput>
</td>   
   <td>#DateFormat(getpages.DateCreated, "mmmm d, yyyy")#</td>
   <td>#DateFormat(getpages.DateModified, "mmmm d, yyyy")#</td>
  </tr>
 
  
 </cfoutput>
</table>

Open in new window

And the result is here:
http://www.hardrockteam.org/managepages2.cfm

It seems to work well. =) I worked with it for a long time, then asked for help on the cf-talk list. The part of the query I kept missing was:

WHERE ISNULL(p.parentID, 0) = 0

but that makes perfect sense now that I see it.

I'll come back and close this question. I hope you all are having a relaxing weekend. Thank you again.

Eric

gdemaria

Hey Eric, sorry I wasn't around to help more. I don't know why but I didn't receive any email notifications that you were adding posts to this question.

It's looking good though!

Let me know if you have anything I can help you with..

Eric Bourland

ASKER

gdemaria, _agx_, randhawa, thank you as always. gdemaria, I see where you were going with this:

SELECT p.PageID parentID
,p.PageTitle parentTitle
,c.PageID childID
,c.PageTitle childTitle
,c2.PageID grandChildID
,c2.PageTitle grantChildTitle
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.PageTitle, c.PageTitle, c2.PageTitle

And that is very similar to the code I finally used.

This solution makes sense to me, and it is a solution I have needed for a long time now, but never got around to resolving. =) Thank you again. Hope you are having a great day.

Eric