Trouver les lignes qui vérifient des valeurs différentes pour une même colonne, sans OR

**avairet** · 04/02/2010, 15h49

Bonjour à tous,

C'est un truc qui me fait toujours taper la tête contre les murs et qui doit être simple une fois qu'on l'a fait une fois !

Mon souci est plus parlant par un exemple :

Soit une table "annonces_specific_fields" composée comme çà :

Code :

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

id | annonce_id | specific_field_id | real_value

Disons que j'aimerais faire la requête suivante sans OR, mais avec des AND :

Code :

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

1
2
3
4
SELECT DISTINCT(annonce_id) FROM annonces_specific_fields
WHERE specific_field_id = X AND real_value <= Y
OR (specific_field_id = Z AND real_value = "toto")
OR (specific_field_id = W AND (real_value BETWEEN t AND u))

En fait, trouver les lignes qui valident toutes ces conditions et non pas au moins une.
Bien entendu, si je remplace les OR par des AND, çà ne va pas puisque aucune ligne ne validera plusieurs valeurs pour le champ real_value.

J'avais quelque chose qui marchait presque avec une clause EXISTS.

Voici le code avec des valeurs réelles :

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
 
SELECT distinct(annonce_id) FROM annonces_specific_fields AS spec 
WHERE specific_field_id IN 
( 
SELECT id FROM specific_fields WHERE slug IN ('surface','pieces') 
) 
 
AND `spec`.`real_value` <= 50 
 
AND exists 
(
SELECT annonce_id FROM annonces_specific_fields 
WHERE (real_value = "2") 
AND `spec`.`annonce_id` = `annonces_specific_fields`.`annonce_id`
)

Mais le client a trouvé un cas où çà ne marche pas !

Suis-je sur la bonne voie avec cette clause EXISTS() et si oui où est mon erreur ?
Ou bien y a-t-il une autre façon de faire ?

Par avance merci pour vos éclairages !

**avairet** · 04/02/2010, 16h42

Bon, j'ai trouvé une solution qui semble fonctionner, mais je ne suis pas sûr qu'elle soit optimale, à cause du produit cartésien au début et de la requête imbriquée "dupliquée"...

Quand je fais un EXPLAIN, même si j'ai toujours du mal à en décrypter les infos, il me semble que c'est pas génial...

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
 
SELECT distinct(spec.annonce_id) from annonces_specific_fields AS spec,
annonces_specific_fields AS spec2
WHERE (spec.specific_field_id 
    IN (
        SELECT id FROM specific_fields
        WHERE slug = 'pieces'
    )
    AND spec.real_value = 2
)
AND (spec2.specific_field_id 
    IN (
        SELECT id FROM specific_fields
        WHERE slug = 'surface'
    )
    AND spec2.real_value <= 50
)
AND spec.annonce_id = spec2.annonce_id

Résultats du Explain

**CinePhil** · 05/02/2010, 12h23

trouver les lignes qui valident toutes ces conditions et non pas au moins une.

Code :

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

1
2
3
4
SELECT DISTINCT(annonce_id) FROM annonces_specific_fields
WHERE specific_field_id = X AND real_value <= Y
OR (specific_field_id = Z AND real_value = "toto")
OR (specific_field_id = W AND (real_value BETWEEN t AND u))

Tu as trois conditions, il faut donc compter si annonce_id répond aux trois conditions.
Supprime le distinct et fait un regroupement :

Code :

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

1
2
3
4
5
6
7
SELECT annonce_id 
FROM annonces_specific_fields
WHERE (specific_field_id = X AND real_value <= Y)
OR (specific_field_id = Z AND real_value = "toto")
OR (specific_field_id = W AND real_value BETWEEN t AND u)
GROUP BY annonce_id
HAVING COUNT(*) = 3

**avairet** · 05/02/2010, 13h49

Merci CinePhil pour ton idée, mais cela ne marche pas...

