mysql related fields

Question

These 4 fields are related to each other

Friends - posts - users - feeds

I want it to output it as: enter image description here

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.

Please explain better what you want to achieve, and how friends are related to users and posts ... — aleroot
The friends table is related to user and posts. The friends.friend_id = posts.user_id = users.user_id All posts from posts will be outputted using the ff conditions: 1.) if friends.friends_id or friends.user_id matches posts.user_id 2.) if no match then just select all the posts from posts.user_id = current logged user. — Peter Wateber
How feeds is related to other tables? How to distinguish between you and everybody in feeds? — vyegorov
You can test the sql statement above. the feeds are the same as "LIKE" on facebook. the you and everybody counts all the feeds or likes from a certain user using user_id. — Peter Wateber

vyegorov vyegorov · Accepted Answer · 2012-05-13T09:36:26

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.

mysql related fields

2 Answers