Trier les données d'une table en fonction de la date d'une autre

Version imprimable

Bonjour à tous,

Soit la table dossiers :

id nom date

1 Toto 2023-11-01

2 Tata 2023-11-02

3 Tutu 2023-11-03

et la table comments

id id_dossier comment datetime user

1 2 bla bla 2023-11-02 09:00:00 user

2 2 bla bla 2023-11-02 09:30:00 admin

3 2 bla bla 2023-11-02 19:30:00 user

4 1 bla bla 2023-11-01 19:30:00 user

5 1 bla bla 2023-11-01 20:00:00 admin

6 3 bla bla 2023-11-03 08:30:00 user

Comment trier dossier en fonction du champ datetime de comments sachant que le dossier qui comporte le dernier commentaire écrit par le user "user" doit s'afficher en 1er et les autres à la suite dans l'ordre de comments.datetime puis user "user" ?

Dans cet exemple on aurait : 2, 1 et 3

J'ai fait :

Code:

SELECT d.*, c.date FROM dossiers d LEFT JOIN comments c on d.id = c.id_dossier GROUP BY c.id_dossier ORDER BY c.datetime DESC, FIELD(user,'user')

Mais ce n'est pas bon. J'ai ajouté une clause GROUP BY pour ne pas que la boucle affiche les dossiers autant de fois qu'ils sont de commentaires...
Bref je sèche.
Merci de votre aide.

23/11/2023, 09h19
Séb.

Il y a différentes solutions, plus ou moins directes.

Cela va dépendre des colonnes que tu souhaites récupérer.

Quelles sont-elles ?
23/11/2023, 09h29
renaud26

Bonjour,

Je souhaite récupérer tous les champs de la table dossiers. De plus, il y a une clause WHERE dossiers.statut NOT IN(x,y) que je n'ai pas fait figurer dans mon exemple car cela ne me semblait pas utile.
23/11/2023, 12h28
escartefigue

il faut conditionner le tri en utilisant un CASE dans la clause ORDER BY
23/11/2023, 12h36
renaud26

Bonjour,

Aurais tu un exemple dans le cas de ma requête avec jointure ? Je regarde le sujet mais il n'y a pas grand chose...

id	nom	date
1	Toto	2023-11-01
2	Tata	2023-11-02
3	Tutu	2023-11-03

id	id_dossier	comment	datetime	user
1	2	bla bla	2023-11-02 09:00:00	user
2	2	bla bla	2023-11-02 09:30:00	admin
3	2	bla bla	2023-11-02 19:30:00	user
4	1	bla bla	2023-11-01 19:30:00	user
5	1	bla bla	2023-11-01 20:00:00	admin
6	3	bla bla	2023-11-03 08:30:00	user

1 pièce(s) jointe(s)

Voici

Création du jeu d'essai avec deux CTE :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
with DO_dossier (DO_ident, DO_nom, DO_date) as
    (select 1, 'tata', '2023-11-01'   union all
     select 2, 'toto', '2023-11-02'   union all
     select 3, 'tutu', '2023-11-03'
    )
   , CO_comment (Co_ident, DO_ident, CO_texte, CO_dth, CO_user) as
    (select 1, 2, 'bla bla bla', '2023-11-02 18:15:00', 'user'   union all
     select 2, 2, 'truc muche',  '2023-11-02 09:30:00', 'admin'  union all
     select 3, 2, 'bidule',      '2023-11-02 16:20:00', 'user'   union all
     select 4, 1, 'coucou',      '2023-11-01 19:30:00', 'user'   union all
     select 5, 1, 'azerty',      '2023-11-01 20:00:00', 'martin' union all
     select 6, 3, 'commentaire', '2023-11-03 08:30:00', 'josé'   union all
     select 7, 3, 'des trucs',   '2023-11-03 17:44:00', 'simone' union all  
     select 8, 3, 'tagada',      '2023-11-03 16:31:00', 'user'     
    )

Puis la requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select DO.DO_nom
     , DO.DO_date
     , CO.CO_texte
     , CO.CO_dth
     , CO_user
from  DO_dossier as DO
inner join
      CO_comment as CO
   on CO.DO_ident = DO.DO_ident
