SQL中的Twitter样式跟随表 [英] Twitter style following-follower table in SQL

问题描述

我在mySQL中有一个基本的跟随者/下表，看起来像这样。

I have a basic follower/following table in mySQL which looks like this.

id      user_id     follower_id
1           userA       userB
2           userC       userA
3           userA       userC
4           userB       userC
5           userB       userA

我检查过这些主题，但无法找到我需要的

I checked these topics but couldnt find what I need

数据库设计，用于'追踪者'和'跟踪'？

SQL关注和关注者

我需要的是有一个系统像这样：

What I need is to have a system like this:

假设我们是userB（我们遵循userA，我们后面是userC和userA）

Assume we are userB ( we follow userA, we are followed by userC and userA)

我喜欢返回一个包含此跟随者/后续状态的结果。例如对于userB：

I like to return a result that includes this follower/following state. For example for userB:

id      followedBy     areWeFollowing
1           userA       1
2           userC       0

感谢您的帮助！

arda

推荐答案

使用此查询来查找您是否关注您。

With this query to find if who you follow, follow you too.

SELECT ff1.follower_id as followedBy,
(
   select count(follower_id) 
   from follower_following as ff2 
   where ff2.user_id = ff1.follower_id 
   and ff2.follower_id = ff1.user_id 
) as areWeFollowing 
FROM follower_following as ff1  
where user_id = 'userB';

这篇关于SQL中的Twitter样式跟随表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！