Un NOT IN dans le WHERE d'un SELECT, avec CONCAT : résultat inattendu

Version imprimable

Hello :D

Je planche sur un outil de gestion de playlist et dans cet outil, je peux effectuer une recherche sur ce qui a été déjà joué précédemment (d'autres playlists d'avant donc).
Etant donné que cet outil sera disponible pour d'autres animateur de la radio, il m'a été soumis aussi l'idée de pouvoir chercher dans la base de données des titres de la radio.
Pour faire quelque chose d'ergonomique, je souhaite faire en sorte que pour les titres des playlists précédentes, ceux de la base de titres de la radio ne soient pas affichés (pour pas les avoir en double quoi.

Je fais donc une première requête (que je simplifie pour ici en retirant les options WHERE) pour alimenter un tableau à parti des titres playlists. La colonne 'titre" contient les informations sous la forme suivante :

Citation:

artiste - titre

Voici la requête :
Code:

1 2 3 SELECT LOWER(titre) AS titre FROM playlist_playlist GROUP BY titre
Je mets les résultats dans un tableau $titrespl_arr

Pour la base radio, je fais une deuxième requête, sachant que les informations d'artiste et de titres sont sur deux colonnes distinctes. Il faut donc que j'utilise un CONCAT.
Voici la requête :
Code:

1 2 3 4 SELECT CONCAT(TRIM(LOWER(artist)),' - ',TRIM(LOWER(title))) AS titre FROM songs WHERE CONCAT(TRIM(LOWER(artist)),' - ',TRIM(LOWER(title))) NOT IN ('" . addslashes(implode(',',$titrespl_arr)) . "') GROUP BY titre
La condition NOT IN n'a pas l'air d'être pris en compte.

Dans une playlist précédente, j'ai par exemple le titre (une fois "lowerisé") :

Citation:

2 brothers on the 4th floor - heaven is here

Dans la base de la radio ,j'ai le même titre, qui est écrit pareil une fois "lowerisé", "trimé", et concaténé :

Citation:

2 brothers on the 4th floor - heaven is here

