• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 556
  • Last Modified:

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 : <!--- order by parent title, child title --->

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
0
Eric Bourland
Asked:
Eric Bourland
  • 7
  • 6
  • 2
  • +1
5 Solutions
 
gdemariaCommented:
Hey Eric,

Looks like your error is a simple typing error, you have two aliases for this table

 INNER JOIN #REQUEST.contentTable#    p   AS   ParentPageTitle


I think you meant just this...  using "p" as your table alias...

 INNER JOIN #REQUEST.contentTable#    p
0
 
Eric BourlandAuthor Commented:
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:

 <!--- display record information --->
 <cfoutput query="getPages">
   
   <cfif ParentID IS 0> <!--- if ParentID is 0, then web page is a parent page --->  
   Parent: #getPages.PageTitle#

   <cfelseif ParentID IS 100000>   <!--- if ParentID is 100000, then web page is omitted from navigation --->    
   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....
0
 
gdemariaCommented:
Do you have more than two levels?  

I regular join can only show you parent and child.   If you are guaranteed not to have more than 3 levels, you can add another join for the 3rd level.  

If you can have any number of levels, you have to do some type of heirarchical query..  Oracle does this well,  all others are clumsy or at least challenging.

What's your database and version?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
_agx_Commented:
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 you need to display an arbitrary number of levels, then yes a CTE is the best approach for sql server 2005+.  However, if the number of levels is fixed then using JOINs might be simpler.
0
 
Eric BourlandAuthor Commented:
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.
0
 
_agx_Commented:
If there's a hard limit of 3 levels, it'd probably be simpler to stick w/joins as gd suggested.
0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
my suggestion will be to use the recursion

here is the way to do it

http://nstree.riaforge.org/
0
 
gdemariaCommented:
agreed, just add another join for your 3rd level.

However, looking at your current select statement.   Assuming "P" is for parent and "C" is for child, you are fetching on parents that have children.. If a parent does not have a child, you are excluding them because of your inner join.  Is that what you want?

I think you want...

From PARENT P
  left join CHILD C2 on c2.parentID = P.pageID
  left join CHILD C3 on c3.parentID = c2.PageID
0
 
Eric BourlandAuthor Commented:
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
0
 
gdemariaCommented:
Hopefully this will better illustrate the concept...

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
0
 
gdemariaCommented:
You may want the where clause


where p.parentID = 0
0
 
Eric BourlandAuthor Commented:
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
0
 
Eric BourlandAuthor Commented:
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
0
 
Eric BourlandAuthor Commented:
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
0
 
gdemariaCommented:
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..
0
 
Eric BourlandAuthor Commented:
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
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 7
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now