order by DO.DO_nom asc
       , case when CO.CO_user='user' then 1
              else CO.CO_dth
         end asc
    , CO_dth desc

Et enfin, le résultat :

Pièce jointe 646975

23/11/2023, 15h32
renaud26

Wahooooo !!!! 8O

Je ne connaissais pas du tout les CTE. Je tente de comprendre ce que tu as écrit avec l'aide du tuto https://sgbd.developpez.com/actu/101...riel-de-Lyche/

Je peux transcrire cette requête directement dans PhpMyadmin avec les données de mes 2 tables mySql ? Je dois créer une table temporaire avant ?
Désolé si mes questions te paraissent idiotes, mais je suis largué :oops:
23/11/2023, 16h08
renaud26

Les CTE ne semblent pas possibles avec MySQL 5.6 installé sur mon serveur :?
23/11/2023, 16h16
escartefigue

Les CTE (Common Table Expression) ne sont apparues que depuis la V8 de MySQL.

Il ne s'agit pas de tables temporaires, mais de requêtes corrélées qui permettent de mutualiser des requêtes, d'en simplifier l'écriture et qui sont indispensables en cas de requêtes récursive.

Pour le cas spécifique de ce fil de discussion, j'ai créé des CTE pour éviter d'avoir à créer de vraies tables avec des ordres CREATE TABLE, mais il n'est pas nécessaire de créer des CTE pour ce besoin précis, utilisez simplement ma requête sur vos tables en adaptant les noms des colonnes.
23/11/2023, 16h39
renaud26

Okay...donc j'ai adapté votre requête à mes tables.
Le truc c'est que sans GROUP BY, les dossiers sortent autant de fois qu'il y a de comments. Et sans GROUP BY, j'ai bien les dossiers mais l'ordre n'est plus respecté : le dossier qui comporte le dernier comment (la date la plus récente de la table comments) n'est plus en 1ère position.
Bon, je crois que je chauffe un peu, là :aie:
24/11/2023, 06h30
renaud26

Bon, je vais tenter de décomposer ma demande autrement.
Dans cette table messages

id id_dossier message date dest

1 1 bla bla 2023-11-01 admin

2 1 blo blo 2023-11-02 client

3 2 bla bla 2023-11-01 admin

4 2 blo blo 2023-11-02 client

5 2 bla bla 2023-11-02 Admin

Comment ordonner les résultats avec le id_dos 2 en premier puisque le dernier destinataire est "admin" ? En gros classer par date, les plus vieux messages d'abord SI le dernier destinataire est admin (donc que c'est à l'admin de répondre). Ou même, comment ne sélectionner QUE le 2 ?
24/11/2023, 08h11
escartefigue
C'est exactement ce que fait la requête que j'ai proposée hier à 14h29, sauf que dans la demande initiale c'était le destinataire "user" qui devait être mis en premier, maintenant c'est "admin", ce qui ne change rien au principe.

Pour plus de clarté, merci de communiquer un exemple de résultat attendu, avec un jeu d'essai en entrée plus riche, à savoir :
- présence de dossiers ayant plusieurs commentaires "admin" et au moins un commentaire d'un autre destinataire ;
- présence de dossiers ayant un seul commentaire "admin" et au moins un commentaire d'un autre destinataire ;
- présence de dossiers n'ayant aucun commentaire "admin" ;
- présence de dossiers n'ayant aucun commentaire autre.

Bonjour, oui je comprends que je ne suis pas très clair, je suis désolé. J'ai trop voulu simplifier mon post initial. En réalité ma table "dossiers" s'appelle suivi_sav et "comment" se nomme suivi_sav_fil

La structure de la table suivi_sav :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE TABLE `suivi_sav` ( `id` int(11) NOT NULL, `id_client` varchar(10) NOT NULL, `id_vendeur` int(11) NOT NULL, `id_admin` varchar(30) NOT NULL, `date` date NOT NULL, `prenom` varchar(30) NOT NULL, `nom` varchar(30) NOT NULL, `email` varchar(50) NOT NULL, `telephone` varchar(20) NOT NULL, `num_fact` varchar(10) NOT NULL, `produit` varchar(100) NOT NULL, `date_install` date NOT NULL, `probleme` text NOT NULL, `statut` enum('A traiter','Urgent','En cours','En attente','En attente retour','Résolu','Supprimer') NOT NULL DEFAULT 'A traiter' )
La structure de la table suivi_sav_fil :
Code:

