MYSQL查询-获取与帖子相关的最新评论 [英] MYSQL Query - Get latest comment related to the post
问题描述
我正在尝试获取与我下载的每个帖子相关的最新1或2条评论，就像instagram一样，因为它们显示每个帖子的最新3条评论，到目前为止，我正在获得帖子&喜欢计数.
I am trying to get the latest 1 or 2 comments related to each post I download, a bit like instagram does as they show the latest 3 comments for each post, So far I am getting the posts & the likes counts.
现在我所要做的就是弄清楚如何获取最新评论，而不是太确定如何处理它，这就是为什么我希望有更多专业知识的人可以帮助我！
Now all I need to do is figure out how to get the latest comments, not too sure how to approach it and that is why I hoping someone with a lot more expertise can help me!
这是我当前的查询:
(SELECT
P.uuid,
P.caption,
P.imageHeight,
P.path,
P.date,
U.id,
U.fullname,
U.coverImage,
U.bio,
U.username,
U.profileImage,
coalesce(Activity.LikeCNT,0),
Activity.CurrentUserLiked
FROM USERS AS U
INNER JOIN Posts AS P
ON P.id = U.id
LEFT JOIN (SELECT COUNT(DISTINCT Activity.uuidPost) LikeCNT, Activity.uuidPost, Activity.id, sum(CASE WHEN Activity.id = $id then 1 else 0 end) as CurrentUserLiked
FROM Activity Activity
WHERE type = 'like'
GROUP BY Activity.uuidPost) Activity
ON Activity.uuidPost = P.uuid
AND Activity.id = U.id
WHERE U.id = $id)
UNION
(SELECT
P.uuid,
P.caption,
P.imageHeight,
P.path,
P.date,
U.id,
U.fullname,
U.coverImage,
U.bio,
U.username,
U.profileImage,
coalesce(Activity.LikeCNT,0),
Activity.CurrentUserLiked
FROM Activity AS A
INNER JOIN USERS AS U
ON A.IdOtherUser=U.id
INNER JOIN Posts AS P
ON P.id = U.id
LEFT JOIN (SELECT COUNT(DISTINCT Activity.uuidPost) LikeCNT, Activity.uuidPost, Activity.id, sum(CASE WHEN Activity.id = $id then 1 else 0 end) as CurrentUserLiked
FROM Activity Activity
WHERE type = 'like'
GROUP BY Activity.uuidPost) Activity
ON Activity.uuidPost = P.uuid
AND Activity.id = U.id
WHERE A.id = $id)
ORDER BY date DESC
LIMIT 0, 5
基本上，评论与点赞存储在同一张表中.
Basically the comments are store in the same table as the likes.
所以表是Activity，然后有一个列comment用于存储注释文本，然后类型"等于注释".
So the table is Activity, then I have a column comment which stores the comment text, and then the "type" is equal to "comment".
可能解释得不是很好，但我愿意尝试并提供尽可能多的细节！
Possibly not very well explained but I am willing to try and give as much detail as possible!
如果有人可以帮助，将不胜感激！
If anyone can help it's very much appreciated!!
更新
关于 https://stackoverflow.com/users/1016435/xqbert 给出的查询，我目前正在获取此信息错误:
On this query given by https://stackoverflow.com/users/1016435/xqbert I am currently getting this error:
操作'='的归类(utf8_general_ci，IMPLICIT)和(utf8_unicode_ci，IMPLICIT)的非法混合
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
SELECT Posts.id,
Posts.uuid,
Posts.caption,
Posts.path,
Posts.date,
USERS.id,
USERS.username,
USERS.fullname,
USERS.profileImage,
coalesce(A.LikeCNT,0),
com.comment
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 comment, UUIDPOST, @row_num := IF(@prev_value=UUIDPOST,@row_num+1,1) as row_number,@prev_value := UUIDPOST
FROM Activity
CROSS JOIN (SELECT @row_num := 1) x
CROSS JOIN (SELECT @prev_value := '') y
WHERE type = 'comment'
ORDER BY UUIDPOST, date DESC) Com
ON Com.UUIIDPOSt = Posts.UUID
AND row_number <= 2
ORDER BY date DESC
LIMIT 0, 5
最新编辑
表结构:
帖子
----------------------------------------------------------
| id | int(11) | | not null |
| uuid | varchar(100) | utf8_unicode_ci | not null |
| imageLink | varchar(500) | utf8_unicode_ci | not null |
| date | timestamp | | not null |
----------------------------------------------------------
用户
-------------------------------------------------------------
| id | int(11) | | not null |
| username | varchar(100) | utf8_unicode_ci | not null |
| profileImage | varchar(500) | utf8_unicode_ci | not null |
| date | timestamp | | not null |
-------------------------------------------------------------
活动
----------------------------------------------------------
| id | int(11) | | not null |
| uuid | varchar(100) | utf8_unicode_ci | not null |
| uuidPost | varchar(100) | utf8_unicode_ci | not null |
| type | varchar(50) | utf8_unicode_ci | not null |
| commentText | varchar(500) | utf8_unicode_ci | not null |
| date | timestamp | | not null |
----------------------------------------------------------
这些是一些示例，在这种情况下，活动"表中的类型"将始终等于注释".
Those are some examples, in the "Activity" table in this case "type" will always be equal to "comment".
总结一切和渴望的结果:
Sum up of everything and desire result:
当我查询用户的帖子时，我希望能够进入活动"表并为他的每个帖子获得最新 2条评论.也许没有评论，所以很明显它将返回0，或者该帖子可能有100条评论.但我只想获得最新/最近的2条评论.
When I query the users posts, I would like to be able to go into the "Activity" table and get the latest 2 comments for every posts he has. Maybe there will be no comments so obviously it would return 0, maybe there could be 100 comments for that post. But I only want to get the latest/most recent 2 comments.
一个例子可能是看Instagram是如何做到的.对于每个帖子，显示最新的评论1、2或3 ...
An example could be looking at how Instagram does it. For every post the display the most recent comments 1, 2 or 3....
希望这会有所帮助！
推荐答案
此错误消息
排序规则(utf8_general_ci，IMPLICIT)和 (utf8_unicode_ci，IMPLICIT)用于操作'='
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
通常是由于您定义了列和表.通常，这意味着在等号的两边都有不同的排序规则.您需要做的是选择一个并将该决定包括在您的查询中.
is typically due to the definition of your columns and tables. It usually means that on either side of an equal sign there are different collations. What you need to do is choose one and include that decision in your query.
这里的排序规则问题是@prev_value的CROSS JOIN，它需要使用显式的排序规则.
The collation issue here was in the CROSS JOIN of @prev_value which needed an explicit collation to be used.
我也将"row_number"逻辑略微更改为单个交叉联接，并将if逻辑移至了选择列表的末端.
I have also slightly changed the "row_number" logic to a single cross join and moved the if logic to the extremes of the select list.
下面显示了一些样本数据.需要样本数据来测试查询.任何尝试通过工作示例回答您的问题的人都将需要数据.我将其包含在此处的原因有两个.
Some sample data is displayed below. Sample data is needed to test queries with. Anyone attempting to answer your question with working examples will need data. The reason I am including it here is twofold.
- 这样您就可以理解我提出的任何结果
- 这样，将来在您提出另一个与SQL有关的问题时，您就会了解提供数据的重要性.您这样做不仅对我们更方便.如果问询者提供了样本数据，那么问询者将已经理解了它-这不是某个陌生人的发明，因为他们花了一些时间来提供帮助.
样本数据
请注意，表中缺少某些列，仅包括了表详细信息中指定的列.
Please note some columns are missing from the tables, only the columns specified in the table details have been included.
此示例数据对单个帖子有5条评论(没有记录喜欢的消息)
This sample data has 5 comments against a single post (no likes are recorded)
CREATE TABLE Posts
(
`id` int,
`uuid` varchar(7) collate utf8_unicode_ci,
`imageLink` varchar(9) collate utf8_unicode_ci,
`date` datetime
);
INSERT INTO Posts(`id`, `uuid`, `imageLink`, `date`)
VALUES
(145, 'abcdefg', 'blah blah', '2016-10-10 00:00:00') ;
CREATE TABLE USERS
(
`id` int,
`username` varchar(15) collate utf8_unicode_ci,
`profileImage` varchar(12) collate utf8_unicode_ci,
`date` datetime
) ;
INSERT INTO USERS(`id`, `username`, `profileImage`, `date`)
VALUES
(145, 'used_by_already', 'blah de blah', '2014-01-03 00:00:00') ;
CREATE TABLE Activity
(
`id` int,
`uuid` varchar(4) collate utf8_unicode_ci,
`uuidPost` varchar(7) collate utf8_unicode_ci,
`type` varchar(40) collate utf8_unicode_ci,
`commentText` varchar(11) collate utf8_unicode_ci, `date` datetime
) ;
INSERT INTO Activity (`id`, `uuid`, `uuidPost`, `type`, `commentText`, `date`)
VALUES
(345, 'a100', 'abcdefg', 'comment', 'lah lha ha', '2016-07-05 00:00:00'),
(456, 'a101', 'abcdefg', 'comment', 'lah lah lah', '2016-07-06 00:00:00'),
(567, 'a102', 'abcdefg', 'comment', 'lha lha ha', '2016-07-07 00:00:00'),
(678, 'a103', 'abcdefg', 'comment', 'ha lah lah', '2016-07-08 00:00:00'),
(789, 'a104', 'abcdefg', 'comment', 'hla lah lah', '2016-07-09 00:00:00') ;
[SQL标准行为:每个Post查询2行]
这是我最初的查询，并进行了一些更正.我更改了选择列表的列顺序，以便在显示结果时可以轻松地看到一些与注释相关的数据.请研究它们提供的结果，以便您了解查询的作用.由于我已经指出的原因，在我正在使用的示例数据中不存在以#开头的列.
This was my initial query, with some corrections. I changed the column order of the select list so that you will see some comment related data easily when I present the results. Please study those results they are provided so you may understand what the query will do. Columns preceded by # do not exist in the sample data I am working with for reasons I have already noted.
SELECT
Posts.id
, Posts.uuid
, rcom.uuidPost
, rcom.commentText
, rcom.`date` commentDate
#, 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
, commentText
, 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
ORDER BY
posts.`date` DESC
;
结果 :
Results:
| id | uuid | uuidPost | commentText | date | date | id | username | profileImage | num_likes |
|-----|---------|----------|-------------|------------------------|---------------------------|-----|-----------------|--------------|-----------|
| 145 | abcdefg | abcdefg | hla lah lah | July, 09 2016 00:00:00 | October, 10 2016 00:00:00 | 145 | used_by_already | blah de blah | 0 |
| 145 | abcdefg | abcdefg | ha lah lah | July, 08 2016 00:00:00 | October, 10 2016 00:00:00 | 145 | used_by_already | blah de blah | 0 |
有2个ROWS-如预期的那样.一行用于最近的注释，另一行用于下一个最近的注释.这是SQL的正常行为，除非在此答案下添加注释，否则问题的读者会认为该正常行为是可以接受的.
There are 2 ROWS - as expected. One row for the most recent comment, and another rows for the next most recent comment. This is normal behaviour for SQL and until a comment was added under this answer readers of the question would assume this normal behaviour would be acceptable.
该问题缺少明确表达的预期结果".
The question lacks a clearly articulated "expected result".
[选项1:每个帖子查询一行，最多可添加2条评论，并添加了列]
在下面的评论中，您发现您不希望每个帖子2行，这很容易解决.嗯，这很容易，但是有很多选择，而这些选择是由用户以需求的形式来决定的.如果问题有预期结果"，那么我们将知道选择哪个选项.尽管如此，这是一个选择
In a comment below it was revealed that you did not want 2 rows per post and this would be an easy fix. Well it kind of is easy BUT there are options and the options are dictated by the user in the form of requirements. IF the question had an "expected result" then we would know which option to choose. Nonetheless here is one option
SELECT
Posts.id
, Posts.uuid
, max(case when rcom.row_number = 1 then rcom.commentText end) Comment_one
, max(case when rcom.row_number = 2 then rcom.commentText 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
, commentText
, 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
posts.`date` DESC
;
查询2的结果 :
Results of query 2:
| id | uuid | Comment_one | Comment_two | date | id | username | profileImage | num_likes |
|-----|---------|-------------|-------------|---------------------------|-----|-----------------|--------------|-----------|
| 145 | abcdefg | hla lah lah | ha lah lah | October, 10 2016 00:00:00 | 145 | used_by_already | blah de blah | 0 |
**选项2，将最新评论连接到一个逗号分隔的列表中，**
** Option 2, concatenate the most recent comments into a single comma separated list **
SELECT
Posts.id
, Posts.uuid
, group_concat(rcom.commentText) Comments_two_concatenated
#, 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
, commentText
, 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
posts.`date` DESC
查询3的结果 :
Results of query 3:
| id | uuid | Comments_two_concatenated | date | id | username | profileImage | num_likes |
|-----|---------|---------------------------|---------------------------|-----|-----------------|--------------|-----------|
| 145 | abcdefg | hla lah lah,ha lah lah | October, 10 2016 00:00:00 | 145 | used_by_already | blah de blah | 0 |
**摘要**
** Summary **
我提出了3个查询，每个查询仅显示2条最近的评论，但是每个查询的执行方式都不同.第一个查询(默认行为)将为每个帖子显示2行.选项2添加一列，但删除第二行.选项3连接了最近的2条评论.
I have presented 3 queries, each one shows only the 2 most recent comments, but each query does that in a different way. The first query (default behaviour) will display 2 rows for each post. Option 2 adds a column but removes the second row. Option 3 concatenates the 2 most recent comments.
请注意:
- 该问题缺少涵盖所有列的表定义
- 该问题缺少任何示例数据，这使您更难理解此处给出的任何结果，但也使我们更难以准备解决方案
- 该问题还缺少明确的预期结果"(所需的输出)，这导致答案的进一步复杂性
我确实希望提供的附加信息有用，并且到目前为止，您还知道SQL将数据显示为多行是正常的.如果您不希望这种正常行为，请具体说明您在问题中真正想要的是什么.
I do hope the additional provided information will be of some use, and that by now you also know that it is normal for SQL to present data as multiple rows. If you do not want that normal behaviour please be specific about what you do really want in your question.
后记.要包含另一个遵循"子查询，可以使用与已有子查询类似的子查询.它可以在该子查询之前或之后添加.您还可以在sqlfiddle 此处
Postscript. To include yet another subquery for "follows" you may use a similar subquery to the one you already have. It may be added before or after that subquery. You may also see it in use at sqlfiddle here
LEFT JOIN (
SELECT
COUNT(*) FollowCNT
, IdOtherUser
FROM Activity
WHERE type = 'Follow'
GROUP BY
IdOtherUser
) F ON USERS.id = F.IdOtherUser
尽管添加另一个子查询可能会解决您对更多信息的需求，但总体查询可能会随着数据增长而变慢.确定了您真正需要的功能后，可能值得考虑在这些表上需要哪些索引. (我相信建议您单独咨询该建议，并且如果您确保包含1.表的完整DDL和2.查询的解释计划.)
Whilst adding another subquery may resolve your desire for more information, the overall query may get slower in proportion to the growth of your data. Once you have settled on the functionality you really need it may be worthwhile considering what indexes you need on those tables. (I believe you would be advised to ask for that advice separately, and if you do make sure you include 1. the full DDL of your tables and 2. an explain plan of the query.)
这篇关于MYSQL查询-获取与帖子相关的最新评论的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
