CODEWITHSUNDEEP
mysqlrelation
Andy
Andy

Reputation: 1043

MySQL: users, users_groups and grous tables

I have got 3 tables (only important for question columns here):

USERS              GROUPS                 USERS_GROUPS
id                 id                     user_id
                   importance             group_id
                   color

As you can see, one user can be in many groups, each group has got different color, some groups are more important than others. For example user A is in group editor (blue color) and moderator (green color). What color he should be visible as? In green, because moderator is more important than editor.

But I have no idea (maybe it isn't possible in one query?) how to get user's color from table groups. I have created such query:

SELECT * FROM `users` INNER JOIN users_groups ON users_groups.user_id = users.id INNER JOIN groups ON groups.id=users_groups.group_id

But it returns as many user's rows as user has group, like that:

 USERNAME   (...)    GROUP_ID       NAME (group)         COLOR       IMPORTANCE
 user                2              moderator            green       50
 user                3              editor               blue        25
 administrator       1              admin                orange      100
 administrator       3              editor               blue        25

I would like to get only most important group - administrator as member of group admin and user as member of group moderator.

Upvotes: 3

Views: 398

Answers (2)

Andrew G
Andrew G

Reputation: 349

Option 1: This one gets the most important group for each user and then goes back into the groups table to find the color for that group. The additional JOIN is needed because the initial GROUP BY will not necessarily grab the correct color corresponding with the MAX(importance) result.

SELECT T1.*, groups.color FROM
(
    SELECT users.id, MAX(groups.importance) AS most_important_group FROM `users` 
    INNER JOIN 
    users_groups 
    ON users_groups.user_id = users.id 
    INNER JOIN groups 
    ON groups.id=users_groups.group_id
    GROUP BY users.id
) AS T1
INNER JOIN
groups
ON T1.most_important_group=groups.importance
GROUP BY T1.id

Option 2: This option orders the initial result set in a way which should bring each users' highest importance group to the top along with the corresponding color from that row. After the outer GROUP BY, you should only see one row for each user containing the info you want. You can customize which fields to return from the subquery. I included as many as possible, but you should whittle these down if you don't need them (fewer fields should mean a faster result).

SELECT * FROM
(
    SELECT users.*, users_groups.*, groups.importance, groups.color FROM `users` 
    INNER JOIN users_groups 
    ON users_groups.user_id = users.id 
    INNER JOIN groups 
    ON groups.id=users_groups.group_id
    ORDER BY users.id, groups.importance DESC
) AS T1
GROUP BY T1.id

The performance of these will vary depending on your table sizes and definitely with your index structure. Try them both and respond with which one is faster. If you need faster run time, feel free to respond with some EXPLAIN statements from these queries and DESCRIBE statements about the tables.

Upvotes: 1

Bad Wolf
Bad Wolf

Reputation: 8349

This is a fairly common problem in mysql which has several different solutions, the generally fastest is to join to a subquery which selects the highest importance to filter the results.

SELECT users.*, groups.*
FROM `users` 
INNER JOIN users_groups 
ON users_groups.user_id = users.id 
INNER JOIN groups 
ON groups.id=users_groups.group_id
INNER JOIN (SELECT user_groups.user_id, MAX(importance) AS importance FROM groups GROUP BY user_id) mxGrp
ON (groups.user_id = mxGrp.user_id AND groups.importance = mxGrp.importance);

Read this blog post for discussion about other possible solutions.

Upvotes: 1

Related Questions