1 2 3 4 5 6 7 8 CREATE TABLE `suivi_sav_fil` ( `id` int(11) NOT NULL, `id_sav` int(11) NOT NULL, `intervenant` varchar(10) NOT NULL, //peut être un code client type CLT09999 ou un code admin type ADM02 `message` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `date` datetime NOT NULL, `dest` varchar(255) DEFAULT NULL )
Lorsqu'un client crée un dossier SAV cela génère une entrée dans suivi_sav et son commentaire insère une entrée dans suivi_sav_fil avec le champ 'intervenant' = $id_client et 'dest' à 'admin'
Puis un admin répond au client et cela génère une autre entrée dans suivi_sav_fil avec 'intervenant' = $id_admin et 'dest' = 'client'
et les messages se succèdent, il peut y avoir une dizaine d'échanges mais pas forcément en séquence. Un admin peut très bien envoyer 2 réponses coup sur coup.

Dans le tableau d'affichage administration je veux afficher les dossiers une ligne par dossier dans l'ordre suivant :
1- Les messages qui attendent une réponse admin, c'est à dire ceux dont la dernière ligne de suivi_sav_fil a le champ 'dest' à 'admin' (où, donc, c'est le client qui a posté en dernier) même anciens
2- Les dossiers avec statut 'En attente' (par défaut) puis les autres statut avec un ORDER BY FIELD(statut,'x','y'...) avec les derniers arrivée en haut de liste

Mais peut-être que, dès le départ, mon système est mal pensé, conçu, articulé ?

24/11/2023, 09h07
Séb.

suivi_sav_fil.dest est inutile puisque dépendant de l'auteur du message
suivi_sav_fil.intervenant est VARCHAR(10) et suivi_sav.id_admin est VARCHAR(30) => Soit l'un est trop petit, soit l'un est trop grand

Citation:

1- Les messages qui attendent une réponse admin, c'est à dire ceux dont la dernière ligne de suivi_sav_fil a le champ 'dest' à 'admin' (où, donc, c'est le client qui a posté en dernier) même anciens
2- Les dossiers avec statut 'En attente' (par défaut) puis les autres statut avec un ORDER BY FIELD(statut,'x','y'...) avec les derniers arrivée en haut de liste

Pourquoi multiplier les règles et ne pas avoir un statut "En attente réponse admin" ?
Ainsi tout serait trié selon le statut.

Si tu veux poursuivre donne un jeu de lignes comme demandé plus haut et le résultat attendu en fonction.
24/11/2023, 09h36
renaud26

Je ne vois pas trop comment donner un jeu de lignes...sous quelle forme ?
J'avais ajouté le champ dest pour me simplifier la tâche, s'il s'avère qu'il est inutile, je le virerai.

Le résultat attendu est décrit : une ligne par dossier triés selon ce que j'ai dit : par "en attente réponse admin", statut, date DESC.
La ligne comprend les données ID - Dates (entrée dossier + dernier message) - Prénom + nom cli - TEL - Statut - Bouton permettant d'ouvrir le popup du fil de message.

Je ne peux pas utiliser un statut "attente réponse admin" car l'admin doit pouvoir modifier le statut comme il le souhaite et passer un dossier à "urgent", par exemple, (il passera alors en tête de liste - mais toujours après les "non répondus").

2 pièce(s) jointe(s)

C'est dommage d'avoir modifié les noms des tables et colonnes en cours de discussion, ça ne facilite pas la compréhension et comme j'avais préparé des tables et un jeu de données, tout est à refaire...

Bon ce n'est pas très grave, je vous livre deux requêtes

Tout d'abord, mon jeu d'essai enrichi comme expliqué plus haut, cette fois-ci sans passer par des CTE puisque vous êtes sous MySQL 5.6 (mais encore une fois, ça ne change rien aux requêtes qui suivent ):

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
create table DO_dossier
      (  DO_ident    integer        primary key
       , DO_nom      char(10)       not null
       , DO_date     date           not null
      )
