?
Solved

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

Posted on 2012-03-23
16
Medium Priority
?
538 Views
Last Modified: 2012-03-25
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
Comment
Question by:Eric Bourland
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 1600 total points
ID: 37758411
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
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37758561
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
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 1600 total points
ID: 37758613
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
Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 400 total points
ID: 37758678
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
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37758682
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
 
LVL 52

Expert Comment

by:_agx_
ID: 37758694
If there's a hard limit of 3 levels, it'd probably be simpler to stick w/joins as gd suggested.
0
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 37758781
my suggestion will be to use the recursion

here is the way to do it

http://nstree.riaforge.org/
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 1600 total points
ID: 37758787
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
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37759005
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
 
LVL 39

Accepted Solution

by:
gdemaria earned 1600 total points
ID: 37759037
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 37759041
You may want the where clause


where p.parentID = 0
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37759237
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
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37759435
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
 
LVL 3

Author Comment

by:Eric Bourland
ID: 37762250
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 37762664
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
 
LVL 3

Author Closing Comment

by:Eric Bourland
ID: 37763101
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

DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question