Jointure et GROUP BY sur 2 colonnes [Résolu]

[sidacoq]

Bonjour,

Voila 4 heures que je me prends la tête sur cette requête où je bloque ...
Pouvez vous m'aider ?

Voila mon début de ma requête SQL :

Code :

1
2
3
4
5
6
7
SELECT *
FROM user_message
WHERE fk_user2_id =1
OR fk_user1_id =1
GROUP BY fk_user2_id
ORDER BY time_id ASC
LIMIT 0 , 30

Resultat MYSQL :



Au final, je souhaite la même requête mais en regroupant la ligne 2 et 3.
Je souhaite pouvoir faire un group BY avec la ligne précédente.
Pour que si fk_user1_id et fk_user2_id sont identique même inversé, la valeur qui sera retourné sera la valeur où le fk_time_id est le maximum.

Pas facile comme requête mais merci de vos aides.

Merci.
En attente de votre retour.

[Oishiiii]

Est-ce que vous pourriez nous fournir la structure de la table au format SQL (CREATE TABLE..) ainsi qu'un jeu d'essai (quelques INSERT INTO..), ça nous aidera à vous aider

[CinePhil]

1) Quel est le fk_time_id maxi pour chaque fk_user2_id où fk_user1_id = 1

Code :

1
2
3
4
SELECT fk_user2_id AS autre_user, MAX(fk_time_id) AS Max_time
FROM user_message
WHERE fk_user1_id = 1
GROUP BY fk_user2_id

2) Quel est le fk_time_id maxi pour chaque fk_user1_id où fk_user2_id = 1

Code :

1
2
3
4
SELECT fk_user1_id AS autre_user, MAX(fk_time_id) AS Max_time
FROM user_message
WHERE fk_user2_id = 1
GROUP BY fk_user1_id

3) On réunit les deux requêtes

Code :

1
2
3
4
5
6
7
8
9
SELECT fk_user2_id AS autre_user, MAX(fk_time_id) AS Max_time
FROM user_message
WHERE fk_user1_id = 1
GROUP BY fk_user2_id
UNION
SELECT fk_user1_id AS autre_user, MAX(fk_time_id) AS Max_time
 FROM user_message
 WHERE fk_user2_id = 1
 GROUP BY fk_user1_id

4) On fait refait un groupement sur l'ensemble des deux requêtes

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT tmp.autre_user, MAX(tmp.Max_time_id) AS Max_time
FROM
(
    SELECT fk_user2_id AS autre_user, MAX(fk_time_id) AS Max_time_id
    FROM user_message
    WHERE fk_user1_id = 1
    GROUP BY fk_user2_id
    UNION
    SELECT fk_user1_id AS autre_user, MAX(fk_time_id) AS Max_time_id
    FROM user_message
    WHERE fk_user2_id = 1
    GROUP BY fk_user1_id
) tmp
GROUP BY tmp.autre_user

[sidacoq]

Merci de t'interesser à mon problème
Voila la structure de la table avec quelques <insert>.

Code :

