Home / Questions / 44647
Accepted
Máxima Alekz
Asked: 2020-01-18 22:59:58 +0800 CST 2020-01-18 22:59:58 +0800 CST

MySQL - Detail with a query

  • 772

I have a database called messenger , made up of 3 tables

Table - users

Here the personal information of each user is saved.

 ______ _________ _________ ________
| idUs | nomUs   | emailUs | codeUs |
 ────── ───────── ───────── ────────
|   1  | Andrey  | [email protected] | 123456 |
 ────── ───────── ───────── ────────
|   2  | Paola   | [email protected] | 123456 |
 ────── ───────── ───────── ────────
|   3  | Felipe  | [email protected] | 123456 |
 ────── ───────── ───────── ────────
|   4  | Daniela | [email protected] | 123456 |
 ────── ───────── ───────── ────────

Table - messages_token

Here joins are made of the user IDs that are chatting.

That is to say that for example : 1.2 => Andrey & Paola

 ______ _____________
| idMT | Descripcion |
 ────── ─────────────
|   1  |     1.2     |
 ────── ─────────────
|   2  |     1.3     |
 ────── ─────────────
|   3  |     1.4     |
 ────── ─────────────
|   4  |     2.2     |
 ────── ─────────────

Table - messages

Here each message is recorded, including its TokenID to know who is talking to whom.

 ______ __________ __________ ____________ _____________________ _________
|  id  | usuarioA | usuarioB | mensaje    | date                | TokenID |
 ────── ────────── ────────── ──────────── ───────────────────── ───────── 
|   1  |     1    |     2    |   Hola     | 2017-01-17 02:52:00 |    1    |
 ────── ────────── ────────── ──────────── ───────────────────── ───────── 
|   2  |     2    |     1    |   Hi!      | 2017-01-17 02:53:00 |    1    |
 ────── ────────── ────────── ──────────── ───────────────────── ───────── 
|   3  |     1    |     2    |   Qué más  | 2017-01-17 02:54:00 |    1    |
 ────── ────────── ────────── ──────────── ───────────────────── ───────── 
|   4  |     2    |     1    | Nada, aquí | 2017-01-17 02:55:00 |    1    |
 ────── ────────── ────────── ──────────── ───────────────────── ─────────

I have the following query:

SELECT 
   TokenID,usuarios.nomUs as destinatario,
   mensaje,
   date
FROM
   mensajes,usuarios
WHERE
   (usuarioA = ? OR usuarioB = ?) AND
   mensajes.usuarioA = usuarios.idUs AND
   usuarios.idUs <> ?
GROUP BY
   TokenID
ORDER BY
   mensajes.date DESC

What I want with the query is that it be organized by date descendingly , and that the TokenID is NOT repeated, as if it were DISTINCT but showing the rest of the columns . The use of <> is so that the recipient is not "me" but always the other user.

My query doesn't work as I expect, it doesn't repeat TokenID but selects the first message, ie. Instead of throwing:

 _________ ________________ ____________ _____________________
| TokenID |  destinatario  |   mensaje  |        date         |
 ───────── ──────────────── ──────────── ─────────────────────
|    1    | Paola Gonzales | Nada, aquí | 2017-01-17 02:55:00 |
 ───────── ──────────────── ──────────── ─────────────────────

It is resulting in:

 _________ ________________ ____________ _____________________
| TokenID |  destinatario  |   mensaje  |        date         |
 ───────── ──────────────── ──────────── ─────────────────────
|    1    | Paola Gonzales |    Hola    | 2017-01-17 02:52:00 |
 ───────── ──────────────── ──────────── ─────────────────────

I also want to be able to find the latest messages from other users. How can I solve my problem?

mysql

1 Answers

  1. Best Answer

    As I see it, what you need for the last received message is to consult directly as you say, with an order by. however, we have to take into account that the best way to consult these tables is to determine which is going to be the table that marks the rhythm or main and which is the secondary one.

    In this case, the table that must be the main table is the message table and the secondary table is the user table, from which information will only be taken.

    To get a certain number of messages it is only a matter of changing that 1 of the limit for the value you need

    SELECT      MEN.tokenId 'TOKENID',
            USU.NOMUS 'DESTINATARIO',
            MEN.MENSAJE 'MENSAJE',
            MEN.DATE 'DATE'
FROM        mensajes  MEN
INNER JOIN  USUARIOS  USU
ON          MEN.USUARIOB = USU.IDUS
WHERE       (USU.IDUS = 1 OR USU.IDUS = 2)
order by    MEN.date desc limit 1
    • 2