基于加入结果的订单选择(发送最后一条消息时的排序对话) [英] Order Select Based on Join Results (Sort Conversations on Last Message Sent)

问题描述

我正在Hibernate中为消息传递组件创建一个SQL查询.我的想法是我要查询用户的会话 ，并按上次发送邮件的日期进行排序.

I am creating an SQL query in Hibernate for a messaging component. The idea is that I am querying to get conversations for a user, sorted on the date of the last message sent.

我有两个表:

conversations

messages

I have two tables:

conversations

messages

在我的选择查询中，我正在尝试类似的操作，但是顺序永远不会在最后发送的消息上发生.

In my select query I am attempting something like this but the ordering never happens on the last message sent.

String sql =
            "SELECT * FROM conversations " +
            "JOIN messages ON messages.conversation_id = conversations.id "+
            "WHERE (conversations.creator_id = :userId OR conversations.to_id = :userId)" +
            "GROUP BY messages.conversation_id "+
            "ORDER BY messages.created DESC";

推荐答案

该问题是由于MySQL对GROUP BY子句的行为进行了扩展.其他数据库将引发错误……类似于SELECT列表中的聚合."(如果我们在sql_mode中包含ONLY_FULL_GROUP_BY，我们可以使MySQL引发类似的错误.)

The issue is due to a MySQL-specific extension to the behavior of the GROUP BY clause. Other databases would throw an error... something akin to on-aggregate in SELECT list". (We can get MySQL to throw a similar error, if we include ONLY_FULL_GROUP_BY in sql_mode.)

表达式messages.created的问题是引用GROUP BY中不确定行中的值.在GROUP BY运算之后，ORDER BY运算会在处理过程中更晚地发生.

The issue with the expression messages.created is that refers to a value from an indeterminate row in the GROUP BY. The ORDER BY operation occurs much later in the processing, after the GROUP BY operation.

要获取为每个组创建的最新"消息，请使用 aggregate 表达式MAX(messages.created).

To get the "latest" created for each group, use an aggregate expression MAX(messages.created).

要从同一行中获取其他值，则要复杂一些.

To get the other values from that same row, is a little more complicated.

假定created在给定的conversation_id组中为唯一(或者，如果不能保证它不是唯一的，则可以返回created ...

Assuming that created is unique within a given conversation_id group (or, if there's no guaranteed that it's not unique, and you are okay with returning multiple rows with the same value for created...

要获取每个conversation_id的最新版本created

To get the latest created for each conversation_id

SELECT lm.conversation_id
     , MAX(lm.created) AS created
  FROM conversation lc
  JOIN message lm
    ON lm.conversation_id = lc.id
 WHERE (lc.creator_id = :userId OR lc.to_id = :userId)
 GROUP BY lm.conversation_id

您可以将其用作嵌入式视图，以获取最新的created

You can use that as an inline view, to get the whole row with that latest created

SELECT c.*
     , m.*
  FROM ( SELECT lm.conversation_id
              , MAX(lm.created) AS created
           FROM conversation lc
           JOIN message lm
             ON lm.conversation_id = lc.id
          WHERE (lc.creator_id = :userId OR lc.to_id = :userId)
          GROUP BY lm.conversation_id
       ) l
  JOIN conversation c
    ON c.id = l.conversation_id
  JOIN messages m
    ON m.conversation_id = l.conversation_id
   AND m.created         = l.created
 WHERE (c.creator_id = :userId OR c.to_id = :userId)

注意:

您可以添加ORDER BY子句以按需要对返回的行进行排序.

You can add an ORDER BY clause to order the rows returned however you need.

外部查询中的WHERE子句可能是多余的，并且是不必要的.

The WHERE clause on the outer query is likely redundant, and unnecessary.

我们宁愿避免使用SELECT *，而是明确列出要返回的表达式.

We prefer to avoid using SELECT *, and prefer to explicitly list the expressions to be returned.

这篇关于基于加入结果的订单选择(发送最后一条消息时的排序对话)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！