php - 如何显示我的帖子以及关注者的帖子MySQL

我正在尝试创建一个类似twitter的系统，用户可以相互连接，然后他们将能够看到彼此的帖子

这是用户表

+-----+------------+---------+
|  id |  name      |username |
+-----+------------+---------+
|   1 | Phillip    | user1   |
|   2 | Another    | user2   |
+-----+------------+---------+

连接表

+---------+-----------+--------------+--------------+
|      id | follower  |  following   |     status   |
+---------+-----------+--------------+--------------+
|       1 |  user1    |  user2       |       0      |
|       2 |  user3    |  user1       |       1      |
+---------+-----------+--------------+--------------+

发布表

+---------+-----------+--------------+--------------+
| post_id | content   |     title    |    username  |
+---------+-----------+--------------+--------------+
|       1 | hello guyz|      eg1     |     user1    |
|       2 | example1  |      eg 2    |     user2    |
+---------+-----------+--------------+--------------+

我试过的查询

SELECT
post.*
FROM users
LEFT JOIN connection ON (users.username = connection.follower
 OR users.username = connection.following)
INNER JOIN messages ON (
  users.username = post.username
  OR connection.following = post.username
)
WHERE users.username = 'user1'
GROUP BY post.id
LIMIT 0, 8

此查询可能不正确，因为此查询也不完整

为了让您更好地理解->

假设我的名字是example，如果我将请求发送到example2，并且他接受了我的请求，然后status will be set to 1，那么我如何在时间轴上看到他的帖子，以及他如何在时间轴上看到我的帖子

最佳答案

查询所需的结果是

SELECT * FROM `posts`
WHERE `username` = 'user1'
OR `username` IN (SELECT `follower` FROM `connection` WHERE `following`='user1' AND `status`=1)