select MIN CASE

**Sylvercis** · 28/04/2019, 16h11

Bonjour,

Dans une base contenant une liste de film, je cherche a sortir le titre de chaque film dans une langue en particulier.
Jusque là c'est simple, le problème c'est que pour certains films il se peut qu'il n'y ai pas de titre en français donc si je demande a récuperer tous les titres en français il va y avoir des trous.

Il faut donc que lorsque le titre français n'est pas disponible, je récupère à la place le titre en anglais.

voici un exemple simplifié de la base, on s'encombre pas des jointures pour une meilleure compréhension:

film_id	titre	language	status
1	les veuves	fr	0
1	widows	en	0
2	rambo	fr	0
3	spiderman	en	0
3	spiderman	fr	0
3	spiderman	cn	0
4	Hobbit	fr	1
4	Hobbit	cn	0
4	Hobbit	en	0

En sortie il me faut ceci dans le cas ou je demande les titres en français

film_id	titre	language
1	les veuves	fr
2	rambo	fr
3	spiderman	fr
4	Hobbit	en

j'ai fait ça et cela a l'air de marcher si on veut sortir le 'fr' avec 'en" par defaut voir n'importe quel autre language ensuite:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT f.film_id film_id, f.titre titre, f.language lang, f.status stat,
   MIN(CASE 
	   WHEN f.language = 'fr' THEN 1
       WHEN f.language = 'en' THEN 2
       ELSE 3 END
   ) AS preference
FROM films f
 
WHERE f.status = 0
 
GROUP BY f.film_id

mais si j'essaie de sortir en 1er 'en' puis 'fr' puis n'importe quel language:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT f.film_id film_id, f.titre titre, f.language lang, f.status stat,
   MIN(CASE 
       WHEN f.language = 'en' THEN 1
       WHEN f.language = 'fr' THEN 2
       ELSE 3 END
   ) AS preference
FROM films f
 
WHERE f.status = 0
 
GROUP BY f.film_id

ça ne va plus.

Est ce que quelqu'un saurait comment tourner cette requête ?

Pour ceux qui veulent faire des essai, voici de quoi recreer la table:

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
CREATE TABLE IF NOT EXISTS `films` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `film_id` int(11) NOT NULL,
  `titre` varchar(255) NOT NULL,
  `language` varchar(2) NOT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;
 
--
-- Déchargement des données de la table `films`
--
 
INSERT INTO `films` (`id`, `film_id`, `titre`, `language`, `status`) VALUES
(1, 1, 'les veuves', 'fr', 0),
(2, 1, 'widows', 'en', 0),
(3, 2, 'rambo', 'fr', 0),
(4, 3, 'spiderman', 'en', 0),
(5, 3, 'spiderman', 'fr', 0),
(6, 3, 'spiderman', 'cn', 0),
(7, 4, 'hobbit', 'fr', 1),
(8, 4, 'hobbit', 'cn', 0),
(9, 4, 'hobbit', 'en', 0);

**al1_24** · 28/04/2019, 17h55

Une manière de faire :

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
SELECT f.film_id film_id, f.titre titre, f.language lang, f.status stat
FROM films f
WHERE f.status = 0
    and exists
        (   select  null
            from    films sel
            WHERE   sel.status = 0
            having  MIN(CASE 
                         WHEN sel.language = 'en' THEN 1
                         WHEN sel.language = 'fr' THEN 2
                         ELSE 3 END
                     )   = CASE 
                             WHEN f.language = 'en' THEN 1
                             WHEN f.language = 'fr' THEN 2
                             ELSE 3
                            END
        )
 
GROUP BY f.film_id

**Sylvercis** · 28/04/2019, 18h29

Je ne comprends pas trop la requête pour le moment mais après l'avoir essayé je peux te dire qu'elle ne fonctionne pas correctement.

J'obtiens 3 titres de films en anglais et il me manque "rambo" (dont le titre est seulement en français).

Si le titre anglais n'existe pas, il faut qu'un titre sorte par defaut dans un autre language.
En fait si on demande les titres dans un language quelconque, le 1er language par defaut est l'anglais, ensuite n'importe quel autre language si l'anglais n'existe pas non plus.
Si par contre on demande les titres en anglais, le 1er language par defaut reste l'anglais et donc on devrait obtenir le titre dans n'importe quel autre language.

Le tout est que l'on doit impérativement obtenir la liste complete des titres de films quelquesoit le language.

**al1_24** · 28/04/2019, 18h38

En effet, la requête que j'ai postée ne fonctionne pas correctement

Il manque une ligne entre les lignes 7 et 8.

Code :

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

