MySQL查询，按条件分组，然后按最新分组

Omari Victor Omosa 发表于 Dev

奥玛丽·维克多·奥莫萨（Omari Victor Omosa）

我有这段代码，可以根据已登录用户的ID对消息进行分组，该代码可以很好地工作。但是现在我想做一些我不知道要实现的修改

我的查询代码

 $query_messages = mysqli_query($link,"SELECT 
      PreQuery.*,
      m2.message,
      m2.senderID,
      m2.receiverID
   from
      ( SELECT 
              if( m.senderID < m.receiverID, m.senderID, m.receiverID ) as Person1,
              if( m.senderID < m.receiverID, m.receiverID, m.senderID ) as Person2,
              max( m.messageID ) as LastMessageIDPerChat,
              max( m.date ) as LastMessageDate
           FROM 
              messages m
           WHERE 
                   '$user' IN ( senderID, receiverID ) 
           GROUP BY
              Person1, 
              Person2
           ORDER BY 
              m.date ASC
           LIMIT 10 ) PreQuery

         JOIN messages m2
            on PreQuery.LastMessageIDPerChat = m2.messageID    ORDER BY 
              date DESC");
$row_messages = mysqli_fetch_assoc($query_messages);
$totalRows_messages = mysqli_num_rows($query_messages);

数据库结构

简要说明

假设*已登录用户的变量为$ user

在数据库中，用户可以是senderID或ReceiverID

当$用户删除他的谈话中，他的消息从更新0到1的senderdelete列或列receiverdelete *

问题是：

我希望修改MySQLI查询代码并将其添加到查询中

if $user is the sender then senderdelete should be value 0

和

if $user is the receiver then receiverdelete should be value 0

有什么帮助吗？

奥玛丽·维克多·奥莫萨（Omari Victor Omosa）

我在where子句中添加了它，它似乎正在工作

WHERE 
'$myid' IN ( senderID ) AND senderdelete = 0 
OR
'$myid' IN ( receiverID ) AND receiverdelete = 0

完整的更新代码

 $query_messages = mysqli_query($link,"SELECT 
      PreQuery.*,
      m2.message,
      m2.senderID,
      m2.receiverID,
      m2.senderdelete,
      m2.receiverdelete
   from
      ( SELECT 
              if( m.senderID < m.receiverID, m.senderID, m.receiverID ) as Person1,
              if( m.senderID < m.receiverID, m.receiverID, m.senderID ) as Person2,
              max( m.messageID ) as LastMessageIDPerChat,
              max( m.date ) as LastMessageDate
           FROM 
              messages m
           WHERE 
                   '$myid' IN ( senderID) AND senderdelete = 0 OR '$myid' IN ( receiverID) AND receiverdelete = 0 

           GROUP BY
              Person1, 
              Person2
           ORDER BY 
              m.date ASC
           LIMIT 10 ) PreQuery

         JOIN messages m2
            on PreQuery.LastMessageIDPerChat = m2.messageID    ORDER BY 
              date DESC");
$row_messages = mysqli_fetch_assoc($query_messages);
$totalRows_messages = mysqli_num_rows($query_messages);