mysql - 从用户和关注用户中选择帖子的 SQL 查询不包括自己的帖子

Question

我正在构建一个查询，该查询应返回 $userId 及其关注的用户的最后 10 篇帖子(默认排序)。

带有最小查询示例的 SQL Fiddle:https://www.db-fiddle.com/f/i5ByFAXwADj5pfjCTn1g1m/2

数据库结构非常简单:

posts (id, root, user, content)
users (id, username)
following (user_id, followed)

这是我目前用来获取所有帖子的查询:

SELECT posts.id, posts.content, users.id AS user_id, users.username
FROM posts
     LEFT JOIN users ON posts.user = users.id
WHERE LIMIT 10

查询正常，但它会一视同仁地列出每个用户的帖子。

这是我构建的查询，用于排除 $userId 未关注的用户的帖子，但它不包括 $userId 自己的帖子:

SELECT posts.id, posts.content, users.id AS user_id, users.username
FROM following
     LEFT JOIN posts ON posts.user = '$userId' OR posts.user = following.followed
     LEFT JOIN users ON posts.user = users.id
WHERE (following.user_id = '$userId' OR following.user_id = NULL) LIMIT 10

我已经尝试用 INNER JOIN 和 RIGHT JOIN 替换 LEFT JOIN posts 但没有成功。我找不到错误，为什么查询不包括 $userId 发表的帖子？

我也尝试过从帖子中选择并加入关注者，但它返回重复的内容:

SELECT posts.id, posts.content, users.id AS user_id, users.username
FROM posts
     LEFT JOIN following ON following.user_id = '$userId'
     LEFT JOIN users ON posts.user = users.id
WHERE (posts.user = '$userId' OR posts.user = following.followed)
LIMIT 10;

Answer 1

我正要发布一个 UNION 解决方案

SELECT
    post_id,
    content,
    user_id,
    username
FROM
    (SELECT
        posts.id post_id,
        content,
        users.id user_id,
        username
    FROM
        posts INNER JOIN
            users
        ON user = users.id
    UNION SELECT
        posts.id,
        content,
        users.id,
        username
    FROM
        posts INNER JOIN (
            following INNER JOIN
                users
            ON user_id = users.id
        ) ON user = followed
    ) p
WHERE
    user_id = 1
LIMIT 10;

然后我看到@Gordon Linoff 的解决方案可能更好 - 至少更简洁 - 但我认为它不像发布的那样有效。

SELECT
    posts.id,
    content,
    users.id,
    username
FROM
    posts INNER JOIN
        users
    ON user = users.id
WHERE
    users.id = 1
    OR EXISTS (
        SELECT
            *
        FROM
            following
        WHERE
            followed = user
            AND user_id = 1
    )
LIMIT 10;