1
2
3
            WHERE   sel.status = 0
               and sel.film_id = f.film_id
            having  MIN(CASE

Là, ça devrait être plus proche du résultat attendu.

**Sylvercis** · 28/04/2019, 20h41

En effet, là ça a l'air de fonctionner nickel.
J'ai essayé les 3 languages dans la table et j'ai bien les résultats attendu.

Il faut maintenant que j’étudie et comprenne le fonctionnement de cet requête pour l'adapter à ma véritable base de données et mes différents besoins.

Merci Beaucoup pour ton aide

**Sylvercis** · 30/04/2019, 18h14

Bon finalement cette requête ne fait pas l'affaire. Elle fonctionne correctement mais elle est beaucoup trop lourde.
Lors de mes essais sur la base de données réelle, la réponse mets 9s a arriver sur mon pc en local.

Il faudrai redescendre a 2 ou 3s maxi. (c'est pour l'admin du site donc je peux me permettre ce délai mais 9s c'est beaucoup trop).

Si j'ai bien compris cette requête, le problème c'est que pour chaque ligne de ma table, une sous requête est executée pour déterminer si le language corresponds aux critères de recherche.

Je pense qu'il faudrai plutôt si c'est possible faire une sous requete qui sort toutes les lignes une fois pour toute après avoir corrigé le language souhaité de façon à ce qu'ensuite avec la requete principal, avec l'aide de GROUP BY et MIN() je puis récupérér les lignes souhaitées.

Pour ce faire il y a un changement important dans les données a traiter qui rentre en compte.
Dans la table de mon exemple j'utilisais des suffixes: 'fr", 'en', 'cn', etc...

En réalité j'ai une table séparée pour les langues et elles sont numérotées.
Autant utiliser les numéros pour accelerer le traitement.

1 = en
2 = fr
3 = sp
etc...

L'idée serai de remplacer à la volée le numéro de la langue que je souhaite sortir en priorité par le numéro zero dans la sous requête. (le zero n'est pas utilisé dans ma table).
Ensuite dans la requête principal je n'aurai qu'a faire SELECT min(language_id) pour récupérer les titres dans le language souhaité.
Avec ce tri j'aurai par défaut, l'anglais puis espagnol puis etc...

Mon problème c'est que je ne vois pas comment écrire la sous requete pour effectuer cette modification à la volée. (Est ce seulement possible ?)

Merci d'avance

**Artemus24** · 04/05/2019, 09h23

Salut à tous.

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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `langage`
--------------
 
--------------
create table `langage`
(  `id`       tinyint unsigned not null primary key,
   `code`        char(002)     not null,
   `libelle`  varchar(255)     not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `langage` (`id`,`code`,`libelle`) values
  ( 1, 'en', 'english'),
  ( 2, 'fr', 'french'),
  ( 3, 'cn', 'chinese')
--------------
 
--------------
select * from `langage`
--------------
 
+----+------+---------+
| id | code | libelle |
+----+------+---------+
|  1 | en   | english |
|  2 | fr   | french  |
|  3 | cn   | chinese |
+----+------+---------+
--------------
DROP TABLE IF EXISTS `film`
--------------
 
--------------
create table `film`
(  `id`       integer unsigned not null auto_increment primary key,
   `film_id`  integer unsigned not null,
   `titre`    varchar(255)     not null,
   `langue`   tinyint unsigned not null,
   `status`   tinyint unsigned not null,
  CONSTRAINT `FK_LANGAGE` FOREIGN KEY (`langue`) REFERENCES `langage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  index `idx_01` (`langue`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `film` (`film_id`,`titre`,`langue`,`status`) values
  (1, 'Les veuves', 2, 0),
  (1, 'Widows',     1, 0),
  (2, 'Rambo',      2, 0),
  (3, 'Spiderman',  1, 0),
  (3, 'Spiderman',  2, 0),
  (3, 'Spiderman',  3, 0),
  (4, 'Hobbit',     2, 1),
  (4, 'Hobbit',     3, 0),
  (4, 'Hobbit',     1, 0)
--------------
 
--------------
select    *
    from  `film`
order by  film_id, langue
--------------
 
+----+---------+------------+--------+--------+
| id | film_id | titre      | langue | status |
+----+---------+------------+--------+--------+
|  2 |       1 | Widows     |      1 |      0 |
|  1 |       1 | Les veuves |      2 |      0 |
|  3 |       2 | Rambo      |      2 |      0 |
|  4 |       3 | Spiderman  |      1 |      0 |
|  5 |       3 | Spiderman  |      2 |      0 |
|  6 |       3 | Spiderman  |      3 |      0 |
|  9 |       4 | Hobbit     |      1 |      0 |
|  7 |       4 | Hobbit     |      2 |      1 |
|  8 |       4 | Hobbit     |      3 |      0 |
+----+---------+------------+--------+--------+
--------------
select      t1.*,
            t2.libelle
 
      from  `film` as t1
 
inner join  `langage` as t2
        on  t2.id = t1.langue
 
     where  t1.id = (  select id
                         from  (  select  film_id,
                                          any_value(id) as id
                                    from  `film`
                                   where  status=0
                                group by  film_id, langue
                                order by  film_id, field(langue,2,1,3)
                               ) as x
 
                        where  film_id = t1.film_id
                     group by  film_id
                    )
 
order by  t1.film_id
--------------
 
+----+---------+------------+--------+--------+---------+
| id | film_id | titre      | langue | status | libelle |
+----+---------+------------+--------+--------+---------+
|  1 |       1 | Les veuves |      2 |      0 | french  |
|  3 |       2 | Rambo      |      2 |      0 | french  |
|  5 |       3 | Spiderman  |      2 |      0 | french  |
|  9 |       4 | Hobbit     |      1 |      0 | english |
+----+---------+------------+--------+--------+---------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

@+

**Sylvercis** · 04/05/2019, 12h19

Je l'ai testé tel quel et ça marche nickel.
le "field(langue,2,1,3)" me convient assez bien, ça me permettra de faire simple au depart puis de laisser ensuite l'utilisateur paramétrer l'ordre de sortie selon ses gouts.

Il faut maintenant que je l'essaie avec mes vrais tables pour voir ce que ça donne au niveau vitesse.

**Sylvercis** · 04/05/2019, 14h50

Je viens de tester la requête adapté a mes tables pour lister les evenements et sous phpmyadmin j'ai une réponse en 0.0000s.

Testé ensuite dans ma console admin et pareil, affichage de la page plus ou moins instantanée.

Étant donné qu'actuellement tous les articles sont référencé dans ma base comme étant français et que la colonne status ne contient également que des 0, je n'ai pas pu vérifier le bon fonctionnement de cette requête avec ma base de données mais je suis confiant.

Niveau rapidité en tout cas je valide, le fait fait d'être passé sous innoDB doit fortement y contribuer je pense.

Je vais créer quelques enregistrement avec des langues et status différents pour vérifier tout cela.

ps: j'ai pas bien compris l'utilité de "any_value", j'ai l'impression que c'est un cache misère qu'il vaudrai mieux éviter ?

**Artemus24** · 07/05/2019, 12h30

Salut à tous.

J'ai créé une table utilisateur contenant le critère de tri en fonction des langues préférées de celui-ci.
J'ai créé une fonction de nom "tri" qui va vous retourner l'identifiant de la table "film" en fonction de l'utilisateur.
Et trois exemples, puisque j'ai trois utilisateurs dans mon exemple, avec le tri associé.

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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `langage`
--------------
 
--------------
create table `langage`
(  `id`       tinyint unsigned not null auto_increment primary key,
   `code`        char(002)     not null,
   `libelle`  varchar(255)     not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `langage` (`code`,`libelle`) values
  ('en', 'english'),
  ('fr', 'french'),
  ('cn', 'chinese')
--------------
 
--------------
select * from `langage`
--------------
 
+----+------+---------+
| id | code | libelle |
+----+------+---------+
|  1 | en   | english |
|  2 | fr   | french  |
|  3 | cn   | chinese |
+----+------+---------+
--------------
DROP TABLE IF EXISTS `utilisateur`
--------------
 
--------------
create table `utilisateur`
(  `id`      integer unsigned not null auto_increment primary key,
   `nom`     varchar(255)     not null,
   `langue`  tinyint unsigned not null,
   `tri`     varchar(255)     not null,
  CONSTRAINT `FK_UTILISATEUR_LANGAGE` FOREIGN KEY (`langue`) REFERENCES `langage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `utilisateur` (`nom`,`langue`,`tri`) values
  ('jean', 2, '2,1,3'),
  ('john', 1, '1,2,3'),
  ('wei',  3, '3,1,2')
--------------
 
--------------
select * from `utilisateur`
--------------
 
+----+------+--------+-------+
| id | nom  | langue | tri   |
+----+------+--------+-------+
|  1 | jean |      2 | 2,1,3 |
|  2 | john |      1 | 1,2,3 |
|  3 | wei  |      3 | 3,1,2 |
+----+------+--------+-------+
--------------
DROP TABLE IF EXISTS `film`
--------------
 
--------------
create table `film`
(  `id`       integer unsigned not null auto_increment primary key,
   `film_id`  integer unsigned not null,
   `titre`    varchar(255)     not null,
   `langue`   tinyint unsigned not null,
   `status`   tinyint unsigned not null,
  CONSTRAINT `FK_FILM_LANGAGE` FOREIGN KEY (`langue`) REFERENCES `langage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  index `idx_01` (`langue`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `film` (`film_id`,`titre`,`langue`,`status`) values
  (1, 'Les veuves', 2, 0),
  (1, 'Widows',     1, 0),
  (2, 'Rambo',      2, 0),
  (3, 'Spiderman',  1, 0),
  (3, 'Spiderman',  2, 0),
  (3, 'Spiderman',  3, 0),
  (4, 'Hobbit',     2, 1),
  (4, 'Hobbit',     3, 0),
  (4, 'Hobbit',     1, 0)
--------------
 
--------------
select    *
    from  `film`
order by  film_id, langue
--------------
 
+----+---------+------------+--------+--------+
| id | film_id | titre      | langue | status |
+----+---------+------------+--------+--------+
|  2 |       1 | Widows     |      1 |      0 |
|  1 |       1 | Les veuves |      2 |      0 |
|  3 |       2 | Rambo      |      2 |      0 |
|  4 |       3 | Spiderman  |      1 |      0 |
|  5 |       3 | Spiderman  |      2 |      0 |
|  6 |       3 | Spiderman  |      3 |      0 |
|  9 |       4 | Hobbit     |      1 |      0 |
|  7 |       4 | Hobbit     |      2 |      1 |
|  8 |       4 | Hobbit     |      3 |      0 |
+----+---------+------------+--------+--------+
--------------
CREATE FUNCTION `tri`(_id_user  integer unsigned,
                      _id_film  integer unsigned)
  RETURNS integer unsigned
  DETERMINISTIC
  LANGUAGE SQL
BEGIN
  return (    select  id
                from  (    select  t1.film_id,
                                   any_value(t1.id) as id
 
                             from  `film`        as t1
                       inner join  `utilisateur` as t2
                               on  t2.id = _id_user
 
                            where  t1.status=0
                         group by  t1.film_id, t1.langue
                         order by  t1.film_id, find_in_set(t1.langue,t2.tri)
                      ) as x
 
               where  film_id = _id_film
            group by  film_id
         );
END
--------------
 
--------------
select      t1.*,
            t2.libelle
      from  `film` as t1
inner join  `langage` as t2
        on  t2.id = t1.langue
     where  t1.id = tri(1, t1.film_id)
  order by  t1.film_id
--------------
 
+----+---------+------------+--------+--------+---------+
| id | film_id | titre      | langue | status | libelle |
+----+---------+------------+--------+--------+---------+
|  1 |       1 | Les veuves |      2 |      0 | french  |
|  3 |       2 | Rambo      |      2 |      0 | french  |
|  5 |       3 | Spiderman  |      2 |      0 | french  |
|  9 |       4 | Hobbit     |      1 |      0 | english |
+----+---------+------------+--------+--------+---------+
--------------
select      t1.*,
            t2.libelle
      from  `film` as t1
inner join  `langage` as t2
        on  t2.id = t1.langue
     where  t1.id = tri(2, t1.film_id)
  order by  t1.film_id
--------------
 
+----+---------+-----------+--------+--------+---------+
| id | film_id | titre     | langue | status | libelle |
+----+---------+-----------+--------+--------+---------+
|  2 |       1 | Widows    |      1 |      0 | english |
|  3 |       2 | Rambo     |      2 |      0 | french  |
|  4 |       3 | Spiderman |      1 |      0 | english |
|  9 |       4 | Hobbit    |      1 |      0 | english |
+----+---------+-----------+--------+--------+---------+
--------------
select      t1.*,
            t2.libelle
      from  `film` as t1
inner join  `langage` as t2
        on  t2.id = t1.langue
     where  t1.id = tri(3, t1.film_id)
  order by  t1.film_id
--------------
 
+----+---------+-----------+--------+--------+---------+
| id | film_id | titre     | langue | status | libelle |
+----+---------+-----------+--------+--------+---------+
|  2 |       1 | Widows    |      1 |      0 | english |
|  3 |       2 | Rambo     |      2 |      0 | french  |
|  6 |       3 | Spiderman |      3 |      0 | chinese |
|  8 |       4 | Hobbit    |      3 |      0 | chinese |
+----+---------+-----------+--------+--------+---------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Comme on peut le voir, le tri est bien respecté selon les choix de l'utilisateur.

@+

select MIN CASE [MySQL-5.7]

Requêtes MySQL

Vue hybride

Discussions similaires

Partager

Partager