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
530 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
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 400 total points
Comment Utility
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
Comment Utility
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 400 total points
Comment Utility
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
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 100 total points
Comment Utility
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
Comment Utility
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_
Comment Utility
If there's a hard limit of 3 levels, it'd probably be simpler to stick w/joins as gd suggested.
0
 
LVL 15

Expert Comment

by:myselfrandhawa
Comment Utility
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 400 total points
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
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 400 total points
Comment Utility
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
Comment Utility
You may want the where clause


where p.parentID = 0
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now