php - MYSQL 查询 - 获取与帖子相关的最新评论

coder 2023-06-12 原文

我正在尝试获取与我下载的每个帖子相关的最新 1 或 2 条评论，有点像 Instagram 所做的，因为它们显示每个帖子的最新 3 条评论，到目前为止，我得到了帖子和喜欢计数。

现在我需要做的就是弄清楚如何获得最新评论，不太确定如何处理它，这就是为什么我希望有更多专业知识的人可以帮助我!

这是我当前的查询:

(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

基本上评论和赞存储在同一个表中。

所以表是Activity ，那么我有一列comment其中存储评论文本，然后“类型”等于“评论”。

可能没有很好的解释，但我愿意尝试提供尽可能多的细节!

如果有人可以提供帮助，将不胜感激!!

更新

在此查询由 https://stackoverflow.com/users/1016435/xqbert 给出我目前收到此错误:

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 |
    ----------------------------------------------------------

这些是一些示例，在这种情况下的“事件”表中，“类型”将始终等于“评论”。

总结一切和渴望的结果:

当我查询用户帖子时，我希望能够进入“事件”表并获得最新他的每个帖子都有 2 条评论。也许不会有评论，所以很明显它会返回 0，也许该帖子可能有 100 条评论。但我只想获得最新/最近的 2 条评论。

一个例子可能是看看 Instagram 是如何做到的。对于每个帖子，显示最新的评论 1、2 或 3....

希望这可以帮助!

Fiddle link

最佳答案

这个错误信息

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

通常是由于您的列和表的定义。这通常意味着在等号的两侧有不同的排序规则。您需要做的是选择一个并将该决定包含在您的查询中。
这里的排序规则问题出在 @prev_value 的 CROSS JOIN 中，它需要使用显式排序规则。
我还将“row_number”逻辑稍微更改为单个交叉连接，并将 if 逻辑移至选择列表的极端。
下面显示了一些示例数据。需要使用示例数据来测试查询。任何试图用工作示例回答您的问题的人都需要数据。我在这里包括它的原因是双重的。

以便您了解我提出的任何结果

以便将来当您询问另一个与 SQL 相关的问题时，您会了解提供数据的重要性。您这样做不仅对我们更方便。如果提问者提供了样本数据，那么提问者就已经理解了——这不会是某个花费了一些时间来提供帮助的陌生人的发明。

样本数据
请注意表中缺少某些列，仅包含表详细信息中指定的列。
此示例数据对单个帖子有 5 条评论(没有点赞记录)

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 行]
这是我最初的查询，有一些更正。我更改了选择列表的列顺序，以便您在呈现结果时可以轻松看到一些评论相关数据。请研究它们提供的结果，以便您了解查询将执行的操作。由于我已经指出的原因，我正在使用的示例数据中不存在以 # 开头的列。

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
      ;

See a working demonstration of this query at SQLFiddle
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 行。一行是最近的评论，另一行是下一个最近的评论。这是 SQL 的正常行为，在此答案下添加注释之前，问题的读者会认为这种正常行为是可以接受的。
该问题缺乏明确阐述的“预期结果”。

[选项 1:每个 Post 查询一行，最多 2 条评论，添加列]
在下面的评论中，有人透露您不希望每个帖子有 2 行，这很容易解决。嗯，这很容易，但是有选项，并且选项由用户以需求的形式指定。如果问题有“预期结果”，那么我们就会知道选择哪个选项。尽管如此，这是一种选择

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
      ;

See the second query working at SQLFiddle
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，将最近的评论连接到一个逗号分隔的列表中 **

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

See this third query working at SQLFiddle
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 |

** 概括 **
我提出了 3 个查询，每个查询只显示 2 个最近的评论，但每个查询以不同的方式进行。第一个查询(默认行为)将为每个帖子显示 2 行。选项 2 添加一列但删除第二行。选项 3 连接了 2 个最近的评论。
请注意:

问题缺少涵盖所有列的表定义

问题缺少任何样本数据，这让您更难理解此处提供的任何结果，也让我们更难准备解决方案

该问题也缺乏明确的“预期结果”(想要的输出)，这导致回答

时更加复杂

我确实希望提供的附加信息会有用，现在您也知道 SQL 将数据显示为多行是正常的。如果您不希望出现这种正常行为，请具体说明您在问题中真正想要的是什么。

后记。要包含另一个“跟随”子查询，您可以使用与您已有的子查询类似的子查询。它可以在该子查询之前或之后添加。您也可以在 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. 查询的解释计划。)

关于php - MYSQL 查询 - 获取与帖子相关的最新评论，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/40268501/

