<cfquery name="listcategories" datasource="#request.dsn1#" dbtype="ODBC">
Select CatID as itemid ,
Category as description,
ParentId as ParentItemID,
catlevel,
catid,
category,
parentid
From Categories_internal
order by Orderby desc
</cfquery>
<cfmodule template="../customtags/MakeTree.cfm"
Query="#listcategories#"
ReturnVar="listcategories">
<cfloop query="listcategories">
<cfquery name="testdate" datasource="#request.dsn1#" dbtype="ODBC">
Select id,catid_internal
From articles
Where catid_internal = #catid#
and getdate() between starttime and endtime
</cfquery>
<cfif testdate.recordcount gt 0>
<cfif catlevel eq 0>
aux1 = insFld(foldersTree, gFld("#category#", "#request.self#?fuseaction=#fusebox.targetcircuit#.content&catid=#catid#"))
<cfelse>
aux#(catlevel+1)# = insFld(aux#catlevel#, gFld("#category#", "#request.self#?fuseaction=#fusebox.targetcircuit#.content&catid=#catid#"))
</cfif>
</cfif>
</cfloop>
Now the issue I have and Mike D I hope you're reading this, when I write the query like this, whether it's in a stored procedure or not, the make tree tag chokes completely and takes up all processor time.
Select Categories_internal.CatID as itemid ,
Categories_internal.Category as description,
Categories_internal.ParentId as ParentItemID,
Categories_internal.catlevel,
Categories_internal.catid,
Categories_internal.category,
Categories_internal.parentid
From Categories_internal inner join articles on (Categories_internal.catid = articles.catid_internal)
Where getdate() between articles.starttime and articles.endtime
order by Categories_internal.Orderby desc
This would have allowed me to do one query instead of looping over the queries to see which associated category has an article that has expired and shouldn't be used.
I'm using the latest version of your tag Mike. Any insight?
Bob
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