Voici ma requête actuelle :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT distinct(`spec`.`annonce_id`) from annonces_specific_fields AS spec, 
annonces_specific_fields AS spec2, 
annonces_specific_fields AS spec3 
WHERE (`spec`.`specific_field_id` IN ( SELECT id FROM specific_fields WHERE slug = "km" ) 
        AND (`spec`.`real_value` <= 100000) 
)
AND (`spec2`.`specific_field_id` IN ( SELECT id FROM specific_fields WHERE slug = "annee-mod" ) 
        AND (`spec2`.`real_value` between 2000 and 2005) 
)
AND (`spec3`.`specific_field_id` IN ( SELECT id FROM specific_fields WHERE slug = "energie" ) 
        AND (`spec3`.`real_value` = "Essence") 
)
AND `spec`.`annonce_id` = `spec3`.`annonce_id`

Résultat : annonce_id = 7

Et voici la tienne :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT `annonce_id` from annonces_specific_fields AS spec
WHERE (`spec`.`specific_field_id` IN ( SELECT id FROM specific_fields WHERE slug = "km" ) 
        AND (`spec`.`real_value` <= 100000) 
)
OR (`spec`.`specific_field_id` IN ( SELECT id FROM specific_fields WHERE slug = "annee-mod" ) 
        AND (`spec`.`real_value` between 2000 and 2005) 
)
OR (`spec`.`specific_field_id` IN ( SELECT id FROM specific_fields WHERE slug = "energie" ) 
        AND (`spec`.`real_value` = "Essence") 
)
GROUP BY annonce_id
HAVING COUNT(*) = 3

Résultat : vide

**CinePhil** · 05/02/2010, 14h20

Envoyé par avairet

Et voici la tienne :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT `annonce_id` from annonces_specific_fields AS spec
WHERE (`spec`.`specific_field_id` IN ( SELECT id FROM specific_fields WHERE slug = "km" ) 
        AND (`spec`.`real_value` <= 100000) 
)
OR (`spec`.`specific_field_id` IN ( SELECT id FROM specific_fields WHERE slug = "annee-mod" ) 
        AND (`spec`.`real_value` between 2000 and 2005) 
)
OR (`spec`.`specific_field_id` IN ( SELECT id FROM specific_fields WHERE slug = "energie" ) 
        AND (`spec`.`real_value` = "Essence") 
)
GROUP BY annonce_id
HAVING COUNT(*) = 3

Euh... non, ce n'est pas ma requête !
Jamais je n'écrirais une horreur pareille !

Exprime clairement ton besoin avec les vrais noms de tables et de colonnes et les vraies valeurs parce que là je n'arrive pas à comprendre ta requête !

J'ai quand même fait l'effort de réécrire ta requête telle que je la comprends.

Code :

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

1
2
3
4
5
6
7
SELECT annonce_id 
FROM annonces_specific_fields
WHERE (slug = 'km' AND real_value <= 100000) 
  OR (slug = 'annee-mod' AND real_value BETWEEN 2000 AND 2005
  OR (slug = 'energie' AND real_value = 'Essence') 
GROUP BY annonce_id
HAVING COUNT(*) = 3

Elle devrait donner les annonces_id pour lesquelles les kilomètres sont <= 100 000 ET l'année est comprise entre 2000 et 2005 inclus ET l'énergie est l'essence.

On fait un OU sur les conditions et on compte le nombre de lignes par annonce_id puis on ne retient que les annonce_id qui ont trois lignes, c'est à dire qui répondent aux 3 conditions. Ainsi, ça transforme la série de OU en ET.

Au passage, les valeurs textuelles s'écrivent entre apostrophes, pas entre guillemets.

**avairet** · 05/02/2010, 15h02

Euh... non, ce n'est pas ma requête !
Jamais je n'écrirais une horreur pareille !

Désolé, je voulais dire "voilà ton schéma de requête appliqué à mon besoin" !
Merci quand même pour le compliment...

J'ai quand même fait l'effort de réécrire ta requête telle que je la comprends.

Merci et bravo

C'est bien cela, j'ai juste oublié de préciser que "slug" n'est pas dans "annonces_specific_fields" (d'où mes "horribles" requêtes imbriquées).

Elle devrait donner les annonces_id pour lesquelles les kilomètres sont <= 100 000 ET l'année est comprise entre 2000 et 2005 inclus ET l'énergie est l'essence.

Exactement

Mais cela ne marche pas, désolé ! Il y a bien dans mes tables une annonce et une seule qui matche ces 3 conditions à la fois, or ta requête ne renvoie rien... Même en ajoutant une jointure avec la table "specific_fields" qui contient les slugs.

