Discussion :

[davis40]

Bonjour,

J'aurais besoin de votre aide concernant une requête sur une base MySQL.

J'ai 4 tables (groups, jobs, users, comments) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
40
41
42
43
44
45
46
-- Table des groupes d'utilisateurs.
CREATE TABLE IF NOT EXISTS groups (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	added_date DATETIME DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (id)
)
ENGINE=INNODB DEFAULT CHARSET=utf8;
 
-- Table des métiers des utilisateurs.
CREATE TABLE IF NOT EXISTS jobs (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	added_date DATETIME DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (id)
)
ENGINE=INNODB DEFAULT CHARSET=utf8;
 
-- Tables des utilisateurs.
CREATE TABLE IF NOT EXISTS users (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	job_id INT UNSIGNED DEFAULT NULL,
	group_id INT UNSIGNED NOT NULL,
	last_name VARCHAR(255) NOT NULL,
	first_name VARCHAR(255) NOT NULL,
	added_date DATETIME DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (id),
	FOREIGN KEY (job_id) REFERENCES jobs(id),
	FOREIGN KEY (group_id) REFERENCES groups(id)
)
ENGINE=INNODB DEFAULT CHARSET=utf8;
 
-- Tables des commentaires des utilisateurs.
CREATE TABLE IF NOT EXISTS comments (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	user_id INT UNSIGNED NOT NULL,
	job_id INT UNSIGNED NOT NULL,
	group_id INT UNSIGNED NOT NULL,
	comment VARCHAR(5000),
	added_date DATETIME DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (id),
	FOREIGN KEY (user_id) REFERENCES users(id),
	FOREIGN KEY (job_id) REFERENCES jobs(id),
	FOREIGN KEY (group_id) REFERENCES groups(id)
)
ENGINE=INNODB DEFAULT CHARSET=utf8;

Mon but est d'avoir un tableau de la forme ci-dessous :

Nom

Prénom

Groupe

Métier

Commentaire le plus récent de l'année en cours

ID du commentaire le plus récent de l'année en cours

Commentaire le plus récent de l'année n-1

ID du commentaire le plus récent de l'année n-1

Ce tableau doit lister tous les utilisateurs (l'utilisateur doit n’apparaître qu'une fois) qu'ils aient un commentaire ou non. J'ai réussi avec la requête suivante à récupérer une liste mais il affiche tous les commentaires. J'ai essayé de faire mieux avec des sous-requêtes mais rien de mieux.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT u.last_name AS u_last_name,
	   u.first_name AS u_first_name,
	   g.name AS g_name,
	   j.name AS j_name,
	   c.added_date AS c_added_date
FROM users AS u
INNER JOIN groups AS g ON u.group_id = g.id
INNER JOIN jobs AS j ON u.job_id = j.id
LEFT OUTER JOIN comments AS c ON a.id = c.user_id

Est-ce qu'il faut que je requête pour chaque utilisateur ? ou puis-je le faire en une fois ? et comment ?

Merci par avance,

Davis

[escartefigue]

Bonjour,

Comme vous voulez afficher 2 fois des colonnes issues de la table commentaires, vous devez faire 2 fois la jointure outer avec cette table

• une fois pour le commentaire le plus récent de l'année
• une autre fois pour celui de l'année précédente

Dans chacune de ces jointures, vous vérifierez que c'est le commentaire le plus récent (avec where not exists, ou max())

[escartefigue]

Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 us.last_name  as US_nom
     , us.first_name as US_prn
     , gr.name       as GR_nom
     , jb.name       as JB_nom
     , c1.comment    as C1_cmt
     , c1.id         as C1_id
     , c2.comment    as C2_cmt
     , c2.id         as C2_id   
FROM users AS US
INNER JOIN groups AS GR
   ON GR.id = US.group_id
INNER JOIN jobs AS JB 
   ON JB.ID = US.job_id
LEFT OUTER JOIN comments AS C1
   ON C1.user_id = US.id
  AND C1.added_date between '2017-01-01' and '2017-12-31'
  AND not exists
     (select 1
      from comments as SUBQ
      where SUBQ.user_id    = C1.user_id
        and SUBQ.added_date > C1.added_date)
LEFT OUTER JOIN comments AS C2
   ON C2.user_id = US.id
  AND C2.added_date between '2016-01-01' and '2016-12-31'
  AND not exists
     (select 1
      from comments as SUBQ
      where SUBQ.user_id    = C2.user_id
        and SUBQ.added_date between '2016-01-01' and '2016-12-31'
        and SUBQ.added_date > C2.added_date)

Ici j'ai borné les dates "en dur" pour faire vite, vous pouvez bien sur utiliser des fonctions pour calculer les années dynamiquement

[davis40]

Bonjour,

Comme vous voulez afficher 2 fois des colonnes issues de la table commentaires, vous devez faire 2 fois la jointure outer avec cette table

• une fois pour le commentaire le plus récent de l'année
• une autre fois pour celui de l'année précédente

Dans chacune de ces jointures, vous vérifierez que c'est le commentaire le plus récent (avec where not exists, ou max())

Merci pour la réponse,

par contre si je fais je récupère pas tous les utilisateurs. Un exemple de la requête ?

Merci

[escartefigue]

S'il vous manque des utilisateurs c'est que certains d'entre eux n'ont soit pas de groupe, soit pas de job
En ce cas remplacez la/les jointure(s) inner concernée(s) par une/des jointures outer

[davis40]

Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 us.last_name  as US_nom
     , us.first_name as US_prn
     , gr.name       as GR_nom
     , jb.name       as JB_nom
     , c1.comment    as C1_cmt
     , c1.id         as C1_id
     , c2.comment    as C2_cmt
     , c2.id         as C2_id   
FROM users AS US
INNER JOIN groups AS GR
   ON GR.id = US.group_id
INNER JOIN jobs AS JB 
   ON JB.ID = US.job_id
LEFT OUTER JOIN comments AS C1
   ON C1.user_id = US.id
  AND C1.added_date between '2017-01-01' and '2017-12-31'
  AND not exists
     (select 1
      from comments as SUBQ
      where SUBQ.user_id    = C1.user_id
        and SUBQ.added_date > C1.added_date)
LEFT OUTER JOIN comments AS C2
   ON C2.user_id = US.id
  AND C2.added_date between '2016-01-01' and '2016-12-31'
  AND not exists
     (select 1
      from comments as SUBQ
      where SUBQ.user_id    = C2.user_id
        and SUBQ.added_date between '2016-01-01' and '2016-12-31'
        and SUBQ.added_date > C2.added_date)

Ici j'ai borné les dates "en dur" pour faire vite, vous pouvez bien sur utiliser des fonctions pour calculer les années dynamiquement

Exactement ce qu'il me fallait, ça fonctionne ! merci beaucoup !