Accueil Bonus

SQL: ORDER BY avant GROUP BY

Voici un problème auquel j’ai pu faire face ces derniers temps. Dans le cadre du développement d’une messagerie privée sous forme de conversation (type messagerie smartphone), l’objectif était de faire remonter dans les résultats SQL une conversation par rapport à un nouveau message reçu à l’intérieur.

Pour bien comprendre le problème, voici un schéma de la base de donnée:

Un utilisateur est lié à une conversation par la table de liaison « user_conv« .
Le message contient l’id de l’utilisateur expéditeur « id_exp_msg« .

L’idée est donc de classer les conversations en cours par la date de leur dernier message reçu. La requête de base permettant de lister les conversations était la suivante:

SELECT c.id_conversation, c.sujet_conversation, m.date_msg, m.id_exp_msg FROM ((user_conv uc INNER JOIN msg m ON uc.id_conversation_uc=m.id_conv_msg) INNER JOIN conversation c ON c.id_conversation=uc.id_conversation_uc) WHERE uc.id_user_uc='{ID_USER_DESTINATAIRE}' AND m.id_exp_msg!='{ID_USER_DESTINATAIRE}' GROUP BY c.id_conversation;

Le problème ici, c’est que la requête groupe bien les conversations ensemble, mais renvoie comme date de message (date_msg) la date du PREMIER message de la conversation. Nous souhaitons avoir celle du DERNIER.

Malheureusement, un simple ORDER BY m.date_msg DESC à la fin de la requête ne suffit pas, puisque cela va trier APRÈS le résultat du GROUP BY. On va donc obtenir les conversations, effectivement triées par date de message, mais par la date de leur PREMIER message.

Bon, si on prend le problème à l’envers et en français, on souhaite finalement récupérer les messages classés par date décroissante, pour finalement les grouper. C’est donc dans cet ordre que j’ai pensé à la solution. On va d’abord aller chercher tous les messages correspondant au user, avec le order by, puis sur la base de ce résultat on va grouper les conversations.

Requête de sélection des messages correspondants:

SELECT c.id_conversation, c.sujet_conversation, m.date_msg, m.id_exp_msg FROM ((user_conv uc INNER JOIN msg m ON uc.id_conversation_uc=m.id_conv_msg) INNER JOIN conversation c ON c.id_conversation=uc.id_conversation_uc) WHERE uc.id_user_uc='{ID_USER_DESTINATAIRE}' AND m.id_exp_msg!='{ID_USER_DESTINATAIRE}' ORDER BY m.date_msg DESC;

Puis sur la base de ces résultats on va grouper par conversation:

SELECT results.id_conversation, results.sujet_conversation, results.date_msg, results.id_exp_msg FROM (SELECT c.id_conversation, c.sujet_conversation, m.date_msg, m.id_exp_msg FROM ((user_conv uc INNER JOIN msg m ON uc.id_conversation_uc=m.id_conv_msg) INNER JOIN conversation c ON c.id_conversation=uc.id_conversation_uc) WHERE uc.id_user_uc='{ID_USER_DESTINATAIRE}' AND m.id_exp_msg!='{ID_USER_DESTINATAIRE}' ORDER BY m.date_msg DESC) results GROUP BY results.id_conversation ORDER BY results.date_msg DESC;

Je ne sais pas si la solution que j’ai employée pour résoudre ce problème est la plus optimisée, mais j’ai procédé avec logique. Je compte sur vous pour réagir en commentaire si vous connaissez une solution plus rapide en exécution que celle-ci. Si possible également, accompagner cette solution d’une rapide analyse en temps.

Pour moi, sur une base de plus de 3000 conversations de plus de 5000 messages, ca me donne le temps d’exécution suivant:

Temps d'execution : 0.0203
Temps d'execution : 0.0214
Temps d'execution : 0.0213
Temps d'execution : 0.0221
Temps d'execution : 0.0211
Temps d'execution : 0.0206
Temps d'execution : 0.0205
Temps d'execution : 0.0208
Temps d'execution : 0.0204
Temps d'execution : 0.0205

Ce qui reste assez convenable.

Si vous avez mieux, à vos claviers!

3 Commentaires

  1. 28 juillet 2011 at 14 h 58 min

    ce que je cherchais, merci

  2. jules
    6 avril 2013 at 19 h 02 min

    cela a egalement resolu mon probleme, merci !

  3. 3 septembre 2014 at 17 h 03 min

    Merci beaucoup !!! C’est la solution que je cherchais depuis plus d’2h… :D

Ajouter un commentaire

Votre Email n'est jamais publiée ou partagée. Les champs requis sont marqués *.

*
*