MYSQL查询 - 包括我在当前查询中关注的人员的帖子
问题描述：
I am trying to get the posts that belong to the user's I follow in my current query.
At the moment I am getting: Current user's posts - like count for each post & and comments.
I need to have this result though: Current user's posts - People I am following post's - like count for each post & comments.
The final result is like most social network homepages do. They get your posts and the posts of the people you are following / or your friends posts.
This is my current query:
SELECT
Posts.id
, Posts.uuid
, max(case when rcom.row_number = 1 then rcom.comment end) Comment_one
, max(case when rcom.row_number = 2 then rcom.comment end) Comment_two
, Posts.caption
, Posts.path
, Posts.`date`
, USERS.id
, USERS.username
, USERS.fullname
, USERS.profileImage
, COALESCE(A.LikeCNT, 0) num_likes
FROM Posts
INNER JOIN USERS ON Posts.id = 145
AND USERS.id = 145
LEFT JOIN (
SELECT
COUNT(A.uuidPost) LikeCNT
, A.UUIDPost
FROM Activity A
WHERE type = 'like'
GROUP BY
A.UUIDPOST
) A ON A.UUIDPost = Posts.uuid
LEFT JOIN (
SELECT
@row_num := IF(@prev_value=UUIDPOST,@row_num+1,1) as row_number
, comment
, uuidPost
, `date`
, @prev_value := UUIDPOST
FROM Activity
CROSS JOIN ( SELECT @row_num := 1, @prev_value := '' collate utf8_unicode_ci ) xy
WHERE type = 'comment'
ORDER BY
uuidPost
, `date` DESC
) rcom ON rcom.uuidPost = Posts.UUID
AND rcom.row_number <= 2
GROUP BY
Posts.id
, Posts.uuid
, Posts.caption
, Posts.path
, Posts.`date`
, USERS.id
, USERS.username
, USERS.fullname
, USERS.profileImage
, COALESCE(A.LikeCNT, 0)
ORDER BY date DESC
;
How are my followers stored?
I store my followers in the "Activity" table like so: id(currentUser) - idOtherUser(Person I am following) - type(which is equal to "Follow").
That is it. Here is a sql fiddle with some sample data.
Any further questions please let me know.
答
This could be what you are looking for.
I assumed every user has to hit the follow button himself. AlsoThis is written from the perspective from user 145, you have to change the WHERE-clauses. In your sample there is no relation for user 145 following user 123, I added it to test the query.
I removed the Users.id from the select list, as it is the same as Posts.id anyways.
Some sample data for the like count would have been nice, don't know how to fill your tables.
SELECT
*
FROM
(
SELECT
Posts.id,
Posts.uuid,
max(
CASE
WHEN rcom.row_number = 1 THEN
rcom.commentText
END
) Comment_one,
max(
CASE
WHEN rcom.row_number = 1 THEN
rcom.commentUserName
END
) Comment_one_user,
max(
CASE
WHEN rcom.row_number = 2 THEN
rcom.commentText
END
) Comment_two,
max(
CASE
WHEN rcom.row_number = 2 THEN
rcom.commentUserName
END
) Comment_two_user,
Posts.`date`,
USERS.username,
USERS.profileImage,
COALESCE (A.LikeCNT, 0) num_likes
FROM
Posts
INNER JOIN USERS ON Posts.id = USERS.id
LEFT JOIN (
SELECT
COUNT(A.uuidPost) LikeCNT,
A.UUIDPost
FROM
Activity A
WHERE
type = 'like'
GROUP BY
A.UUIDPOST
) A ON A.UUIDPost = Posts.uuid
LEFT JOIN (
SELECT
@row_num :=
IF (
@prev_value = UUIDPOST ,@row_num + 1,
1
) AS row_number,
commentText,
uuidPost,
Activity.`date`,
@prev_value := UUIDPOST,
USERS.id AS commentUserId,
USERS.username AS commentUserName
FROM
Activity
CROSS JOIN (
SELECT
@row_num := 1,
@prev_value := '' COLLATE utf8_unicode_ci
) xy
INNER JOIN USERS ON USERS.id = Activity.id
WHERE
type = 'comment'
ORDER BY
uuidPost,
`date` DESC
) rcom ON rcom.uuidPost = Posts.UUID AND rcom.row_number <= 2
WHERE Posts.id = 145
GROUP BY
Posts.id,
Posts.uuid,
Posts.`date`,
USERS.username,
USERS.profileImage,
COALESCE (A.LikeCNT, 0)
UNION
SELECT
Posts.id,
Posts.uuid,
max(
CASE
WHEN rcom.row_number = 1 THEN
rcom.commentText
END
) Comment_one,
max(
CASE
WHEN rcom.row_number = 1 THEN
rcom.commentUserName
END
) Comment_one_user,
max(
CASE
WHEN rcom.row_number = 2 THEN
rcom.commentText
END
) Comment_two,
max(
CASE
WHEN rcom.row_number = 2 THEN
rcom.commentUserName
END
) Comment_two_user,
Posts.`date`,
USERS.username,
USERS.profileImage,
COALESCE (A.LikeCNT, 0) num_likes
FROM
Posts
INNER JOIN Activity ON Activity.type = 'Follow' AND Posts.id = Activity.IdOtherUser
INNER JOIN USERS ON Activity.IdOtherUser = USERS.id
LEFT JOIN (
SELECT
COUNT(A.uuidPost) LikeCNT,
A.UUIDPost
FROM
Activity A
WHERE
type = 'like'
GROUP BY
A.UUIDPOST
) A ON A.UUIDPost = Posts.uuid
LEFT JOIN (
SELECT
@row_num :=
IF (
@prev_value = UUIDPOST ,@row_num + 1,
1
) AS row_number,
commentText,
uuidPost,
Activity.`date`,
@prev_value := UUIDPOST,
USERS.id AS commentUserId,
USERS.username AS commentUserName
FROM
Activity
CROSS JOIN (
SELECT
@row_num := 1,
@prev_value := '' COLLATE utf8_unicode_ci
) xy
INNER JOIN USERS ON USERS.id = Activity.id
WHERE
type = 'comment'
ORDER BY
uuidPost,
`date` DESC
) rcom ON rcom.uuidPost = Posts.UUID AND rcom.row_number <= 2
WHERE Activity.id = 145
GROUP BY
Posts.id,
Posts.uuid,
Posts.`date`,
USERS.username,
USERS.profileImage,
COALESCE (A.LikeCNT, 0)
) AS posts
ORDER BY
posts.`date` DESC;
edit
After tidying up the indentation and joining the comments + likecount after selecting the posts:
SELECT
posts.id,
posts.uuid,
max(CASE WHEN rcom.row_number = 1 THEN rcom.commentText END) AS Comment_one,
max(CASE WHEN rcom.row_number = 1 THEN rcom.commentUserName END) Comment_one_user,
max(CASE WHEN rcom.row_number = 2 THEN rcom.commentText END) Comment_two,
max(CASE WHEN rcom.row_number = 2 THEN rcom.commentUserName END) Comment_two_user,
posts.`date`,
posts.username,
posts.profileImage,
COALESCE(A.LikeCNT) AS likes
FROM
(
SELECT
Posts.id,
Posts.uuid,
Posts.`date`,
USERS.username,
USERS.profileImage
FROM
Posts
INNER JOIN USERS
ON Posts.id = USERS.id
WHERE
Posts.id = 145
GROUP BY
Posts.id,
Posts.uuid,
Posts.`date`,
USERS.username,
USERS.profileImage
UNION
SELECT
Posts.id,
Posts.uuid,
Posts.`date`,
USERS.username,
USERS.profileImage
FROM
Posts
INNER JOIN Activity
ON Activity.type = 'Follow' AND Posts.id = Activity.IdOtherUser
INNER JOIN USERS
ON Activity.IdOtherUser = USERS.id
WHERE
Activity.id = 145
GROUP BY
Posts.id,
Posts.uuid,
Posts.`date`,
USERS.username,
USERS.profileImage
) AS posts
LEFT JOIN (
SELECT
COUNT(A.uuidPost) LikeCNT,
A.UUIDPost
FROM
Activity A
WHERE
type = 'like'
GROUP BY
A.UUIDPOST
) AS A ON A.UUIDPost = posts.uuid
LEFT JOIN (
SELECT
commentText,
uuidPost,
`date`,
commentUserId,
commentUserName,
@row_num := IF (@prev_value = UUIDPOST, @row_num + 1, 1) AS row_number,
@prev_value := UUIDPOST
FROM
(
SELECT
commentText,
uuidPost,
Activity.`date`,
USERS.id AS commentUserId,
USERS.username AS commentUserName
FROM
Activity
INNER JOIN USERS
ON USERS.id = Activity.id
WHERE
type = 'comment'
ORDER BY
uuidPost,
`date` DESC
) AS comments
CROSS JOIN (
SELECT
@row_num := 1,
@prev_value := '' COLLATE utf8_unicode_ci
) AS xy
) rcom
ON rcom.uuidPost = posts.UUID AND rcom.row_number <= 2
GROUP BY
posts.uuid
ORDER BY
posts.`date` DESC