One take on reordering data. Given an ID and a direction, we want to reorder rows by updating a position column.
I'm running this on SQL 2000 but it'll be good for 7 and 2005.
We have a listing table with a foreign key to an agent table. We select the position and agentID of the listing row we're looking to move.
DECLARE @position INT, @agentID INT SELECT @position = position, @agentID = agentID FROM listing WHERE listingID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ARGUMENTS.listingID#">
Then depending on whether we're moving the listing up or down we do one of the following.
<cfif ARGUMENTS.direction EQ "down"> UPDATE listing SET position = position - 1 WHERE agentID = @agentID AND position = @position + 1 UPDATE listing SET position = position + 1 WHERE listingID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ARGUMENTS.listingID#"> <cfelseif ARGUMENTS.direction EQ "up"> UPDATE listing SET position = position + 1 WHERE agentID = @agentID AND position = @position - 1 UPDATE listing SET position = position - 1 WHERE listingID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ARGUMENTS.listingID#"> </cfif>
We update the position of the lising that's in the way. Then we update the position of the listing that's moving.
Wack that in a transaction and you should be good to go. Unless of course, someone tries to move the top listing up or the bottom one down!
We could just ensure that the calling code never tries to, but best to check here in case it get's misused.
We'll add a check to see if we're trying to move the bottom listing down...
SELECT @maxPosition = MAX(position) FROM listing WHERE agentID = @agentID IF (@maxPosition != @position) BEGIN UPDATE ... END
... and the same for the top one moving up...
IF (@position != 1) BEGIN UPDATE ... END
So the complete code wrapped in a function might looks like this.
<cffunction name="move"> <cfargument name="listingID"> <cfargument name="direction"> <cfquery datasource="#VARIABLES.DSN#"> BEGIN TRAN DECLARE @position INT, @agentID INT, @maxPosition INT SELECT @position = position, @agentID = agentID FROM listing WHERE listingID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ARGUMENTS.listingID#"> <cfif ARGUMENTS.direction EQ "down"> SELECT @maxPosition = MAX(position) FROM listing WHERE agentID = @agentID IF (@maxPosition != @position) BEGIN UPDATE listing SET position = position - 1 WHERE agentID = @agentID AND position = @position + 1 UPDATE listing SET position = position + 1 WHERE listingID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ARGUMENTS.listingID#"> END <cfelseif ARGUMENTS.direction EQ "up"> IF (@position != 1) BEGIN UPDATE listing SET position = position + 1 WHERE agentID = @agentID AND position = @position - 1 UPDATE listing SET position = position - 1 WHERE listingID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ARGUMENTS.listingID#"> END </cfif> COMMIT TRAN </cfquery> </cffunction>