php - 我从1个表中获取数据并检查第二个表中不存在该数据

Question

我创建了 3 个论坛组和 group_members 表，我只想获取那些不在组成员中的组以及用户 ID，当前正在获取组 ID 和用户 ID 不存在于组成员表中的组(如果有数据)仅当存在 1 个组成员时，它才会提取记录。简而言之，我想显示用户尚未加入的组，这里是我的三个表的表架构

Groups
+----+----------+
| id | name     |
+----+----------+
| 1  | group 1  |
| 2  | group 2  |
| 3  | group 3  |
| 4  | group 4  |
+----+----------+

forums
+------------------+-------------+
| id | title       | group_id    |
+------------------+-------------+
| 1  | test 1      |           2 |
| 2  | test 2      |           3 |
| 3  | test 3      |           2 |
| 4  | test 4      |           3 |
| 5  | test 5      |           2 |
| 6  | test 6      |           4 |
+------------------+-------------+

Group_members
+-----------------+-------------+
| id | user_id  |  group_id    |
+-----------------+-------------+
| 1  | 107       |     2        |
| 2  | 106       |     3        |
+-----------------+-------------+

这是我写的sql

<?php
$sql_grp_chk = $this->db->query("SELECT * FROM groups WHERE NOT EXISTS (SELECT * FROM group_members WHERE groups.id == group_members.group_id)");
foreach($sql_grp_chk->result() as $data_ct):
    $sql_gr_coun = $this->db->query("SELECT groups.*, (SELECT count(group_id) FROM forums WHERE groups.id = forums.group_id) as forumcount FROM groups WHERE groups.id != '".$data_ct->id."' ORDER BY forumcount DESC LIMIT 5");
    foreach($sql_gr_coun->result() as $data_count):
        $sql_follow = $this->db->get_where('group_members', array('group_id' => $data_count->id));
        var_dump($data_count);
?>

<?php endforeach; ?>
<?php endforeach; ?>

Answer 1

不确定为什么存在forums，但要选择所有未链接到用户的群组，您可以执行left join:

select g.* from groups g
left join group_members m on m.group_id = g.id and m.user_id = :userId
where m.id is null;

编辑:

根据链接的论坛数量选择前 5 个群组:

select g.*, count(nullif(f.id, 1)) as cnt from groups g
inner join forums f on f.group_id = g.id
group by g.id
order by cnt desc
limit 5;

两个查询一起 - 按链接的论坛数量排名前 5 位的群组，其中用户尚未加入:

select g.*, count(nullif(f.id, 1)) as cnt from groups g
left join group_members m on m.group_id = g.id and m.user_id = :userId
left join forums f on f.group_id = g.id
where m.id is null
group by g.id
order by cnt desc
limit 5;