Eric Bourland
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:
My first stab at a CTE query is:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrec t 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
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>
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>
But that gets me this error:Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrec
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
If there's a hard limit of 3 levels, it'd probably be simpler to stick w/joins as gd suggested.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
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
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>
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You may want the where clause
where p.parentID = 0
where p.parentID = 0
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:
Using this cfoutput:
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
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>
Using this cfoutput:
<cfoutput query="getPages">
#getPages.PageTitle#
</cfoutput>
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
ASKER
So far I have:
But I am not clear how I can order the Parents, then the Children in the output:
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
<!--- 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>
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>
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
ASKER
I made progress on this task. =)
I revised the query and the output:
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
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>
<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>
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
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..
It's looking good though!
Let me know if you have anything I can help you with..
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
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
ASKER
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....