Au passage, les valeurs textuelles s'écrivent entre apostrophes, pas entre guillemets.

Quand on génère la requête en PHP avec des concaténations sur des strings, parfois le guillemet est bien pratique et ne pose aucun souci à MySQL...

**Maljuna Kris** · 05/02/2010, 15h18

Saluton,

Envoyé par avairet

Quand on génère la requête en PHP avec des concaténations sur des strings, parfois le guillemet est bien pratique et ne pose aucun souci à MySQL...

C'est faire fi de sprintf(), voire de PDO.

**CinePhil** · 05/02/2010, 15h20

OK alors structure des tables et jeu de données exemple please sinon on ne va pas s'en sortir.

Pour ce qui est des guillemets ou des apostrophes, j'ai donné le standard SQL.
MySQL est permissif, ce n'est pas le cas de tous les SGBD alors autant prendre de bonnes habitudes.
Et en PHP, ce qui est à l'intérieur de guillemets est interprétable donc on peut écrire de cette façon :

Code php :

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

1
2
3
4
5
$requete = "
  SELECT les_colonnes
  FROM la_table
  WHERE la_colonne = '$valeur'
  ORDER BY une_colonne";

Je viens de penser à un truc : je suppose que la colonne real_value est de type VARCHAR ?
Il faut alors transformer les valeurs numériques en chaînes de caractères, ou faire une opération de transformation du type de la colonne s'il y a des calculs numériques à faire (peut-être pour le BETWEEN par exemple).

**CinePhil** · 05/02/2010, 15h20

Envoyé par Maljuna Kris

Saluton,C'est faire fi de sprintf(), voire de PDO.

+1 Maljuna Kris !
Je n'y avais pas pensé !

**avairet** · 05/02/2010, 15h35

C'est faire fi de sprintf(), voire de PDO.

Pour ce qui est des guillemets ou des apostrophes, j'ai donné le standard SQL.

Et en PHP, ce qui est à l'intérieur de guillemets est interprétable donc on peut écrire de cette façon :

Ok, ok, j'ai 8 ans de pratique PHP, je connais tout çà, il se trouve que dans ce cas précis, pour faire simple, j'ai utilisé les guillemets... je ne veux pas m'étendre ici sur le contexte applicatif, le framework utilisé etc. Ma question était purement SQL.

Je viens de penser à un truc : je suppose que la colonne real_value est de type VARCHAR ?

Oui... voilà donc a priori ce qui coince !

Et malheureusement, j'ai à la fois besoin du BETWEEN et de valeurs littérales...

Pour le moment, mon autre mécanisme fonctionne, mais sans doute qu'avec une forte volumétrie, les perfs vont morfler.

**CinePhil** · 05/02/2010, 15h49

OK alors on reprend la requête, j'y ajoute la jointure avec specific_fields et je transtype quand c'est utile la colonne real_value :

Code :

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

1
2
3
4
5
6
7
8
SELECT a.annonce_id 
FROM annonces_specific_fields AS a
INNER JOIN specific_fields AS s ON a.specific_field_id = s.id
WHERE (s.slug = 'km' AND CAST(a.real_value AS UNSIGNED) <= 100000) 
  OR (s.slug = 'annee-mod' AND CAST(a.real_value AS UNSIGNED) BETWEEN 2000 AND 2005
  OR (s.slug = 'energie' AND a.real_value = 'Essence') 
GROUP BY a.annonce_id
HAVING COUNT(*) = 3

Ca donne quoi ?

**avairet** · 05/02/2010, 15h54

Ca donne quoi ?

Ben toujours rien

**CinePhil** · 05/02/2010, 16h04

Alors je redemande ceci :

OK alors structure des tables et jeu de données exemple please sinon on ne va pas s'en sortir.

Parce que là je commence à sécher sur le problème. Faut dire que c'est vendredi après-midi aussi, je fatigue !

**avairet** · 05/02/2010, 16h14

Tu as tout, je ne peux rien ajouter de plus...
Revoici la structure des 2 tables concernées, que tu avais parfaitement déduite :

Code :

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

1
2
3
TABLE "annonces_specific_fields" :
 
id (INT, PK, AI) | annonce_id (INT) | specific_field_id (INT) | real_value (VARCHAR 45)

Code :

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

1
2
3
TABLE "specific_fields" :
 
id (INT, PK, AI) | name (VARCHAR 45) | slug (VARCHAR 45) | unit (VARCHAR 10)

Données exemples :

A) specific_fields
1 | Kilométrage | km | km
2 | Année modèle | annee-mod | null
3 | Energie | energie | null
...