;
insert into DO_dossier
      (DO_ident, DO_nom, DO_date)
values
      (1, 'dupond', '2022-11-03')
    , (2, 'lesage', '2022-11-20')
    , (3, 'martin', '2023-01-14')
    , (4, 'abadie', '2023-01-30')  
;
create table CO_comment
      (  CO_ident    integer        primary key
       , DO_ident    integer        not null
       , CO_texte    varchar(255)   not null
       , CO_date     date           not null
       , CO_dest     char(10)       not null
      )
;
-- création d'un jeu d'essais plus riche :
--    cas de dossiers avec pluieurs commentaires admin
--                    avec un seul commentaire admin
--                    sans commentaire admin
insert into CO_comment 
      (CO_ident, DO_ident, CO_texte, CO_date, CO_dest)
values  
       (1, 1, 'comment 01', '2023-11-01', 'admin')
     , (2, 1, 'comment 02', '2023-11-02', 'client1')
     , (3, 1, 'comment 03', '2023-11-03', 'admin')  
     , (4, 2, 'comment 04', '2023-11-01', 'client1')
     , (5, 2, 'comment 05', '2023-11-02', 'admin')
     , (6, 2, 'comment 06', '2023-11-02', 'client2')
     , (7, 3, 'comment 07', '2023-11-01', 'client1')
     , (8, 3, 'comment 08', '2023-11-02', 'client2')
     , (9, 4, 'comment 09', '2023-11-02', 'admin')
;

La première requête, qui ne restitue que la ligne la plus récente de chaque dossier, celle de l'admin si présente, autre sinon :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- cas 1 : récupération du commentaire "admin" le plus récent par dossier
--         à défaut, récupération du commentaire le plus récent    
select DO.DO_nom
     , coalesce(admn.CO_texte, autr.CO_texte) as txt
     , coalesce(admn.CO_date, autr.CO_date)   as dte
from DO_dossier as DO
-- dossier admin le plus récent  
left join
     (select DO_ident 
           , CO_texte
           , CO_date
      from CO_comment S1
      where CO_dest='admin'
        and not exists
           (select 1
            from CO_comment S2
            where S2.DO_ident=S1.DO_ident 
              and S2.CO_date>S1.CO_date
              and S2.CO_dest='admin'
           )
     ) as admn
   on admn.DO_ident=DO.DO_ident
-- dossier autre le plus récent  
left join
     (select DO_ident 
           , CO_texte
           , CO_date
      from CO_comment S1
      where CO_dest<>'admin'
        and not exists
           (select 1
            from CO_comment S2
            where S2.DO_ident=S1.DO_ident 
              and S2.CO_date>S1.CO_date
           )  
     ) as autr
   on autr.DO_ident=DO.DO_ident
;

La deuxième qui restitue toutes les lignes des dossiers, mais celle la plus récente de l'admin en premier, à défaut la plus récente autre, puis les restantes triées par dates :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- cas 2 : récupération de tous les commentaires, avec le commentaire "admin" le 
--         plus récent en premier
select distinct nom, txt, dte 
from (
select DO.DO_nom as nom
     , CO_texte  as txt
     , CO_date   as dte
     , 0         as seq
from DO_dossier as DO
inner join
     CO_comment as admn
   on admn.DO_ident=DO.DO_ident
  and admn.CO_dest='admin'
  and not exists
         (select 1
          from CO_comment S1
          where S1.DO_ident=admn.DO_ident 
            and S1.CO_dest='admin'
            and S1.CO_date>admn.CO_date
         ) 
union all
select DO.DO_nom as nom
     , CO_texte  as txt
     , CO_date   as dte
     , 1         as seq
from DO_dossier as DO
inner join
     CO_comment as admn
   on admn.DO_ident=DO.DO_ident
) subq
  order by nom, seq, dte desc
;

Résultat 1 :

Pièce jointe 647014

Résultat 2 :

Pièce jointe 647015

À adapter avec vos noms de tables et de colonnes

Impressionnant...
Un grand merci de prendre du temps pour m'aider, c'est vraiment très sympa :D