1
2
3
4
5
6
7
8
CREATE TABLE IF NOT EXISTS `user_message` (
  `user_message_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `fk_message_id` int(10) UNSIGNED NOT NULL,
  `fk_user1_id` int(10) UNSIGNED NOT NULL,
  `fk_user2_id` int(10) UNSIGNED NOT NULL,
  `fk_time_id` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`user_message_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
INSERT INTO `user_message` (`user_message_id`, `fk_message_id`, `fk_user1_id`, `fk_user2_id`, `fk_time_id`) VALUES
(1, 1, 1, 2, 15754),
(2, 5, 1, 2, 15755),
(3, 6, 1, 2, 15757),
(4, 7, 1, 2, 15782),
(5, 8, 1, 2, 15790),
(6, 9, 1, 2, 15833),
(7, 10, 1, 2, 15910),
(8, 11, 1, 2, 15941),
(9, 12, 1, 2, 15945),
(10, 13, 2, 1, 15987),
(11, 14, 1, 2, 21045),
(12, 15, 1, 2, 22417),
(13, 16, 1, 2, 22419),
(14, 17, 1, 2, 22425),
(15, 18, 1, 2, 22426),
(16, 19, 1, 2, 22428),
(17, 20, 1, 2, 22434),
(18, 21, 1, 2, 22846),
(19, 21, 1, 2, 22847),
(20, 22, 1, 2, 22913),
(22, 24, 1, 3, 23043);

Merci.

[sidacoq]

grand merci.
Toutefois, je vais peux être chercher autre chose pour optimiser la requete.
Peux-etre découper la table en 2 tables ? Qu'en dites vous ?

[Oishiiii]

La réponse de CinePhil vous suffit ? Car il est a priori beaucoup plus complexe d'obtenir le résultat que vous demandez dans le premier message, avec les 5 colonnes dans le résultat.

Il y a peut-être un soucis au niveau de la conception de cette table (et de ses voisines ?) effectivement, notamment autour des deux colonnes fk_userX_id qui me chagrinent.

A quoi sert cette table (et ses colonnes) ? A quelle "question" essayez-vous de répondre avec cette requête ?

[sidacoq]

Bonsoir,

Merci de vos réponses.
Mais je comprends maintenant, que le problème est un problème de conception de tables. Oui ... je me suis un peu trop pressé sur cette table ci.

Il s'agit d'une messagerie.
Avec user1 -> celui qui envoie le message
user2 -> celui qui le recoit.

Avec cette requete, je voulais connaitre le dernier message recoi ou envoyé pour chaque correspondant.

[estofilo]

Dans ce cas, le plus simple est d'avoir une sous-requête qui mette d'abord dans la même colonne user1 et user2, sur le modèle de:

Code :

1
2
3
SELECT user1 AS correspondant,time_id, message_id FROM message
UNION ALL
SELECT user2 AS correspondant,time_id, message_id FROM message

Ca ramène le problème à un classique GROUP BY sur ces résultats avec calcul du max(time_id) et si nécessaire re-jointure sur la table message pour retrouver le message complet correspondant (toutes les colonnes)

[sidacoq]

Merci pour vos réponses.
Toutefois, je préfère revoir mon SGBD.
Les solutions que vous me proposez semblent alourdir le temps d’exécution des requêtes quand j'aurai un nombre conséquent d'utilisateurs.
Merci pour vos actions.

[CinePhil]

A priori, ta table est plutôt bien modélisée, à part l'identifiant user_message_id inutile puisqu'il s'agit d'une table associative.

Avec cette requete, je voulais connaitre le dernier message reçu ou envoyé pour chaque correspondant.

Le début de solution proposé par estofilo est plus simple que ma solution puisqu'au final il n'y a plus qu'un GROUP BY à faire.

1) Quel est le dernier fk_time_id de chaque correspondant (fk_user1 ou fk_user2) ?

Code :

1
2
3
4
5
6
7
8
9
SELECT t1.correspondant, MAX(t1.fk_time_id) AS id_time_dernier_message
FROM
(
    SELECT fk_user1_id AS correspondant, fk_time_id
    FROM user_message
    UNION ALL
    SELECT fk_user2 AS correspondant, fk_time_id 
    FROM user_message
) t1

2) À quel fk_message_id correspond ce fk_time_id pour chaque correspondant ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT t2.correspondant, um.fk_message_id
FROM user_message um
INNER JOIN
(
    SELECT t1.correspondant, MAX(t1.fk_time_id) AS id_time_dernier_message
    FROM
    (
        SELECT fk_user1_id AS correspondant, fk_time_id
        FROM user_message
        UNION ALL
        SELECT fk_user2 AS correspondant, fk_time_id 
        FROM user_message
    ) t1    
    GROUP BY tmp.correspondant
) t2 ON t2.id_time_dernier_message = um.fk_time_id
WHERE t2.correspondant = um.fk_user1_id
    OR t2.correspondant = um.fk_user2_id

Pas tout à fait sûr que ça fonctionne à cause du OR mais à essayer.

[sidacoq]

Merci j'ai testé et cela fonctionne plutôt bien à voir avec plus de données combien de temps la requête met à s'exécuter.
Je ne suis pas fan du "UNION ALL" il me semble que son utilisation alourdi fortement la requête ?