B) annonces_specific_fields
1 | 7 | 1 | 78500
2 | 7 | 2 | 2002
3 | 7 | 3 | Essence
4 | 8 | 1 | 101000
5 | 8 | 2 | 2002
6 | 8 | 3 | Essence
7 | 9 | 1 | 95800
8 | 9 | 2 | 1999
9 | 9 | 3 | Essence
10 | 10 | 1 | 95800
11 | 10 | 2 | 2003
12 | 10 | 3 | GPL
...

Ca te convient ? Dans notre cas, seule l'annonce 7 doit sortir, car c'est la seule qui matche les 3 conditions.

**CinePhil** · 06/02/2010, 23h33

L'erreur que tu obtenais était une erreur de syntaxe car il manquait une parenthèse à la fin du BETWEEN, avant le dernier OR.

Voici la bonne requête :

Code :

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

1
2
3
4
5
6
7
8
SELECT a.annonce_id 
FROM annonces_specific_fields AS a
INNER JOIN specific_fields AS s ON a.specific_field_id = s.id
WHERE (s.slug = 'km' AND CAST(a.real_value AS UNSIGNED) <= 100000) 
  OR (s.slug = 'annee-mod' AND CAST(a.real_value AS UNSIGNED) BETWEEN 2000 AND 2005)
  OR (s.slug = 'energie' AND a.real_value = 'Essence') 
GROUP BY a.annonce_id
HAVING COUNT(*) = 3

**avairet** · 07/02/2010, 14h08

Salut,

Merci de ton aide et de ta patience. J'avais vu la parenthèse manquante

Le souci n'est pas là.

Je n'ai pas d'erreur SQL, c'est juste que ta requête ne ramène aucun résultat alors qu'elle devrait ramener l'id 7 !

Même si je ne pense pas que cela puisse jouer, je te précise que j'utilise MySQL 5.1.

Toutefois, j'avais fait pas mal de tests moi aussi avec GROUP BY et HAVING, sans résultat. J'avais trouvé des pistes avec EXISTS mais certains résultats n'étaient pas toujours exacts. Avec ma requête, je trouve toujours les bons résultats, même si je pense qu'elle n'est pas totalement optimisée.

**CinePhil** · 07/02/2010, 14h16

Ben chez moi et avec tes données exemple, ça ramène bien l'id 7 !

**avairet** · 09/02/2010, 16h22

Hello,

Donc comme je le disais dans un message qui n'a pas du s'enregistrer ou qui a été modéré, j'avais des résultats vides chez moi sous MySQL 5.1 en lançant les requêtes depuis MySQLWorkbench 5.2. mais je n'en avais pas en prod avec MySQL 5.0, depuis PhpMyAdmin...

J'ai donc codé la requête à ta manière dans mon PHP et j'ai testé sur les deux environnements avec succès.

Du coup, je pense plus à un bug de Workbench... qui est en bêta et pas mal buggé.

Par contre j'ai fait pas mal de bench sur les deux versions (la tienne avec HAVING COUNT et la mienne avec un produit scalaire de la table et des sous-requêtes) et des analyses avec EXPLAIN :
- en terme de perf pures pour exécuter la requête, ma version est presque toujours plus rapide
- en terme d'analyse effectuée, la tienne semble plus performante, en parcourant au final moins de ligne

Il me faudra refaire ces tests lorsque la volumétrie aura sérieusement augmentée

En tout cas, merci pour ce schéma basique que je n'arrivais jamais à retrouver lorsque j'ai besoin de trouver des lignes qui remplissent TOUTES les conditions.

Trouver les lignes qui vérifient des valeurs différentes pour une même colonne, sans OR

Requêtes MySQL

Discussions similaires

Partager

Partager