0

I have a simple permission system with 3 tables: users, permissions and permission_user. I would like to get all granted permissions with information who it has been granted to. The results should be ordered by:

  1. The number of permissions for user (his power), strongest first
  2. The date user has been created, oldest first
  3. The weight of permission (weight column), smallest first

Here's my query:

  SELECT `users`.`email`,
         `permissions`.`key`
    FROM `users`
    JOIN `permission_user` ON `permission_user`.`user_id` = `users`.`id`
    JOIN `permissions` ON `permissions`.`id` = `permission_user`.`permission_id`
ORDER BY (
             SELECT COUNT(*)
             FROM `permission_user`
             WHERE `user_id` = `users`.`id`
         ) DESC,
         `users`.`created_at` ASC,
         `permissions`.`weight` ASC;

It works fine, but I'm wondering if there is any way to do it without a subquery or counting total permissions granted. It's something that is present in the results set, I could just count unique user ID from that, but is that possible?

Robo Robok
  • 21,132
  • 17
  • 68
  • 126
  • Count(Distinct user_id) might be what you are looking for - this looks similar to what you are doing - http://stackoverflow.com/questions/5737628/mysql-count-distinct – Giles Jan 20 '15 at 10:11
  • @Giles I tried to group by `permission_user.id` (this is the only column I can group by, because I need all records) and to count `permission_user.user_id`, but I'm getting 1 in all records. – Robo Robok Jan 20 '15 at 10:20

1 Answers1

0

You can not do it without subquery.

You can add column cnt_permissions to users table and recalculate it after create or update user permissions. So, you can use it to order in query.

But if you not like that. This SQL query work faster, because it do subquery only once.

SELECT u.`email`,
       p.`key`
FROM (SELECT COUNT(`permission_user`.`permission_id`) as cnt, `user_id` 
      FROM `permission_user`
      GROUP BY `user_id`) as pucnt
LEFT JOIN `users` as u on u.`id` = pucnt.`user_id`
JOIN `permission_user` as pu on pu.`user_id` = u.`user_id`
LEFT JOIN `permissions` as p on p.`id` = pu.`permission_id`
ORDER BY 
      pucnt.cnt DESC,
      u.`created_at` ASC,
      p.`weight` ASC;
Animir
  • 1,121
  • 10
  • 23
  • Is there no way to avoid subquery at all? – Robo Robok Jan 20 '15 at 10:42
  • Try this. (I do not check this query, but look in idea anyway) SELECT u.`email`, p.`key`, COUNT(pu1.`permission_id`) as cnt_permisions FROM `permission_user` as pu JOIN `permission_user` as pu1 on pu1.`user_id` = pu.`user_id` LEFT JOIN `users` as u on u.`id` = pu.`user_id` JOIN `permission_user` as pu on pu.`user_id` = u.`user_id` LEFT JOIN `permissions` as p on p.`id` = pu.`permission_id` GROUP BY pu1.`user_id` ORDER BY cnt_permisions DESC, u.`created_at` ASC, p.`weight` ASC; – Animir Jan 20 '15 at 11:49