Et pourtant les deux apparaissent alors que celui de la playlist seulement devrait apparaitre.
(Et fait dans le while je rempli un autre tableau avec deux valeurs à savoir titre et source, dans laquelle j'indique si le tableau est rempli par la requête sur les playlist ou sur la base radio).

Un extrait de du résultat sous format json montre bien que les deux sont affichés :

Citation:

16:
titre: "2 brothers on the 4th floor - heaven is here"
source: "playlist"
17:
titre: "2 brothers on the 4th floor - heaven is here"
source: "eurodance90"

Seul le 16 devrait apparaitre.

Edit :
Si je fais une requète sur l'exemple avec ce titre :
Code:

1 2 3 4 SELECT CONCAT(TRIM(LOWER(artist)),' - ',TRIM(LOWER(title))) AS titre FROM songs WHERE CONCAT(TRIM(LOWER(artist)),' - ',TRIM(LOWER(title))) != '2 brothers on the 4th floor - heaven is here' GROUP BY titre
Le titre sort bien qu'une fois (playlist) et celui de la base radio n'est pas affiché.

Si quelqu'un a une idée de l'erreur que je fais :oops:

Merkouin ! :lol:

Salut Couin Couin.

Admettons que tu possèdes deux tables.
L'une contient tous les titres de ta playlist.
Et l'autre, les morceaux de ta playlist que tu as déjà diffusés.

Pour faire la différence entre ces deux tables, à savoir "tabone - tabtwo", utilise plutôt le "not exists", comme ci-après :

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
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
--------------
START TRANSACTION
--------------
 
--------------
set session collation_connection = "latin1_general_ci"
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_cs`
--------------
 
--------------
DROP TABLE IF EXISTS `tabone`
--------------
 
--------------
CREATE TABLE `tabone`
(  `id`   integer unsigned NOT NULL auto_increment primary key,
   `lib`  varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_cs`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `tabone` (`lib`) values
  ('un'),('deux'),('trois'),('quatre'),('cinq'),('six')
--------------
 
--------------
select * from `tabone`
--------------
 
+----+--------+
| id | lib    |
+----+--------+
|  1 | un     |
|  2 | deux   |
|  3 | trois  |
|  4 | quatre |
|  5 | cinq   |
|  6 | six    |
+----+--------+
--------------
DROP TABLE IF EXISTS `tabtwo`
--------------
 
--------------
CREATE TABLE `tabtwo`
(  `id`    integer unsigned NOT NULL auto_increment primary key,
   `mess`  varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_cs`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `tabtwo` (`mess`) values
  ('deux'),('cinq')
--------------
 
--------------
select * from `tabtwo`
--------------
 
+----+------+
| id | mess |
+----+------+
|  1 | deux |
|  2 | cinq |
+----+------+
--------------
select lib
from `tabone` as t1
where not exists (select 1 from `tabtwo` as t2 where t2.mess = t1.lib)
--------------
 
+--------+
| lib    |
+--------+
| un     |
| trois  |
| quatre |
| six    |
+--------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Ainsi tu ne diffuseras pas deux fois le même morceau de musique.

Tu peux remettre ta table "tabtwo" à zéro quand tu as diffusé tous les titres de ta playlist.

Cordialement.
Artemus24.
@+

14/12/2021, 08h23
escartefigue
Bonjour,

Comme l'indique Artemus24, un test d'existence avec (NOT) EXISTS sera souvent plus performant qu'avec (NOT) IN.
Mais, le plus important, c'est de favoriser les recherches qui peuvent utiliser des index pour éviter de balayer systématiquement toutes les lignes des tables consultées.

Or une restriction du type WHERE CONCAT(TRIM(LOWER(artist)),' - ',TRIM(LOWER(title))) != '2 brothers on the 4th floor - heaven is here'.
est ce qu'on peut faire de pire !
- la présence de fonctions (ici à la fois CONCAT, TRIM et LOWER) sur la colonne ARTIST compromet tout usage d'index pour cette colonne ;
- la présence de fonctions (TRIM et LOWER) sur la colonne TITLE compromet l'usage d'index pour cette colonne ;
- le prédicat "différent de" compromet également l'usage d'index.
3 bonnes raisons de faire un table scan !
La même stratégie sur une jointure entre les tables ARTISTE et TITRE aurait les conséquences suivantes :
si la table ARTISTE contient 1500 lignes et la table TITLE 6000 lignes, la requête consultera donc 1500*6000 soit 9 millions de lignes à chaque fois que vous voudrez vérifier si un titre a déjà été joué... 10 vérifications ==> 90 millions de lignes lues :aie:
Ici on est sur une seule table (ouf !) mais on fera tout de même un table scan à chaque vérification. Ce qui peut faire beaucoup

La bonne méthode est d'avoir une table playlist, enrichie à chaque fois qu'un titre d'un artiste est joué. Afin d'éviter de rejouer un même titre, vous vérifierez dans cette table que le titre que vous envisagez de jouer n'est pas déjà présent.
Et surtout cette recherche sera faite sur l'identifiant du titre et l'identifiant de l'artiste et non pas sur le libellé du titre ou le nom de l'artiste.
Une recherche sur identifiant (sous réserve que les identifiants soient bien construits, c'est à dire de type asémantique, concis et stable, idéalement un "auto_increment") est ce qu'il y a de plus performant.

Salut à tous.

Il y a plus simple que la solution que je propose.
Ajoutez une colonne dans la table playlist afin de savoir si ce morceau a déjà été joué ou pas.
Dans mon exemple, j'ai ajouté la colonne "joué".
J'y ai mis une date comme astuce de fonctionnement.

Si le morceau a été diffusée hier, donc le 13 décembre 2021, il suffit alors de tester la colonne avec la date du jour.
Si la date du jour est le 14 décembre 2021 alors le morceau peut être diffusée à nouveau.
Après la diffusion, vous le marquez à la date du jour, donc au 14 décembre 2014.

Si inversement, il est marqué au 14 décembre 2014, et que nous sommes ce jour, ce morceau de musique ne sera pas diffusée à nouveau.

Cette astuce de la date permet de marquer le morceau de musique quand il a été diffusée.
Il n'est pas nécessaire de remettre cette colonne à zéro car ce qui est important est la comparaison avec la date du jour.

Voici une simplification de ce que vous cherchez à faire :
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 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 -------------- START TRANSACTION -------------- -------------- set session collation_connection = "latin1_general_ci" -------------- -------------- DROP DATABASE IF EXISTS `base` -------------- -------------- CREATE DATABASE IF NOT EXISTS `base` DEFAULT CHARACTER SET `latin1` DEFAULT COLLATE `latin1_general_cs` -------------- -------------- DROP TABLE IF EXISTS `playlist` -------------- -------------- CREATE TABLE `playlist` ( `id` integer unsigned NOT NULL auto_increment primary key, `lib` varchar(255) NOT NULL, `joue` date NULL ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_cs` ROW_FORMAT=COMPRESSED -------------- -------------- insert into `playlist` (`lib`,`joue`) values ('un',NULL),('deux','2021-12-14'),('trois','2021-12-13'),('quatre','2021-12-12'),('cinq','2021-12-14'),('six','2021-12-13') -------------- -------------- select * from `playlist` -------------- +----+--------+------------+ | id | lib | joue | +----+--------+------------+ | 1 | un | NULL | | 2 | deux | 2021-12-14 | | 3 | trois | 2021-12-13 | | 4 | quatre | 2021-12-12 | | 5 | cinq | 2021-12-14 | | 6 | six | 2021-12-13 | +----+--------+------------+ -------------- select lib from `playlist` as t1 where joue <> CURRENT_DATE() or joue is NULL -------------- +--------+ | lib | +--------+ | un | | trois | | quatre | | six | +--------+ -------------- COMMIT -------------- Appuyez sur une touche pour continuer...
Cela va vous simplifier la vie, mais surtout vous aurez une meilleure performance dans la recherche du prochain morceau de musique à diffiuser.

Cordialement.
Artemus24.
@+