If _all_ children _aways_ get published (like your pseudocode indicates), why not just flag the parent category?
If you want to do it all in SQL, try... http://msdn.microsoft.com/en-us/library/ms186243.aspx Jason Durham On Fri, Jan 28, 2011 at 1:16 AM, wabba <[email protected]> wrote: > > 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:341624 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