J'ai (tenté) d'adapter la 2ème requête - à laquelle j'avoue ne pas comprendre grand chose - à mes tables et noms de colonne :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
select distinct nom, txt, dte 
from (
select DO.nom as nom
     , message  as txt
     , date   as dte
     , 0      as seq
from suivi_sav as DO
inner join
     suivi_sav_fil as admn
   on admn.id_sav=DO.id
  and admn.dest='admin'
  and not exists
         (select 1
          from suivi_sav_fil S1
          where S1.id_sav=admn.id_sav 
            and S1.dest='admin'
            and S1.date>admn.date
         ) 
union all
select DO.nom as nom
     , message  as txt
     , date   as dte
     , 1      as seq
from suivi_sav as DO
inner join
     suivi_sav_fil as admn
   on admn.id_sav=DO.id
) subq
  order by nom, seq, dte desc
;

Et phpMyAdmin me retourne " Champ 'date' est ambigu dans field list".

La 1ère requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
select DO.nom
     , coalesce(admn.message, autr.message) as txt
     , coalesce(admn.date, autr.date)   as dte
from suivi_sav as DO
 
left join
     (select id 
           , message
           , date
      from suivi_sav_fil S1
      where dest='admin'
        and not exists
           (select 1
            from suivi_sav_fil S2
            where S2.id_sav=S1.id_sav 
              and S2.date>S1.date
              and S2.dest='admin'
           )
     ) as admn
   on admn.id_sav=DO.id
 
left join
     (select id 
           , message
           , date
      from suivi_sav_fil S1
      where dest<>'admin'
        and not exists
           (select 1
            from suivi_sav_fil S2
            where S2.id_sav=S1.id_sav 
              and S2.date>S1.date
           )  
     ) as autr
   on autr.id_sav=DO.id
;

Le msg d'erreur est "admn.id_sav inconnu dans ON clause" pourtant, si admn est l'alias de suivi_sav_fil, le champ id_sav existe bien.

24/11/2023, 23h45
renaud26

Bon, finalement, j'ai résolu mon problème via php. Je ne me sentais pas dans le rôle d'explorateur d'usines à gaz. Merci à tous.

Ce n'est pourtant pas insurmontable :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 -- cas 2 : récupération de tous les commentaires, avec le commentaire "admin" le -- plus récent en premier select distinct nom, txt, dte from ( select DO.nom as nom , admn.message as txt , admn.date as dte , 0 as seq from suivi_sav as DO inner join suivi_sav_fil as admn on admn.id_sav=DO.id and admn.dest='admin' and not exists (select 1 from suivi_sav_fil S1 where S1.id_sav=admn.id_sav and S1.dest='admin' and S1.date>admn.date ) union all select DO.nom as nom , admn.message as txt , admn.date as dte , 1 as seq from suivi_sav as DO inner join suivi_sav_fil as admn on admn.id_sav=DO.id ) subq order by nom, seq, dte desc ;
Quelques remarques :

déporter le code coté PHP est une erreur : ce sera moins performant que coté SGBD et moins portable aussi.
un même attribut devrait toujours porter le même nom quelle que soit la table dans laquelle il se trouve.
Par exemple, il n'y a aucune raison que l'identifiant du suivi s'appelle "id" dans la table "suivi_sav", mais "id_save" dans la table "suivi_sav_fil"
Ce genre d'aberration ne facilite pas le code SQL puisqu'on se demande toujours quelle colonne correspond à quelle autre.
Les logiciels de modélisation tels que Power AMC ou Looping proposent systématiquement de propager le nom de la clef primaire comme nom de clef étrangère, ce n'est pas pour rien.
Pour y parvenir, ne nommez jamais un idenfiant "id", précisez de quel "id" il s'agit, par exemple "id_dossier", "id_suivi", "id_user"...
une colonne ne devrait jamais avoir pour nom un mot réservé SQL comme date". À remplacer par exemple par "date_suivi"

id	id_dossier	message	date	dest
1	1	bla bla	2023-11-01	admin
2	1	blo blo	2023-11-02	client
3	2	bla bla	2023-11-01	admin
4	2	blo blo	2023-11-02	client
5	2	bla bla	2023-11-02	Admin