Select latest record on many to many relationship

Question

I have a many to many relationship like below. A user can change their role, and I want to get all users with their last role equals to role2 (example).

users

user_id | user_name  | user_password
1       | user1      | *hashed password*
2       | user2      | *hashed password*
3       | user3      | *hashed password*

roles

role_id | role_name | role_description
1       | role1     | *description*
2       | role2     | *description*

user_roles

user_roles_id | user_id | role_id | created_at
1             | 1       | 1       | 2018-04-10 01:01:01
2             | 2       | 2       | 2018-04-10 01:01:02
3             | 3       | 1       | 2018-04-10 01:01:03
4             | 1       | 2       | 2018-04-12 01:01:01
5             | 1       | 1       | 2018-04-13 01:01:02
6             | 2       | 1       | 2018-04-14 01:01:01
7             | 3       | 2       | 2018-04-14 01:01:02
8             | 2       | 2       | 2018-04-15 01:01:01
9             | 1       | 2       | 2018-04-15 01:01:02
10            | 1       | 1       | 2018-04-16 01:01:01

From those tables, I want to get things like

user_id | role_id
2       | 2
3       | 2

But all this time I got

user_id | role_id
1       | 2
2       | 2
3       | 2

user1 should not be there because its last role was role1. So what I'm trying to do is, get users where last role = 2.

Any help would be appreciated! Thanks in advance!

Addition

The results that I wanted is all data from users table. So it might be like

user_id | user_name | <and all of the rest>
2       | user2     | ...
3       | user3     | ...

So that, the field role_id I mentioned above is just reference to select the user.

Why not just select the user like this: `SELECT user.user_id, role_id FROM users JOIN user_roles ON user.user_id = user_roles.user_id WHERE role_id = 2 GROUP BY user.user_id, role_id` — schlonzo, Apr 17 '18 at 08:51
Well that works, but that query will include the `user1`, i want to select user with last role = 2, so this might include the `created_at` field. Thanks in advance! — Gregorio, Apr 17 '18 at 08:55
Maybe this can be of help? I know my databases, but suck at writing queries for some reason. This looks pretty much like what you need though: https://stackoverflow.com/questions/2411559/how-do-i-query-sql-for-a-latest-record-date-for-each-user — Loek, Apr 17 '18 at 09:04
Yes, mostly close to it, but it still get the `user1` even i've tried to put the criteria @Loek. I'm using mysql mariadb @PeterAbolins — Gregorio, Apr 17 '18 at 09:16

Peter Abolins · Accepted Answer · 2018-04-17T09:52:52.803

1

This is one solution. Essentially, you are looking at each user's latest role, and only include it in the output, if the role is 2.

SELECT 
    ur.user_id, ur.role_id 
FROM 
    user_roles ur
WHERE 
    ur.created_at = (SELECT MAX(created_at) 
                     FROM user_roles
                     WHERE user_id = ur.user_id)
    AND ur.role_id = 2
GROUP BY 
    ur.user_id, ur.role_id;

EDIT

Based on the additional information from the comments, the following will return all information from the Users table:

SELECT 
    u.* 
FROM
    users u INNER JOIN (
        SELECT 
            ur.user_id
        FROM 
            user_roles ur
        WHERE 
            ur.created_at = (SELECT MAX(created_at) 
                             FROM user_roles
                             WHERE user_id = ur.user_id)
            AND ur.role_id = 2
        GROUP BY 
            ur.user_id, ur.role_id) tmp ON tmp.user_id = u.user_id;

edited Apr 17 '18 at 09:52

answered Apr 17 '18 at 09:36

Peter Abolins

1,520
1
11
18

It works! But can I select all data from `users` table? So it might not be `select field1, field2 from user_roles` but mostly like `select * from users` – Gregorio Apr 17 '18 at 09:46
@Gregorio My edited version should answer that question. – Peter Abolins Apr 17 '18 at 09:53
@Gregorio If this solved your problem, perhaps you could mark it as an answer? – Peter Abolins Apr 18 '18 at 06:08
Thanks! I'm using `where in` instead of `join`. – Gregorio Apr 18 '18 at 06:35

Yogesh Sharma · Answer 2 · 2018-04-17T09:57:31.313

1

Use subquery with correlation approach and do the joins

select u.user_id, u.user_name, u.user_password, 
       r.role_id, r.role_name,  r.role_description
from (select  t.user_id, (select role_id from user_roles 
                          where user_id = t.user_id
                          order by created_at desc LIMIT 1) as role_id
      from user_roles t
      group by t.user_id) tt
inner join users u on u.user_id  = tt.user_id
inner join roles r on r.role_id = tt.role_id 
where r.role_id <> 1;

edited Apr 17 '18 at 09:57

answered Apr 17 '18 at 09:40

Yogesh Sharma

49,870
5
26
52

It works too! But i need explanation, and also can I select all the data from `users`, not only from the subquery? – Gregorio Apr 17 '18 at 09:47
Thanks! I think it's though to translate this query to laravel (at least for me). I need to learn more about query builder. – Gregorio Apr 18 '18 at 06:36

Select latest record on many to many relationship

2 Answers2