Múltiplos dados na consulta

[Insanity 9]

Olá pessoal, estou fazendo um chat e tenho a seguinte tabela no banco:

Quero listar as 5 últimas conversas de um usuário, mas a consulta é bem complexa, estou a um tempo tentando resolver com subqueries, mas não cheguei a nada, ela deve atender os seguintes requisitos:

• O usuário em questão pode ser tanto um remetente (sender_id) como um destinatário (recipient_id), então tem que buscar os dois, e quanto o usuário for sender_id, o recipient_id tem que receber um alias para ser um user_id (Veja o código adiante);
  
• Os resultados devem estar ordenados em DESC;
  
• Deve retornar a última mensagem da conversa;
  
• A mensagem não pode estar deletada para o usuário em questão (usei como boolean 0 para não e 1 para sim).
  

Os códigos:

-Onde listo as mensagens:

<?php foreach($list_messages as $msg) : ?>

    <?php $recipient = find_user($con, $msg['user_id']); ?>

        <a href="#" onclick="openPanelChat(<?php echo $recipient['user_id'] ?>)" class="dropdown-notification-content" style="background-color: <?php echo ($msg['seen'] == 0) ? 'rgba(28,146,243,0.08)' : 'white' ?>">

            <img src="<?php echo $recipient['picture'] ?>">

            <b><?php echo $recipient['name'] ?></b>

            <p><?php echo base64_decode($msg['msg']) ?></p>

            <span><?php echo translateDateFull($msg['registry']); ?></span>

        </a>

<?php endforeach; ?>

-A função que lista e retorna tudo em uma array

function list_messages($con, $user_id){

    $list_sql = "Essa é a consulta que quero!";

    $r = mysqli_query($con, $list_sql) or die(mysqli_error());

    $messages = array();

    while($each = mysqli_fetch_assoc($r)){

        $messages[] = $each;

    }

    return $messages;

}

Exemplo de como fica uma conversa entre id=27 e id=31. Isso é uma conversa, vamos supor que esse id=27 seja eu "Artur" e o 31 seja "Vitória", quero exibir a ultima mensagem da nossa conversa, mas eu (id=27) posso ser tanto sender_id quanto recipient_id:

Bom, é isso, o resultado deveria ser algo parecido com o do facebook!

[Motta 645]

Tente um UNION ALL

SELECT USER_ID,MAX(REGISTRY) MAXREGISTRY

FROM

(

SELECT SENDER_ID USER_ID,REGISTRY

FROM MESSAGES

UNION ALL

SELECT RECEIVE_ID,REGISTRY

FROM MESSAGES

) TABELA

GROUP BY USER_ID

[Insanity 9]

Não entendi a sua consulta, Motta! O que consegui até agora foi isso:

SELECT * FROM (SELECT IF(sender_id = 27, recipient_id, sender_id) as user_id, msg, registry, seen FROM messages_chat WHERE recipient_id = 27 OR sender_id = 27 ORDER BY id DESC) AS tabela GROUP BY user_id limit 5

Mas o problema é que o GROUP BY ignora a ordem (independente de já estar ordenado como na consulta acima), queria agora um jeito de fazer o GROUP BY que não ignorasse, tipo como um fazer o GROUP BY com o ORDER BY.

E também tem como fazer desse modo:

SELECT DISTINCT IF(sender_id = 27, recipient_id, sender_id) as user_id FROM messages_chat WHERE recipient_id = 27 OR sender_id = 27  ORDER BY id DESC

Mas só vem o user_id, e eu queria o msg, registry e seen.

[Motta 645]

SELECT USER_ID,MAX(REGISTRY) MAXREGISTRY

--da tabela virtaul acha a maior data seja sender ou receiver
FROM
(

--lendo a tabela messages obtendo o id dos "senders"
SELECT SENDER_ID USER_ID,REGISTRY
FROM MESSAGES

UNION ALL--une as consultas

--lendo a tabela messages obtendo o id dos "receivers"
SELECT RECEIVE_ID,REGISTRY
FROM MESSAGES

) TABELA-- cria um alias para a select

GROUP BY USER_ID

[Insanity 9]

Não está retornando ordenado, na realidade está, mas está ordenando o user_id, e não o id da mensagem.

[Motta 645]

SELECT ID,USER_ID,MAX(REGISTRY) MAXREGISTRY

FROM

(

SELECT ID,SENDER_ID USER_ID,REGISTRY

FROM MESSAGES

UNION ALL

SELECT ID,RECEIVE_ID,REGISTRY

FROM MESSAGES

) TABELA

GROUP BY ID,USER_ID

[Insanity 9]

Não agrupou desta vez haha. Acho que se conseguisse buscar mais dados além do USER_ID nessa consulta resolveria:

SELECT DISTINCT IF(sender_id = 27, recipient_id, sender_id) as user_id FROM messages_chat WHERE recipient_id = 27 OR sender_id = 27 ORDER BY id DESC

Digo, além de buscar só a coluna do user_id (o que o DISTINCT faz) pudesse vir junto msg, seen e registry. Tentei UNION ALL e INNER JOIN mas não deu em nada...