Further to my 2014 answer I finally got back to this question and have built upon my previous approach and it's fatal flaw. I've come up with the following solution, using a SQL Server Stored Procedure to show the logic.
First, the table of movies:
CREATE TABLE [dbo].[usermovies]
([userid] [int], [movieid] [int], [position] [int], [subposition] [int])
And the test data. Note that when we load the data the initial movierank is set in the position column and the subposition is set to 0:
insert into usermovies (userid, movieid, position, subposition)
values (123, 99, 1, 0)
,(123, 98, 2, 0)
,(123, 97, 3, 0)
,(123, 96, 4, 0)
,(123, 95, 5, 0)
,(123, 94, 6, 0)
,(987, 99, 1, 0)
,(987, 98, 2, 0)
,(987, 97, 3, 0)
,(987, 96, 4, 0)
,(987, 95, 5, 0)
,(987, 94, 6, 0)
It is important to understand that the rank of each movie (movierank) is not determined from the position value, but instead from the rank of the row when the records are sorted by position and then by subposition. I created a view to provide the movierank:
CREATE OR ALTER VIEW vwUserMoviesWithRank
as
with userMoviesWithRanks as (
SELECT *
, dense_rank() over (partition by userid order by position asc, subposition asc) as movierank
FROM usermovies
)
SELECT * FROM userMoviesWithRanks
GO
Each user can only have one movie with a given position/subposition value, as this provides the unique movierank. Adding a unique clustered index to the table nicely enforces this rule and also, with sufficient data, would make for faster data access.
CREATE UNIQUE CLUSTERED INDEX [IX_usermovies]
ON [dbo].[usermovies] ([userid] ASC, [position] ASC, [subposition] ASC)
The below stored procedure performs the updates which allow a user's movie rankings to be changed. I've added comments to help explain the logic:
CREATE OR ALTER PROC proc_ChangeUserMovieRank
@userID int,
@movieID int,
@moveToRank int
as
DECLARE @moveFromRank int
DECLARE @movieIDAtNewRank int
DECLARE @positionAtNewRank int
DECLARE @subpositionAtNewRank int
IF @moveToRank<1 THROW 51000, '@moveToRank must be >= 1', 1;
BEGIN TRAN
-- Get the current rank of the movie being moved
SELECT @moveFromRank=movierank FROM vwUserMoviesWithRank WHERE userid=@userID and movieid=@movieID
IF @moveFromRank<>@moveToRank BEGIN
-- Get the position and subposition of the movie we need to shift down the list
-- if this move is decreasing the movie rank then we need to shift the movie at @moveToRank
-- if this move is increasing the movie rank then we need to shift the movie at @moveToRank+1, to accommodate the removal
SELECT @positionAtNewRank=position, @subpositionAtNewRank=subposition
FROM vwUserMoviesWithRank
WHERE userid=@userID and movierank=(@moveToRank + CASE WHEN @moveToRank>@moveFromRank THEN 1 ELSE 0 END)
IF @positionAtNewRank IS NULL BEGIN
-- No movie needs to be updated, so we're adding to the end of the list
-- Our destination is the position+1 of the highest ranked movie (with subposition=0)
SELECT @positionAtNewRank=max(p.position)+1, @subpositionAtNewRank=0
FROM vwUserMoviesWithRank p WHERE p.userid=@userID
END ELSE BEGIN
-- Move down (increase the subposition of) any movies with the same position value as the destination rank
UPDATE m
SET subposition=subposition+1
FROM usermovies m
WHERE userid=@userID AND position=@positionAtNewRank and subposition>=@subpositionAtNewRank
END
-- Finally move the movie to the new rank
UPDATE m
SET position=@positionAtNewRank, subposition=@subpositionAtNewRank
FROM usermovies m
WHERE m.userid=@userID AND m.movieid=@movieID
END
COMMIT TRAN
GO
Here's a test run using the test data above. The movies are listed using the following SELECT statement, I haven't repeated this each time below for the sake of brevity. Here's our movie ranking at the beginning:
SELECT movieid, movierank FROM vwUserMoviesWithRank WHERE userid=123 ORDER BY movierank
movieid movierank
----------- --------------------
99 1
98 2
97 3
96 4
95 5
94 6
Let's move movie 98 to rank 5:
EXEC proc_ChangeUserMovieRank @userID=123, @movieID=98, @moveToRank=5
movieid movierank
----------- --------------------
99 1
97 2
96 3
95 4
98 5
94 6
Move movie 94 to rank 2:
EXEC proc_ChangeUserMovieRank @userID=123, @movieID=94, @moveToRank=2
movieid movierank
----------- --------------------
99 1
94 2
97 3
96 4
95 5
98 6
Move movie 95 to rank 1:
EXEC proc_ChangeUserMovieRank @userID=123, @movieID=95, @moveToRank=1
movieid movierank
----------- --------------------
95 1
99 2
94 3
97 4
96 5
98 6
Move movie 99 to rank 4:
EXEC proc_ChangeUserMovieRank @userID=123, @movieID=99, @moveToRank=4
movieid movierank
----------- --------------------
95 1
94 2
97 3
99 4
96 5
98 6
Move movie 97 to rank 6:
EXEC proc_ChangeUserMovieRank @userID=123, @movieID=97, @moveToRank=6
movieid movierank
----------- --------------------
95 1
94 2
99 3
96 4
98 5
97 6
Move movie 97 to rank 4:
EXEC proc_ChangeUserMovieRank @userID=123, @movieID=97, @moveToRank=4
movieid movierank
----------- --------------------
95 1
94 2
99 3
97 4
96 5
98 6
Move movie 95 to rank 4:
EXEC proc_ChangeUserMovieRank @userID=123, @movieID=95, @moveToRank=4
movieid movierank
----------- --------------------
94 1
99 2
97 3
95 4
96 5
98 6
Which all looks good I think.
Note that following these operations the position/subposition data now looks like this:
select * from vwUserMoviesWithRank WHERE userid=123 order by movierank
userid movieid position subposition movierank
----------- ----------- ----------- ----------- --------------------
123 94 3 0 1
123 99 4 0 2
123 97 4 1 3
123 95 4 2 4
123 96 4 3 5
123 98 6 0 6
The values are quite different from the determined movierank.
As the movie rankings change the position may become the same across a number of rows, such as position 4 above. When this happens more rows will need to be updated when the rankings change, so it is advisable to periodically reset the position and subposition to the movierank value:
UPDATE usermovies
SET position=vwUserMoviesWithRank.movierank, subposition=0
FROM vwUserMoviesWithRank
INNER JOIN usermovies on usermovies.userid=vwUserMoviesWithRank.userid AND usermovies.movieid=vwUserMoviesWithRank.movieid
WHERE usermovies.position<>vwUserMoviesWithRank.movierank OR usermovies.subposition<>0
This works very efficiently and will scale very well and I think it all works, let me know if you think otherwise and I'll take another look (and this time I won't wait 8 years to do so!)
And just to note that I tried to add a SQL Fiddle link here but it appears that they have no SQL Server hosts presently :-/
