I am currently working on some sort of a highscore list. But values displayed are individual for each user, ie. limited by constraints.
One constraint that I am currently working on is "being part of a list".
I have to tables in my database:
| Origin | Destination | 234 333 234 453
| ID | Users.ID | Points 1 1 30 2 2 40 3 1 60 4 4 100
|ID | Name | InternalID | 1 damian 234 2 damian2 333 3 damian3 453 4 damian4 555
My goal is the following:
I need to find my rank, but only within the set of connected "values".
I am damian, my ID is 1 and my internal ID is 234. My "connected users" have internal IDs: 333, 453 which map to User IDs 2, 3.
This means, the list of users to be monitored is 1,2,3 but NOT 4 (since not connected to my internal ID).
With this list (1,2,3) I will now check the values, ordered by Points DESC, and find my position in the subset.
My current approach is:
Get the list of Users.ID:
SELECT DISTINCT ID FROM users WHERE InternalID IN ( SELECT Destination FROM connections, users WHERE Origin = users.InternalID AND users.ID = 1)
This list, I will now map to the values:
SELECT Points FROM values WHERE UsersID IN (Query 1) ORDER BY Points DESC
This should result in a sorted list of all values (Points), only of connected users.
But now I need to find the highest / first position in this set, of my own "Points" value, and I don't know how to do this efficiently. I have tried different approaches with FIND_IN_SET like
FIND_IN_SET(Points, GROUP_CONCAT(Points)) but I can't seem to get them to work, also this would result in 4-5 nested queries.