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 must...@wabba.net 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
