forked from coder/coder
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathorganizationmembers.sql
More file actions
105 lines (99 loc) · 2.75 KB
/
organizationmembers.sql
File metadata and controls
105 lines (99 loc) · 2.75 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
-- name: OrganizationMembers :many
-- Arguments are optional with uuid.Nil to ignore.
-- - Use just 'organization_id' to get all members of an org
-- - Use just 'user_id' to get all orgs a user is a member of
-- - Use both to get a specific org member row
SELECT
sqlc.embed(organization_members),
users.username, users.avatar_url, users.name, users.email, users.rbac_roles as "global_roles"
FROM
organization_members
INNER JOIN
users ON organization_members.user_id = users.id AND users.deleted = false
WHERE
-- Filter by organization id
CASE
WHEN @organization_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN
organization_id = @organization_id
ELSE true
END
-- Filter by user id
AND CASE
WHEN @user_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN
user_id = @user_id
ELSE true
END
-- Filter by system type
AND CASE
WHEN @include_system::bool THEN TRUE
ELSE
is_system = false
END
-- Filter by github user ID. Note that this requires a join on the users table.
AND CASE
WHEN @github_user_id :: bigint != 0 THEN
users.github_com_user_id = @github_user_id
ELSE true
END;
-- name: InsertOrganizationMember :one
INSERT INTO
organization_members (
organization_id,
user_id,
created_at,
updated_at,
roles
)
VALUES
($1, $2, $3, $4, $5) RETURNING *;
-- name: DeleteOrganizationMember :exec
DELETE
FROM
organization_members
WHERE
organization_id = @organization_id AND
user_id = @user_id
;
-- name: GetOrganizationIDsByMemberIDs :many
SELECT
user_id, array_agg(organization_id) :: uuid [ ] AS "organization_IDs"
FROM
organization_members
WHERE
user_id = ANY(@ids :: uuid [ ])
GROUP BY
user_id;
-- name: UpdateMemberRoles :one
UPDATE
organization_members
SET
-- Remove all duplicates from the roles.
roles = ARRAY(SELECT DISTINCT UNNEST(@granted_roles :: text[]))
WHERE
user_id = @user_id
AND organization_id = @org_id
RETURNING *;
-- name: PaginatedOrganizationMembers :many
SELECT
sqlc.embed(organization_members),
users.username, users.avatar_url, users.name, users.email, users.rbac_roles as "global_roles",
COUNT(*) OVER() AS count
FROM
organization_members
INNER JOIN
users ON organization_members.user_id = users.id AND users.deleted = false
WHERE
-- Filter by organization id
CASE
WHEN @organization_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN
organization_id = @organization_id
ELSE true
END
-- Filter by system type
AND CASE WHEN @include_system::bool THEN TRUE ELSE is_system = false END
ORDER BY
-- Deterministic and consistent ordering of all users. This is to ensure consistent pagination.
LOWER(username) ASC OFFSET @offset_opt
LIMIT
-- A null limit means "no limit", so 0 means return all
NULLIF(@limit_opt :: int, 0);