MySQL - Detail with a query

5

I have a database called messaging , consisting of 3 tables

  

Table - users

Here the personal information of each user is stored.

 ______ _________ _________ ________
| idUs | nomUs   | emailUs | codeUs |
 ────── ───────── ───────── ────────
|   1  | Andrey  | g1@d.co | 123456 |
 ────── ───────── ───────── ────────
|   2  | Paola   | g2@d.co | 123456 |
 ────── ───────── ───────── ────────
|   3  | Felipe  | g3@d.co | 123456 |
 ────── ───────── ───────── ────────
|   4  | Daniela | g4@d.co | 123456 |
 ────── ───────── ───────── ────────
  

Table - texting_token

Here, unions of the user IDs that are chatting are made.

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

Each message is recorded here, including your TokenID to know who you are talking to.

 ______ __________ __________ ____________ _____________________ _________
|  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, organize by date descendant , and that NO repeat the TokenID, as if it were DISTINCT but showing the other columns . The use of < > is so that the recipient is not "me" but always the other user.

My query does not work as I expect, it does not repeat TokenID but it selects the first message, that is. 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 last messages of the other users. How can I solve my problem?

    
asked by Máxima Alekz 18.01.2017 в 07:59
source

1 answer

2

As I see it, what you need for the last message received is to consult directly as you say, with an order by. However, we must bear in mind that the best way to consult these tables is to determine which table is going to make the rhythm or main and which is the secondary.

In this case, the table that should be the main one is the message table and the secondary one, the user table, where only information will be taken

To get a certain number of messages it's just 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
    
answered by 19.01.2017 / 20:26
source