mysql - SQL Query to select posts from user and followed users is not including own posts

Question

I'm building a query that should return the last 10 posts (default ordering) by $userId and the users it is following.

SQL Fiddle with a minimal example of the query: https://www.db-fiddle.com/f/i5ByFAXwADj5pfjCTn1g1m/2

The database structure is pretty simple:

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

This is the query I'm currently using to get all posts:

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

The query is working but it is listing posts from every user without distinction.

This is the query I built to exclude posts from users that $userId is not following, but it doesn't include $userId's own posts:

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

I've tried replacing the LEFT JOIN posts with an INNER JOIN and a RIGHT JOIN with no success whatsoever. I'm not able to find the error, why isn't the query including posts made by $userId?

I have also tried selecting from posts and joining followers, but it is returning duplicated content:

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;

标签： mysqlsqldatabaseposts

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;