mysql related fields

Question

These 4 fields are related to each other

I want it to output it as:

In my query:

SELECT users.firstname, users.lastname, users.screenname, posts.post_id, posts.user_id,
posts.post, posts.upload_name, posts.post_type, 
DATE_FORMAT(posts.date_posted, '%M %d, %Y %r') AS date, 
COUNT(NULLIF(feeds.user_id, ?)) AS everybody, SUM(feeds.user_id = ?) AS you,
GROUP_CONCAT(CASE WHEN NOT likes.user_id = ? THEN 
             CONCAT_WS(' ', likes.firstname, likes.lastname)
                    END
            ) as names
FROM website.users users
INNER JOIN website.posts posts ON (users.user_id = posts.user_id)
LEFT  JOIN website.feeds feeds ON (posts.post_id = feeds.post_id)
LEFT  JOIN website.users likes ON (feeds.user_id = likes.user_id)
GROUP BY posts.pid
ORDER BY posts.pid DESC

Now, I am having a problem on which part should I join the friends table, I want to display all the posts from friend_id or user_id and also the post from user who is currently logged in. If no friend matched on the friend table, then just output all the posts from user. Please guys I need your help.

friends.friend_id = friend of the current user

friends.user_id = current friend of the user

Thus, friends.friend_id = posts.user_id or friends.user_id = posts.user_id

If my friends table is not understandable, please help me change it to make it better.

Answer 1

If i well understood you want to JOIN the friends table based on the friends = user_id and if not match JOIN on user_id of the friends table, so you can try with something like this :

SELECT users.firstname, users.lastname, users.screenname, posts.post_id, posts.user_id,
posts.post, posts.upload_name, posts.post_type, 
DATE_FORMAT(posts.date_posted, '%M %d, %Y %r') AS date, 
COUNT(NULLIF(feeds.user_id, ?)) AS friends, SUM(feeds.user_id = ?) AS you,
GROUP_CONCAT(CASE WHEN NOT likes.user_id = ? THEN 
             CONCAT_WS(' ', likes.firstname, likes.lastname)
                    END
            ) as names
FROM website.users users
INNER JOIN website.posts posts ON (users.user_id = posts.user_id)
LEFT  JOIN website.feeds feeds ON (posts.post_id = feeds.post_id)
LEFT  JOIN website.users likes ON (feeds.user_id = likes.user_id)
LEFT  JOIN website.friends friends ON ((posts.user_id = friends.user_id) OR (posts.user_id = friends.friends_id) )
GROUP BY posts.pid
ORDER BY posts.pid DESC

I have basically added a JOIN with friends table with an OR on the two fields that you seem want to JOIN ...

Answer 2

You would like to see posts either from the user, or from his friends. Therefore, instead of joining with users, join with the subquery, like this:

SELECT users.firstname, users.lastname, users.screenname,
       posts.post_id, posts.user_id, posts.post, posts.upload_name,
       posts.post_type, DATE_FORMAT(posts.date_posted, '%M %d, %Y %r') AS date, 
       COUNT(NULLIF(feeds.user_id, ?)) AS everybody,
       SUM(feeds.user_id = ?) AS you,
       GROUP_CONCAT(CASE WHEN NOT likes.user_id = ? THEN 
             CONCAT_WS(' ', likes.firstname, likes.lastname) END) as names
  FROM (SELECT user_id FROM website.users WHERE user_id = ?
        UNION ALL
        SELECT user_id FROM website.friends WHERE friend_id = ?
        UNION ALL
        SELECT friend_id FROM website.friends WHERE user_id = ?) AS who
  JOIN website.users users ON users.user_id = who.user_id
  JOIN website.posts posts ON users.user_id = posts.user_id
  LEFT  JOIN website.feeds feeds ON posts.post_id = feeds.post_id
  LEFT  JOIN website.users likes ON feeds.user_id = likes.user_i)
 GROUP BY posts.pid
 ORDER BY posts.pid DESC;

Test output here.