PHP - Problem with search return INNER JOIN with GROUP BY!

Question

So, I have 3 table:

user:
    | ID | NOME | SOBRENOME |
    | 01 | Igor | Ferreira  |
    | 02 | João | Henrique  |
    | 03 | Rose | Beltão    |

following:
    | ID | user1_id | user2_id |
    | 01 |    01    |    02    |  User 1 > Segue User 2
    | 02 |    02    |    01    |     -- User 2 > Segue User 1
    | 03 |    01    |    03    |  User 1 > Segue User 3
    | 04 |    03    |    01    |     -- User 3 > Segue User 1
    | 05 |    03    |    02    |  User 3 > Segue User 2
    | 06 |    02    |    03    |     -- User 2 > Segue User 3

chat:
    | ID | user1 | user2 |      mensagem      |       data       |
    | 01 |   02  |   03  | ola Rose.          | 30/07/2015 08:25 |  De João > Para Rose
    | 02 |   03  |   02  | Oi João, como tas? | 30/07/2015 08:28 |  De Rose > Para João
    | 03 |   02  |   03  | Estou bem, e você? | 30/07/2015 08:29 |  De João > Para Rose
    | 04 |   01  |   02  | Rose você esta ai? | 30/07/2015 09:11 |  De Igor > Para Rose

In a div I am listing the users who both follow and who brings the latest message according to the table chat:

$sqln = mysqli_query($conn,"SELECT
                                u.id,
                                u.foto,
                                u.username,
                                u.nome_us,
                                u.sobrenome_us,
                                u.tell
                            FROM users u
                                RIGHT JOIN following f
                                    ON f.user1_id = '$id'
                                INNER JOIN chat c
                                    ON ((c.id_de = '$id' and c.id_para = f.user2_id) or (c.id_de = f.user2_id and c.id_para = '$id'))
                            WHERE
                                u.id = f.user2_id
                            GROUP BY 
                                u.id
                            ORDER BY
                                c.data DESC, c.hora DESC");

But if I put it GROUP BY u.id, where it picks up all messages from a user and plays in a single box: however it does not sort according to the most recent messages in the chat table.

Now if I take the GROUP BY u.id it lists really according to the most recent, but it plays all messages without caring of repetitions:

How can I resolve?? Make it actually list most recently on the table chat and not with repetitions in a single div?

Answer 1

My query may be a bit complex, but that’s what I got without being familiar with the system and modeling.

SET @user = 2;

SELECT * FROM (
  SELECT
    z.id, 
    u.nome_us,
    -- u.foto,
    -- u.username,
    u.sobrenome_us,
    -- u.tell,
    (SELECT `data` FROM chat 
     WHERE 
       (id_de = @user AND id_para = z.id) OR
       (id_para = @user AND id_de = z.id)
     ORDER BY `data` DESC LIMIT 1
     ) AS 'data'
  FROM (
    SELECT DISTINCT x.id FROM (
        SELECT id_de AS id, `data`
        FROM chat WHERE id_para = @user LIMIT 20

        UNION DISTINCT

        SELECT DISTINCT id_para AS id, `data`
        FROM chat WHERE id_de = @user LIMIT 20
    ) AS x
  ) AS z
    INNER JOIN users u ON z.id = u.id
) AS j
  ORDER BY j.`data` DESC;

To query only returns the user data and the date of the last message, if you also want the last message, you can do in the same way that the date was searched, using a subquery.

Obs.: Note that I used the SQL variable in query @user, trade with PHP.

Answer 2

You have to make a select from the outside by grouping by user and a select from the inside by sorting. This way:

SELECT * FROM 
(SELECT 
  u.id AS idUsers,
  u.foto,
  u.username,
  u.nome_us,
  u.sobrenome_us,
  u.tell 
FROM
  users u 
  RIGHT JOIN following f 
    ON f.user1_id = '$id' 
  INNER JOIN chat c 
    ON (
      (
        c.id_de = '$id' 
        AND c.id_para = f.user2_id
      ) 
      OR (
        c.id_de = f.user2_id 
        AND c.id_para = '$id'
      )
    ) 
WHERE u.id = f.user2_id 
ORDER BY c.data DESC,
  c.hora DESC) AS mensagens
GROUP BY idUsers