从每个对话中获取最后一条消息

I know similar questions had been asked before, but none of them had this same conditions and their answers didn't work for this case.

我之前曾经问过类似的问题，但没有一个人有同样的条件，他们的答案对这个案子不起作用。

The table containing the messages looks like this:

包含消息的表如下所示：

id | owner_id | recipient_id | content      | created
 1 |        1 |            2 | Hello        | 2015-12-08 20:00
 2 |        2 |            1 | Hey          | 2015-12-08 20:10
 3 |        3 |            1 | You there?   | 2015-12-08 21:00
 4 |        1 |            3 | Yes          | 2015-12-08 21:15
 5 |        4 |            1 | Hey buddy    | 2015-12-08 22:00

And let's say I query the conversations for User ID 1, the expected result is:

让我们说我查询用户ID 1的对话，预期结果是：

id | owner_id | recipient_id | content      | created
 5 |        4 |            1 | Hey buddy    | 2015-12-08 22:00
 4 |        1 |            3 | Yes          | 2015-12-08 21:15
 2 |        2 |            1 | Hey          | 2015-12-08 20:10

I tried many combinations, using JOINs and sub-queries but none of them gave the expected results.

我尝试了许多组合，使用JOIN和子查询，但没有一个给出预期的结果。

I'm looking for an answer in MySQL but this will be used in a project developed under CakePHP 2, so if there is a Cake-specific way of doing this it would be great.

我正在寻找MySQL中的答案，但这将用于CakePHP 2下开发的项目中，所以如果有一个特定于Cake的方法，那就太棒了。

Here is one of the queries I tried with but it's not working. I believe is not even near to what I'm needing.

这是我尝试过的一个查询，但它不起作用。我相信甚至不能满足我的需求。

SELECT
    IF ( owner_id = 1, recipient_id, owner_id ) AS Recipient,

    (
        SELECT
            content
        FROM
            messages

        WHERE
            ( owner_id = 1 AND recipient_id = Recipient  )
        OR
            ( owner_id = Recipient AND recipient_id = 1 )

        ORDER BY
            created DESC

        LIMIT 1
    )
FROM
    messages

WHERE
    owner_id = 1
OR
    recipient_id = 1

GROUP BY
    Recipient;

2 个解决方案

#1

select t.* 
    from 
        t 
      join 
        (select user, max(created) m  
            from 
               (
                 (select id, recipient_id user, created 
                   from t 
                   where owner_id=1 ) 
               union 
                 (select id, owner_id user, created
                   from t 
                   where recipient_id=1)
                ) t1
           group by user) t2
     on ((owner_id=1 and recipient_id=user) or 
         (owner_id=user and recipient_id=1)) and 
         (created = m)
   order by created desc

example on sqlfiddle

在sqlfiddle上的示例

#2

This should do the trick:

这应该是诀窍：

$joins = array(
    array('table' => 'conversations',
        'alias' => 'Conversation2',
        'type' => 'LEFT',
        'conditions' => array(
            'Conversation.id < Conversation2.id',
            'Conversation.owner_id = Conversation2.owner_id',
        )
    ),
    array('table' => 'conversations',
        'alias' => 'Conversation3',
        'type' => 'LEFT',
        'conditions' => array(
            'Conversation.id < Conversation3.id',
            'Conversation.recepient_id = Conversation3.recepient_id',
        )
    )

);

$conditions = array(
    'OR' => array(
        array(
            'Conversation2.id'=>null,
            'Conversation.owner_id' => $ownerId
        ),
        array(
            'Conversation3.id'=>null,
            'Conversation.recipient_id' => $ownerId
        ),
     )
);

$order = array('Conversation.created'=>'DESC');

$lastConversations=$this->Conversation->find('all',compact('conditions','joins','order'));

Provided that the name of the table is conversations and the name of your model is Conversation. It's based on the technique described in the accepted answer of Retrieving the last record in each group.

前提是表的名称是对话，而模型的名称是Conversation。它基于在每组中检索最后一条记录的已接受答案中描述的技术。