有关php - MYSQL 查询 - 获取与帖子相关的最新评论的更多相关文章

ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2
我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.
ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2
我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr
ruby-on-rails - 相关表上的范围为 "WHERE ... LIKE" - 2
我正在尝试从Postgresql表(table1)中获取数据，该表由另一个相关表(property)的字段(table2)过滤。在纯SQL中，我会这样编写查询:SELECT*FROMtable1JOINtable2USING(table2_id)WHEREtable2.propertyLIKE'query%'这工作正常:scope:my_scope,->(query){includes(:table2).where("table2.property":query)}但我真正需要的是使用LIKE运算符进行过滤，而不是严格相等。然而，这是行不通的:scope:my_scope,->(que
ruby - 简单获取法拉第超时 - 2
有没有办法在这个简单的get方法中添加超时选项？我正在使用法拉第3.3。Faraday.get(url)四处寻找，我只能先发起连接后应用超时选项，然后应用超时选项。或者有什么简单的方法？这就是我现在正在做的:conn=Faraday.newresponse=conn.getdo|req|req.urlurlreq.options.timeout=2#2secondsend 最佳答案试试这个:conn=Faraday.newdo|conn|conn.options.timeout=20endresponse=conn.get(url
ruby - 从 Ruby 中的主机名获取 IP 地址 - 2
我有一个存储主机名的Ruby数组server_names。如果我打印出来，它看起来像这样:["hostname.abc.com","hostname2.abc.com","hostname3.abc.com"]相当标准。我想要做的是获取这些服务器的IP(可能将它们存储在另一个变量中)。看起来IPSocket类可以做到这一点，但我不确定如何使用IPSocket类遍历它。如果它只是尝试像这样打印出IP:server_names.eachdo|name|IPSocket::getaddress(name)pnameend它提示我没有提供服务器名称。这是语法问题还是我没有正确使用类？输出:ge
ruby - 获取模块中定义的所有常量的值 - 2
我想获取模块中定义的所有常量的值:moduleLettersA='apple'.freezeB='boy'.freezeendconstants给了我常量的名字:Letters.constants(false)#=>[:A,:B]如何获取它们的值的数组，即["apple","boy"]？最佳答案为了做到这一点，请使用mapLetters.constants(false).map&Letters.method(:const_get)这将返回["a","b"]第二种方式:Letters.constants(false).map{|c
ruby-on-rails - 获取 inf-ruby 以使用 ruby 版本管理器 (rvm) - 2
我安装了ruby版本管理器，并将RVM安装的ruby实现设置为默认值，这样'哪个ruby'显示'~/.rvm/ruby-1.8.6-p383/bin/ruby'但是当我在emacs中打开inf-ruby缓冲区时，它使用安装在/usr/bin中的ruby。有没有办法让emacs像shell一样尊重ruby的路径？谢谢! 最佳答案我创建了一个emacs扩展来将rvm集成到emacs中。如果您有兴趣，可以在这里获取:http://github.com/senny/rvm.el
Ruby 从大范围中获取第 n 个项目 - 2
假设我有这个范围:("aaaaa".."zzzzz")如何在不事先/每次生成整个项目的情况下从范围中获取第N个项目？最佳答案一种快速简便的方法:("aaaaa".."zzzzz").first(42).last#==>"aaabp"如果出于某种原因你不得不一遍又一遍地这样做，或者如果你需要避免为前N个元素构建中间数组，你可以这样写:moduleEnumerabledefskip(n)returnto_enum:skip,nunlessblock_given?each_with_indexdo|item,index|yieldit
ruby-on-rails - 简单的 Ruby on Rails 问题——如何将评论附加到用户和文章？ - 2
我意识到这可能是一个非常基本的问题，但我现在已经花了几天时间回过头来解决这个问题，但出于某种原因，Google就是没有帮助我。(我认为部分问题在于我是一个初学者，我不知道该问什么......)我也看过O'Reilly的RubyCookbook和RailsAPI，但我仍然停留在这个问题上.我找到了一些关于多态关系的信息，但它似乎不是我需要的(尽管如果我错了请告诉我)。我正在尝试调整MichaelHartl'stutorial创建一个包含用户、文章和评论的博客应用程序(不使用脚手架)。我希望评论既属于用户又属于文章。我的主要问题是:我不知道如何将当前文章的ID放入评论Controller。
ruby - Net::HTTP 获取源代码和状态 - 2
我目前正在使用以下方法获取页面的源代码:Net::HTTP.get(URI.parse(page.url))我还想获取HTTP状态，而无需发出第二个请求。有没有办法用另一种方法做到这一点？我一直在查看文档，但似乎找不到我要找的东西。最佳答案在我看来，除非您需要一些真正的低级访问或控制，否则最好使用Ruby的内置Open::URI模块:require'open-uri'io=open('http://www.example.org/')#=>#body=io.read[0,50]#=>"["200","OK"]io.base_ur

php - MYSQL 查询 - 获取与帖子相关的最新评论

有关php - MYSQL 查询 - 获取与帖子相关的最新评论的更多相关文章

随机推荐