3
+----+---------+---------+-----------------+
| 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.

5
  • 1
    Your question is not clear. Where are the number of views per view_id ? You are showing the ordinal_ranking but you have not told us what table/column has the number of views. Jan 20, 2015 at 1:37
  • @RolandoMySQLDBA is the number of views per user important? if so, you can assume that the users table has a field that stores the number of views for each user record
    – Kim Stacks
    Jan 21, 2015 at 3:21
  • Please include the table design of the user table. Jan 21, 2015 at 3:30
  • Please also explain what is being ranked. Having id 1 and 2 with ordinal_ranking=1 and id 3 and 4 with ordinal_ranking=2 does not tell us what the views counts are, or whether the ranking is based on the user, the views, or a combination of both. Jan 21, 2015 at 3:34
  • Question updated to add in users table and to indicate what the ordinal ranking represents
    – Kim Stacks
    Jan 21, 2015 at 4:33

2 Answers 2

1
+50

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;
2
  • My question states "When I want to insert new rows for view_id 12 and for both user_id 1 and 2", the new rows for view_id 12 and for both users should be 3. In other words, not just update for 1 user, but multiple users. and bear in mind, it is entirely possible that some users have more views hence when adding view_id 12 it may not be 3 for certain users.
    – Kim Stacks
    Jan 23, 2015 at 7:26
  • I solved it. I will award you the 50 points anyway. Thank you Rolando
    – Kim Stacks
    Jan 23, 2015 at 14:26
1

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.

2
  • Very sweet solution, especially the delete query. +1 !!! Jan 23, 2015 at 15:44
  • Your are welcome, and thank you for the bounty !!! Jan 24, 2015 at 1:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.