Re: Recursively Delete Records

["Kapitaine"]

Hey guys, thanks for your great help.

 It all got me thinking and I have been searching the Internet for some 
recursion examples. Basing my script on one I found out there which creates a 
site map, I created this one which creates a list of all the IDs and then does 
the remove SQL after.

 It seems to work great! Just thought I'd run it past you guys to see if you 
can spot any potential pitfalls.

 Hope it also coems in handy if you ever need to do a similar task!

 Many thanks again - you're always such a great help here.
 Mickey.

 PS - The script doesn't actually delete the items from the DB but instead sets 
a column value to "1" which in my Application means it's deleted, but this 
could easily be changed.

 <cffunction name="removeItems" access="remote" hint="Sets an item to removed." 
returntype="struct">
        <cfargument name="item_id" type="numeric" required="yes" default="0" />
        <cfargument name="level" type="numeric" required="yes" default="0" />
        
                <cfset var childrensChildren = "" />
                <cfset var firstChildren = "" />
                <cfset var cfc_info = structNew() />
                
                        <cftransaction action="begin">
                                <cftry>
                   
                                        <cfif arguments.level eq 0>
                                                <cfset variables.itemIdList = 
"" />
                                        </cfif>
                                                        
                                        <cfquery name="firstChildren" 
datasource="#request.dsn#" 
username="#request.username#" password="#request.password#">
                                        SELECT item_id, item_parent_id, 
item_token, item_title, item_order, 
item_deleted, item_uuid
                                        FROM #request.tbl_items#
                                        WHERE item_parent_id = <cfqueryparam 
value="#arguments.item_id#" 
cfsqltype="cf_sql_integer" />
                                        ORDER BY item_order
                                        </cfquery>
                                        
                                        <cfset variables.itemIdList = 
variables.itemIdList />
                                        
                                                <cfloop query="firstChildren">
                                                        <cfquery 
name="childrensChildren" datasource="#request.dsn#" 
username="#request.username#" password="#request.password#">
                                                        SELECT item_id, 
item_parent_id, item_token, item_title, item_order, 
item_deleted
                                                        FROM #request.tbl_items#
                                                        WHERE item_parent_id = 
<cfqueryparam value="#firstChildren.item_id#" 
cfsqltype="cf_sql_integer" />
                                                        ORDER BY item_order
                                                        </cfquery>
                                                        
                                                        <cfif 
childrensChildren.recordcount gt 0>
                                                                <cfset 
removeItems(arguments.item_id = firstChildren.item_id, level = 
arguments.level + 1) />
                                                        </cfif>
                                                        
                                                        <cfset 
variables.itemIdList = variables.itemIdList & 
firstChildren.item_id & "," />
                                                </cfloop>
                                        
                                        <cfif arguments.level eq 0>
                                                <cfset variables.itemIdList = 
variables.itemIdList & arguments.item_id />
                                                
                                                <cfloop index="itemId" 
list="#variables.itemIdList#" delimiters=",">
                                                        <cfquery 
name="removeItem" datasource="#request.dsn#" 
username="#request.username#" password="#request.password#">
                                                        UPDATE 
#request.tbl_items#
                                                        SET item_deleted = 
<cfqueryparam value="1" cfsqltype="cf_sql_integer" />
                                                        WHERE item_id = 
<cfqueryparam value="#itemId#" 
cfsqltype="cf_sql_integer" />
                                                        </cfquery>
                                                </cfloop>
                                                
                                                <cftransaction action="commit" 
/>
                                                <cfset cfc_info.type = "ok" />
                                                <cfset cfc_info.message = "This 
item and its children have been 
removed.">

                                                <cfreturn cfc_info />
                                        </cfif>
                                        
                                        <cfcatch type="any">
                                                <cftransaction 
action="rollback" />
                                                <cfset cfc_info.type = "error" 
/>
                                                <cfset cfc_info.message = 
cfcatch  />
                                        </cfcatch>
                                        
                                </cftry>
                        </cftransaction>
        </cffunction>