CODEWITHSUNDEEP
mysqlsqldatabaseposts
Lorenzo Lapucci
Lorenzo Lapucci

Reputation: 139

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

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;

Upvotes: 0

Views: 3888

Answers (4)

artoodetoo
artoodetoo

Reputation: 938

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

So, there are two tasks here:

  1. Get first N records per group
  2. Apply query to given user PLUS the same for the related users

I would do something like this (pseudo code): 

ids = query('SELECT user_id FROM following WHERE followed = :id)', userId).pluck('user_id');
ids.push(userId);

SELECT x.id, x.user_id, x.content
FROM (
      SELECT  @num := IF(@id = user_id, @num + 1, 1) AS num, 
              @id := posts.user_id as x_user_id, 
              posts.*
      FROM 
          (SELECT @num := null, @id := null) x, 
          posts
      WHERE posts.user_id IN(:ids)
      ORDER BY posts.id DESC
) AS x
WHERE x.num <= 10

(https://www.db-fiddle.com/f/aiBUwqDApJp6foyq13ZZ2u/1)

See:

Upvotes: 1

forpas
forpas

Reputation: 164164

Get the posts from the table posts under your conditions and join to users:

select p.id, p.content, u.id AS user_id, u.username
from (
  select *
  from posts
  where user = '$user_id'
     or user in (select user_id from following where followed = '$user_id')
) p inner join users u on u.id = p.user  
order by p.id desc limit 10

Note that as it is your requirement the results may not contain posts by the user '$user_id' if the last 10 posts are from the users that this user follows.
See the demo.

Upvotes: 1

David Aubke
David Aubke

Reputation: 98

I was about to post a UNION solution

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;

Then I saw @Gordon Linoff's solution which might be better - more concise, at least - but I don't think it works as posted.

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270593

If I understand correctly, you basically want an EXISTS clause:

SELECT p.id, p.content, u.id AS user_id, u.username
FROM posts p JOIN
     users u
     ON p.user = u.id
WHERE u.id <> ?   -- ? is the userid AND
      EXISTS (SELECT 1
              FROM following f
              WHERE f.followed = ? AND
                    f.user_id = u.id
             )
LIMIT 10;

Upvotes: 0

Related Questions