How to auto assign ordinal ranking in mysql with as minimal queries for new rows?

Question

+----+---------+---------+-----------------+
| id | view_id | user_id | ordinal_ranking |
+----+---------+---------+-----------------+
|  1 |      10 |       1 |               1 |
|  2 |      10 |       2 |               1 |
|  3 |      11 |       1 |               2 |
|  4 |      11 |       2 |               2 |
+----+---------+---------+-----------------+

So suppose I want to insert new rows for view_id 12 and for both user_id 1 and 2, these 2 new rows should have ordinal ranking of 3.

How do I do that with minimal number of queries?

Bear in mind, I have many users who may all have different number of views assigned.

Another corollary to the problem is when I need to delete a particular view, say view 10, all the users affected should have the ordinal ranking re-ordered.

How do I do that also with minimal number of queries?

EDIT

A comment was made about the number of views per user. I would like to add that you may safely assume that there is a users table and one column stores the number of views for each user record.

I am also okay if the ordinal ranking is zero-based, ie the ranking is 0, 1, 2, 3, etc

I also prefer not to use stored procedures.

EDIT2:

+----+---------+---------+
| id | name | view_count |
+----+---------+---------+
|  1 |     Sal |       2 |
|  2 |     Sam |       2 |
+----+---------+---------+

Is the users table

the ordinal_ranking merely reflects the personal choice of a user with regards to the views she can view.

E.g. user 1 Sal likes to have view 10 to be ranked higher than view 11.

Answer 1

If you are content with adding and deleting a view_id per user_id, here is what I see

Adding View 12 To User 10

I see three queries

• Insert new view into user with BogusRank
• Move all rank down below NewRank
• Change BogusRank to New Rank for new view

Here is the proposed code

SET @view_id = 12;
SET @user_id = 10;
SET @newrank = 3;
SET @defrank = 999999999;
INSERT INTO userviewranks
(view_id,user_id,ordinal_ranking)
VALUES (@view_id,@user_id,@defrank);
UPDATE userviewranks SET ordinal_ranking = ordinal_ranking + 1
WHERE view_id = @view_id AND user_id = @user_id AND ordinal_ranking >= @newrank;
UPDATE userviewranks SET ordinal_ranking = @newrank
WHERE view_id = @view_id AND user_id = @user_id AND ordinal_ranking = @defrank;

Deleting View 12 From User 10

I see three queries

• Retrieve Row With View to Delete
• Move All View Up in Rank
• Delete Row with Old View

Here is the proposed code

SET @view_id = 12;
SET @user_id = 10;
SELECT id,ordinal_ranking INTO @id_to_zap,@rank_to_zap FROM userviewranks
WHERE view_id = @view_id AND user_id = @user_id;
UPDATE userviewranks SET ordinal_ranking = ordinal_ranking - 1
WHERE view_id = @view_id AND user_id = @user_id AND ordinal_ranking > @rank_to_zap;
DELETE FROM userviewranks WHERE id = @id_to_zap;

Answer 2

Assuming the table is called users_access_views and the primary key id is auto increment and assuming there is a users table,

Scenario 1a) Insert new view and have it be the last view of all the users who has at least access to 1 view

To insert a new view_id regardless of how many views each user already is assigned to and this new view_id is 3 and ensure the ordinal ranking is obeyed,

INSERT INTO users_access_views (view_id, user_id, ordinal_ranking) 
SELECT 3, user_id, count(id) + 1 from users_access_views group by user_id;

Scenario 1b) Insert new view and have it be the last view of all the selected users regardless if they already have any views

If on the other hand, you want to add selected users and there is a chance some of these users do not already have any access to any views, let us assume that the users involved are user 1, 2, 3

INSERT INTO users_access_views (view_id, user_id, ordinal_ranking) 
SELECT 3, u.id, count(uav.id) + 1 from users_access_views uav
  RIGHT JOIN users u ON uav.user_id = u.id 
WHERE u.id in (1, 2, 3) 
GROUP BY u.id;

Scenario 2) Delete existing view and reorder the ordinal_ranking after the deletion

To delete an existing view_id regardless of how many each user already is assigned to and this removed view_id is 2 and ensure the ordinal ranking is obeyed,

-- delete all the view_id 2 first

DELETE FROM users_access_views where view_id = 2;

-- update the ordinal ranking 

UPDATE users_access_views ua4 INNER JOIN

-- this subquery temp table inspired by http://stackoverflow.com/a/25763224/80353    
(SELECT ua1.id, ua1.user_id, ua1.view_id, count(ua2.id) + 1 as rank
FROM  users_access_views ua1 LEFT JOIN users_access_views ua2 ON 
ua1.ordinal_ranking>ua2.ordinal_ranking and ua1.user_id = ua2.user_id 
GROUP BY  ua1.id, ua1.user_id, ua1.view_id) ua3 

ON ua3.id = ua4.id 
SET
  ua4.ordinal_ranking = ua3.rank;

If need be, have another query that updates the view_count in the users table after each scenario.