How to find all teams a user belongs to?

Question

Hi, 
 How do you write an sql to get a list of teams a user belongs to? I can only find "default_team" (primary team) in user table. I need to know if user is represented in other teams. I need this in a hook. 
 Thanks and regards Jorgen

JÖRGEN Hakefjäll · Answer

Hi JÖRGEN Hakefjäll ,

The following query will return a list of standard users and their team memberships:

Fullscreen

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT 
    u.id 'User ID', 
    u.user_name 'User Name',
    t.id 'Team ID',
    t.name 'Team Name', 
    tm.explicit_assign 'Explicit Assignment', 
    tm.implicit_assign 'Implicit Assignment' 
FROM 
    users u 
LEFT JOIN 
    team_memberships tm 
ON 
    u.id = tm.user_id 
LEFT JOIN 
    teams t 
ON 
    tm.team_id = t.id 
WHERE 
    u.deleted = 0 
AND 
    tm.deleted = 0 
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SELECT 
    u.id 'User ID', 
    u.user_name 'User Name',
    t.id 'Team ID',
    t.name 'Team Name', 
    tm.explicit_assign 'Explicit Assignment', 
    tm.implicit_assign 'Implicit Assignment' 
FROM 
    users u 
LEFT JOIN 
    team_memberships tm 
ON 
    u.id = tm.user_id 
LEFT JOIN 
    teams t 
ON 
    tm.team_id = t.id 
WHERE 
    u.deleted = 0 
AND 
    tm.deleted = 0 
AND 
    t.deleted = 0 
AND 
    u.portal_only <> 1 
AND 
    u.is_group <> 1 
AND 
    u.external_auth_only <> 1 
ORDER BY 
    u.user_name

Please note a user can be simultaneously explicitly (formal relationship to the team) and implicitly (roll up relationship from a user who reports into them directly or indirectly) assigned to a team so be careful if you want to only filter on one or the other.

Chris