mysql - 如何在同一查询mysql中选择组成员数和关注状态

Question

我正在尝试获取他创建或关注的用户组，以及关注者总数，并希望获得最近关注者的日期，以便我可以以此为准。我有两张 table 群组

id  title   creator_id   created
4   test3   123          1224322344
5   test2   213          2342344444

关注

id  to_id  follower_id   is_group   created
1    4       222             1       234324324
1    4       123             1       444234234
1    5       213             1       234234444

我试过这个查询

SELECT g. * , IFNULL(f.total_followers, 0 ) total_followers 
FROM groups AS g
LEFT JOIN (

    SELECT to_id, COUNT( * ) AS total_followers
    FROM follow
    WHERE is_group =1
    GROUP BY to_id
) AS f ON ( g.id = f.to_id ) 
where g.creator=123 
GROUP BY g.id
g.created DESC
LIMIT 10

但它只返回用户自己群组的关注者数量，而不是他正在关注的群组。

任何帮助将不胜感激。

谢谢

Answer 1

SELECT g.* , IFNULL(f.total_followers, 0 ) total_followers, f.most_recent
FROM groups AS g
LEFT JOIN (
    SELECT to_id, COUNT( * ) AS total_followers, MAX(created) AS most_recent
    FROM follow
    WHERE is_group =1
    GROUP BY to_id
) AS f ON ( g.id = f.to_id ) 
WHERE g.creator=123 
OR g.id in (SELECT to_id FROM follow WHERE follower_id = 123)
ORDER BY most_recent DESC
LIMIT 10

不使用 IN:

SELECT g.* , IFNULL(f.total_followers, 0 ) total_followers, f.most_recent
FROM (SELECT * FROM groups where creator = 123
      UNION DISTINCT
      SELECT g.* FROM groups AS g1
      JOIN follow AS f1 ON ( g1.id = f1.to_id)
      WHERE f1.follower_id = 123) AS g
LEFT JOIN (
    SELECT to_id, COUNT( * ) AS total_followers, MAX(created) AS most_recent
    FROM follow
    WHERE is_group =1
    GROUP BY to_id
) AS f ON ( g.id = f.to_id )
ORDER BY most_recent DESC
LIMIT 10