Hi all, read a lot, post rarely. Anyway, try to keep this simple as possible
- I have a database of items stored within nested categories using a pivot
table to establish parent/childs for the categories (the can virtually nest
indefinitely, one child cat can be under multiple parents, etc). There are
roughly 30,000 items within 3,000 categories. There are a handful of top
level categories. One of the top level categories gets published to an
external site regularly, while the others don't. I use a bit flag on the
items to determine which items get published and which don't, so that only
the items we want published get transferred.
Currently I use a nested CF custom tag to set the publish flags - I feed it
a top level categoryID, it sets Publish=1 on the items in that category,
then calls itself in a loop with all of the CategoryID where the
ParentCatID=CategoryID. Something like this:
(cf_pushlishtree custom tag):
<cfquery>
Update item SET Publish=1 WHERE CategoryID=CurrentCat
</cfquery>
<cfquery name="getsubcats">
Select CategoryID FROM CatPivot WHERE ParentCatID=CurrentCat
</cfquery>
<cfloop query="getsubcats">
<cf_publishtree CurrentCat="#CurrentCat#">
</cfloop>
...It works fine but is slow and will timeout before it finishes, and I
can't restart it where it leaves off. I would think this could be done right
in MS SQL by having a nested function or stored procedure that can do the
update and then loop itself from the select, but I can't figure out how - I
don't think an UPDATE is allowed in a function, and a stored procedure can't
be called from a select statement. I considered adding a "TopCategoryID" to
the item or category table as it would make publishing very simple, but we
can't as categories can exist anywhere in the category structure, often
under multiple parents simultaneously.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341608
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
