CODEWITHSUNDEEP
mysqljoinsql-order-by
APixel Visuals
APixel Visuals

Reputation: 1648

MySQL - Getting a position from a "leaderboard"

I'm trying to construct a query where I would get a leaderboard-like result, then get the position on the leaderboard of a specific player, by their ID, and only if the player isHere = 'true' (stored in another table).

Table here:

 userID  |  isHere |
--------------------
   2    |   true
--------------------
   1    |   true
--------------------
   3    |   false
--------------------
   4    |   true

Table userdata:

   id   |         data            |
------------------------------------
   2    |   {... "points": 5 ...}
------------------------------------
   1    |   {... "points": 10 ...}
------------------------------------
   3    |   {... "points": 2 ...}
------------------------------------
   4    |   {... "points": 28 ...}

Query:

SET
  @row_number = 0;
SELECT
  *
FROM
  (
  SELECT
    (@row_number := @row_number +1) AS num,
    userdata.id,
    userdata.data
  FROM
    userdata
  INNER JOIN
    here ON userdata.id = here.userID
  WHERE
    here.isHere = 'true'
  ORDER BY
    JSON_EXTRACT(userdata.data,
    '$.points') DESC
) AS t
WHERE
  t.id = 1

This returns that num is 1... because for some reason it's sorting by userID/id. I double checked by setting WHERE t.id = as 2, and instead of returning 3, it returns 2... Does anyone know what's wrong with my query?

Note: I did try having the column data be just a BIGINT with the point value, but the same issue happened. So that eliminates JSON_EXTRACT() having any issues (I think?)

Note 2: Running the inner query on its own still orders by the ID with the num column, but displays in PHPMyAdmin in the correct order (Screenshot: https://gyazo.com/73177e79f4fedd4ec7e09ea0e70a9d2b)

Upvotes: 0

Views: 304

Answers (1)

APixel Visuals
APixel Visuals

Reputation: 1648

So here's the query that works:

SET
  @row_number = 0;
SELECT
  *
FROM
  (
  SELECT
    (@row_number := @row_number +1) AS num,
    userdata.id,
    userdata.data
  FROM
    userdata
  INNER JOIN
    (
    SELECT
      userdata.id,
      userdata.data
    FROM
      userdata
    INNER JOIN
      here ON userdata.id = here.userID
    WHERE
      here.isHere = 'true'
    ORDER BY
      JSON_EXTRACT(userdata.data,
      '$.points') DESC
  ) AS t ON userdata.id = t.id
) AS t2
WHERE
  id = ?

The only reason all of this is needed, is due to the order in which MySQL executes parts of queries. Just like parenthesis are used in math in order of operations, I used a SELECT statement here.

Upvotes: 1

Related Questions