我想获取房间member
列表,member
以防房间所有者同时不在其他表和管理员member
中。目前,我分别获取它们。
CREATE TABLE public.room_members (
id bigint NOT NULL,
member_id bigint,
room_id bigint,
group_id bigint
);
CREATE TABLE public.rooms (
id bigint NOT NULL,
member_id bigint,
group_id bigint,
name varchar(128)
);
CREATE TABLE public.members (
id bigint NOT NULL,
group_id bigint,
username varchar(128),
is_admin bool default false
);
CREATE TABLE public.groups (
id bigint NOT NULL,
name varchar(128)
);
-- My Group created
INSERT INTO "groups" (id, name) VALUES (1, 'My Group');
-- Create users for this group. We have 4 users/members
INSERT INTO "members" (id, group_id, username, is_admin) VALUES (1, 1, 'Pratha', true);
INSERT INTO "members" (id, group_id, username) VALUES (2, 1, 'John');
INSERT INTO "members" (id, group_id, username) VALUES (3, 1, 'Mike');
INSERT INTO "members" (id, group_id, username) VALUES (4, 1, 'April');
-- April creates a room and he is owner of this room
INSERT INTO "rooms" (id, group_id, member_id, name) VALUES (1, 1, 4, 'My Room'); -- 4 is April
-- April also adds Mike to the room members. But she does not add herself. As she is owner.
INSERT INTO "room_members" (id, group_id, room_id, member_id) VALUES (1, 1, 1, 3); -- 3 is Mike
room_members
“我的房间”列表(目前仅麦克)room_members
桌子上。因为他是那个房间的所有者(4月)这应该是唯一的。例如,如果用户将自己添加到room_members
并且也owner
应该只提取一次成员。
select * from members
left outer join room_members cm on cm.member_id = members.id
left outer join rooms c on c.id = cm.room_id
where c.name = 'My Room' or members.id = 1
我也不能在这里使用分组方式。我也不需要所有领域。仅room_members
表字段。
看到这里:https : //rextester.com/XWDS42043
room_members
:+-------------+------------+------------+
| member_id | group_id | username |
+-------------+------------+------------+
| 1 | 1 | Pratha |
+-------------+------------+------------+
| 3 | 1 | Mike |
+-------------+------------+------------+
| 4 | 1 | April |
+-------------+------------+------------+
ADMIN
MEMBER
OWNER
room_members
可以很多。我只添加了Mike,但是它可以有多个成员,包括管理员和所有者。
您可以通过以下方式解决此问题UNION
:
-- list the admin(s) of the room group
select m.id, m.group_id, m.username
from rooms r
inner join members m on m.group_id = r.group_id and m.is_admin = true
where r.name = 'My Room'
union
-- list the members of the room
select m.id, m.group_id, m.username
from rooms r
inner join room_members rm on r.id = rm.room_id
inner join members m on rm.member_id = m.id
where r.name = 'My Room'
union
-- recover the room owner
select m.id, m.group_id, m.username
from rooms r
inner join members m on r.member_id = m.id
where r.name = 'My Room'
UNION
消除了整个查询中的重复项,因此,如果用户既是成员和/或组管理员又是会议室的所有者,则它们只会出现一次。
在您的小提琴中,此查询返回:
id group_id username
1 4 1 April
2 3 1 Mike
3 1 1 Pratha
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句