Блог

Как получить рейтинг пользователей в зависимости от баллов репутации.
Исключите роли ‘super_admin’ и ‘staff’.

МОЙ ЗАПРОС:
Когда я делаю свой запрос, включающий всех пользователей, ранжирование работает так, как хотелось, но когда я делаю свой запрос, исключая пользователей, у которых есть роль ‘super_admin’ и ‘staff’, ранжирование неверное.

 SELECT u.id, u.rank 
FROM (
      SELECT u.id, @rownum := @rownum  1 AS rank
      FROM users u, (SELECT @rownum :=0 ) r
      ORDER BY u.reputation DESC, u.register_date ASC
     ) AS u
LEFT JOIN users_role ur ON ur.user_id = u.id
LEFT JOIN roles r ON r.id = ur.role_id
WHERE r.key_role NOT IN ('super_admin','staff')
AND u.id=3
  

ТАБЛИЦЫ:

 users table

ID    REPUTATION     REGISTERED_DATE
1     6000           2018-05-20 14:15:10
2     20             2018-05-22 14:15:10
3     2000           2018-05-25 14:15:10
4     350            2018-05-27 14:15:10
5     14             2018-05-27 19:15:10
6     0              2018-05-28 14:15:10
7     584            2018-05-29 14:15:10
8     54             2018-05-30 14:15:10


users_roles table

ID    USER_ID        ROLE_ID
1     1              1
2     2              2
3     3              3
4     4              3
5     5              3
6     6              3
7     7              3
8     8              3


roles table

ID    NAME              KEY_ROLE
1     Super Admin       super_admin
2     Staff             staff
3     Registered user   registered_user
  

Это основано на вашем коде:

 SELECT id, rank FROM (
  SELECT u.id, @rownum := @rownum  1 AS rank 
  FROM (
      SELECT u.id
      FROM users u
      LEFT JOIN users_roles ur ON ur.user_id = u.id
      LEFT JOIN roles r ON r.id = ur.role_id
      WHERE r.key_role NOT IN ('super_admin','staff')
      ORDER BY u.reputation DESC, u.registered_date ASC
     ) AS u
  ,(SELECT @rownum :=0 ) r
) AS t 
WHERE id=3
  

Смотрите демо
Я включил все условия во внутренний запрос.

Я считаю, что это делает то, что вы хотите:

 SELECT u.id, u.rank 
FROM (SELECT u.id, (@rownum := @rownum   1) AS rank
      FROM users u CROSS JOIN
           (SELECT @rownum := 0 ) r
      WHERE NOT EXISTS (SELECT 1
                        FROM users_role ur JOIN
                             roles r
                             ON r.id = ur.role_id
                        WHERE ur.user_id = u.id AND
                              r.key_role NOT IN ('super_admin', 'staff')
                       )
      ORDER BY u.reputation DESC, u.register_date ASC
     ) u
WHERE u.id = 3;
  

В частности, пользователи с несколькими ролями не повлияют на ранжирование.

Обратите внимание, что в более новых версиях MySQL переменные плохо работают с ORDER BY , поэтому вам нужен дополнительный подзапрос. В новейших версиях вы бы просто использовали оконные функции.