1

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:

Connections

| Origin | Destination |
234        333
234        453

Values:

| ID | Users.ID | Points
1      1          30
2      2          40
3      1          60
4      4          100

Users:

|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:

Query 1:

    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:

Query 2:

 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.