mysql - 将查询混合在一起

Question

我在 ColdFusion 中制作了一个使用不同 MySQL 表的聊天脚本。一个是存储对话的位置，另一个是定义谁发起对话以及谁接收对话的位置。

现在，我制作了一个脚本，可以让您与某人一起聊天。发送消息时，它会在 MySQL 表中添加一行，其中包含时间戳、消息 ID、发送消息的用户 ID 和消息本身。整个对话通过以下代码显示:

// Get all conversations started by the user that is signed in 
<cfquery name = "Friendship" datasource = "#DSN#">
    SELECT *
    FROM  `conversations`
    WHERE  `user_one` LIKE  '#user.id#'
    ORDER BY  `conversations`.`lastmessage` DESC
</cfquery>

// Get all conversations started by others but receiver is the user that is signed  in                                                                                  
<cfquery name = "FriendshipBack" datasource = "#DSN#">
    SELECT *
    FROM  `conversations`
    WHERE  `user_two` LIKE  <cfqueryparam value="#user.id#" cfsqltype="cf_sql_integer">
    ORDER BY  `conversations`.`lastmessage` DESC
</cfquery>


// OUTPUT - conversations that I began
<cfoutput query="Friendship">
    // This code translates a user id into a name
    <cfquery name = "FriendshipToUsername" datasource = "#DSN#">
        SELECT *
        FROM users
        WHERE id = '#user_two#'
    </cfquery>

    <cfif IsDefined("FriendshipToUsername.username")>
        <cfif IsDefined('URL.chat') and URL.chat neq "">
        </cfif>                

       // Display username 
      <h3>#FriendshipToUsername.username#</h3>
    </cfif>

</cfoutput>

/// OUTPUT - conversations that I received
<cfoutput query="FriendshipBack">
    // This query translates a user id into a name
    <cfquery name = "FriendshipToUsernameBack" datasource = "#DSN#">
        SELECT *
        FROM users
        WHERE id = <cfqueryparam value="#user_one#" cfsqltype="cf_sql_integer">
    </cfquery>

    <cfif IsDefined('URL.chat') and URL.chat neq "">
    </cfif>                
    // Display username 

    <h3>#FriendshipToUsernameBack.username</h3>
</cfoutput>

此时聊天列表分为两类:一类是登录用户开始对话的列表，另一类是登录用户收到其他人的对话的列表。问题是，我不希望它以这种方式显示，所以我想将两个列表混合在一起并根据时间戳列出它们(从最新到最旧)

此时聊天列表分为两类:一类是登录用户开始对话的列表，另一类是登录用户收到其他人的对话的列表。问题是，我不希望它以这种方式显示，所以我想将两个列表混合在一起并根据时间戳列出它们(从最新到最旧)

有什么办法可以实现这一点吗？

我知道我的代码是可利用的，但这仍然是 WIP

Answer 1

At this moment the chat list is seperated into two categories: one is a list where the signed in user started the conversation, the other one is where the signed in user received a conversation from someone else.

然后只需查询其中任何一个条件为 true 的表，并按时间戳对记录进行排序。 (此外，正如另一个线程中提到的，当搜索精确匹配时，正确的运算符是等于，即=，而不是LIKE)。

  ...
  WHERE  conversations.user_one = <cfqueryparam value="#user.id#" 
                                           cfsqltype="cf_sql_integer">
  OR     conversations.user_two = <cfqueryparam value="#user.id#" 
                                           cfsqltype="cf_sql_integer">
  ORDER BY  conversations.lastmessage DESC

This code translates a user id into a name

如果可以避免的话，不要在循环内查询。增加了很多不必要的开销。检索相关列的正确方法是添加 JOIN您的查询。由于您必须多次加入 user 表(以获取两个名称)，因此请使用 别名 来区分:

  SELECT conversations.user_one
         , conversations.user_two
         , u1.username AS userNameOne
         , u2.username AS userNameTwo
         , ... 
  FROM   conversations 
            JOIN users u1 INNER JOIN u1.id = conversations.user_one
            JOIN users u2 INNER JOIN u2.id = conversations.user_two
  WHERE  ...