Soucis de requête d'exclusion avec champ SET

Jc · 24-07-2011 09:32:00

PS: Je profite de l'occasion pour donner un ressenti que j'ai à propos des champs SET, c'est que personne ne les utilise (ou rarement pour des choses simples), quitte à dénormaliser un peu/beaucoup leur base de données, car ils ne savent pas les requêter correctement. J'ai trouvé par exemple sur le net (même en anglais) qu'un seul exemple de solution pour faire un INSERT INTO table (champ1,champ2,champSET, champ4) values (?,?,?,?) en PDO. Ils passent par une procédure SQL pour concaténer leur chaine et l'insérer dans la requête pour qu'elle soit interprétée correctement, alors qu'il y a beaucoup plus simple.... (sans vouloir concurrencer Pierrot )

Maljuna Kris · 24-07-2011 09:58:39

Saluton,
C'est parce que j'ai privilégié une solution sans sub-select (MySQL est peu performant avec les sub-select), sinon on doit pouvoir traiter le tout en jouant sur des sub-select dans des clauses WHERE NOT EXISTS, au prix d'une chute de performances par rapport à des jointures.

Maljuna Kris · 24-07-2011 10:06:05

Jc a écrit :

PS: Je profite de l'occasion pour donner un ressenti que j'ai à propos des champs SET, c'est que personne ne les utilise (ou rarement pour des choses simples), quitte à dénormaliser un peu/beaucoup leur base de données, car ils ne savent pas les requêter correctement. J'ai trouvé par exemple sur le net (même en anglais) qu'un seul exemple de solution pour faire un INSERT INTO table (champ1,champ2,champSET, champ4) values (?,?,?,?) en PDO. Ils passent par une procédure SQL pour concaténer leur chaine et l'insérer dans la requête pour qu'elle soit interprétée correctement, alors qu'il y a beaucoup plus simple.... (sans vouloir concurrencer Pierrot )

Ce n'est pas qu'un problème de normalisation ou de difficulté de requêtage, à l'instar des colonnes de type ENUM, les colonnes de type SET sont, quoi qu'on en pense, moins performantes que des jointures vers des nomenclatures et occupent davantage d'espace dans les bases de données.
Personnellement je privilégie toujours l'approche relationnelle dans les schémas de mes bases de données. Ne serait-ce qu'en termes d'évolutivité, il est toujours plus facile de maintenir une nomenclature, même avec peu d'occurrences, qu'un jeu de valeurs (SET ou ENUM).

Jc · 24-07-2011 10:45:40

Je reste d'accord avec toi sur le principe, sauf quand l'approche relationnelle dénormalise la base dans le mauvais sens.
Un champ enum contrairement au champ SET ne peut avoir qu'une seule valeur à la fois de sa liste alors qu'un champ SET peut en être une combinaison. Lorsque la caractéristique du champ SET est conceptuellement fermée et non évolutive et doit être inclue en clé primaire, et si l'on considère un cas de figure où un champ set ne peut prendre que trois valeurs, on peut être amené à multiplier aisément par C(1,3)xC(2,3) enregistrements le poids des enregistrements correspondants en restant sur une approche relationnelle. J'ai déjà vu une telle approche générer sur une volumétrie minimaliste d'utilisation de l'application, écart de 1,000,000 d'enregistrements pour une base de 360,000 enregistrements avec un champ SET. Et les projections de montée en charge faisaient vraiment peur. Dans ce cas en plus les requêtes de productions restaient simples.
Dans mon cas ici, ce choix a été murement réfléchi et reste normalisé au sens du cahier des charges et en considérant le reste de la structure, car on n'aura jamais à requêter sur l'ensemble des données mais que sur l'établissement (etbid) concerné, et si on a 16 enregistrements par établissement cela sera le max. Vu le nombre d'enregistrements, utiliser une sous-requête n'aura pas d'impact sur les performances.
Tes arguments dans l'ensemble ne m'ont pas convaincu.

Dernière modification par Jc (24-07-2011 10:47:39)

Maljuna Kris · 24-07-2011 11:22:05

Tout type de colonne non scalaire, c'est à dire qui intègre, ou permet d'intégrer, plusieurs valeurs distinctes que peut prendre une colonne est une hérésie relationnelle et je fais toujours en sorte d'éviter de m'affranchir de cette norme dans mes schémas de bases de données.
Y déroger c'est, la plupart du temps, se compliquer inutilement la vie dans l'interrogation et la maintenance d'une base de données.
Et pourtant on sait comme cela m'amuse de trouver des requêtes parfois assez tarabiscotées pour pallier les défauts conceptuels des bases de données des uns ou des autres, ou s'affranchir des limites du SQL, notamment celui de MySQL.

Jc · 24-07-2011 11:57:11

Comme tu le dis si bien, "la plupart du temps".
Mais comme l'objet de ma réfléxion portait sur les aspects particuliers, je vais opposer ton hérésie relationnelle à l'hérésie conceptuelle. En effet Si la colonne SET existe c'est notamment pour éviter qu'une entité unique soit représentée par deux ou plusieurs enregistrements en base de données, ce qui, lorsque cela arrive, va à l'encontre de la notion même de clé Primaire et j'en passe. Pour peu que les relations de cette table soient sur un plan relationnel assez complexe, cela devient purement ingérable à tous les niveaux.

Ensuite un champ SET est géré au niveau BIT par MySQL, ce qui reste beaucoup plus rapide que d'autres.

A utiliser avec modération donc et lorsque cela reste nécessaire.

Dernière modification par Jc (24-07-2011 12:59:13)

Maljuna Kris · 24-07-2011 14:11:32

Il en demeure pas moins démontré, par l'objet même de ce post, que l'interrogation de la base en mode relationnel devient ardue, voire tordue, pour certaines opérations relationnelles comme celle qui te préoccupe, alors que le respect de la forme normale aurait débouché sur une requête plus naturelle.
Après, que cela fournisse l'occasion de trouver plaisir à contourner la difficulté en faisant preuve d'ingéniosité ou, à défaut, de malice n'est pas le problème, c'est juste contre-productif.
Ce qui explique pourquoi je me montre assez intraitable quant au fait de s'affranchir des formes normales jusqu'au niveau 3, c'est que je ne connais pas d'exemple où l'on ne le paie pas, souvent très cher, en termes de prise de tête notamment, .
Quant aux optimisations, souvent mises en avant pour justifier ces prises de liberté, on les voit souvent cohabiter avec une indexation peu optimisée, voire un recours inconsidéré aux bases de données pour gérer une information quasi invariante, et un oubli tout aussi systématique du recours aux caches.

Jc · 24-07-2011 15:27:10

Je comprends bien.
Parfois cependant, il arrive un moment, où la difficulté n'est pas "contournable" et on ne peut pas parfois changer la définition d'un objet du cahier des charges juste pour éviter les prises de tête.
Je reste convaincu que la requête finale pour revenir à l'objet de mon post, restera relativement simple, dès que j'ai un peu de temps pour revenir dessus (il le faudra de toute façon) je m'y colle et je posterai la solution retenue.

Merci MK

Maljuna Kris · 25-07-2011 06:05:11

Jc a écrit :

Oups... j'ai oublié de préciser une chose.
Il faut rajouter une option dans la table de définition. Appelons-là opt. elle est définie en (true/false). Quand l'option est "true" et nbsj dans la même table (table def) est 'midi,soir' la valeur de nbsj dans la table travail ne peut être que 'midi,soir' également, sinon (si opt est "false") la valeur de nbsj dans la table travail peut-être soit 'midi' soit 'soir'.

Peux-tu compléter et/ou amender cette table de vérité ?

[c]+++++++++++++++++++++++++++++++++
+ t + def +
+ + + +
+ nbsj + nbsj + opt +
+++++++++++++++++++++++++++++++++
+ midi + midi + ? +
+ midi + midi,soir + false +
+ soir + soir + ? +
+ soir + midi,soir + false +
+ midi,soir + midi,soir + ? +
+ + + +
+++++++++++++++++++++++++++++++++[/c]

Jc · 29-07-2011 19:10:30

Je refais la table de vérité car on est sur un champ SET et à ce titre, 'midi,soir' est différent de 'midi','soir' et il n'en est pas tenu compte.

Voilà donc

[c]************************************************
* def * t *
* * * *
* opt * nbsj * nbsj *
************************************************
* true * 'midi,soir' * 'midi,soir' *
* false * 'midi' * 'midi' *
* false * 'soir' * 'soir' *
* false * 'midi,soir' * 'midi' *
* false * 'midi,soir' * 'soir' *
************************************************[/c]

Ce tableau recense toutes les valeurs possibles des colonnes. Ecrit différemment donc, les valeurs possibles de t.nbsj sont 'midi','soir' lorsque def.opt=false et def.nbsj='midi,soir'.

++

Dernière modification par Jc (29-07-2011 19:13:07)

Maljuna Kris · 30-07-2011 00:10:52

Ceci démontre le caractère tout à fait factice de cette colonne def.opt qui ne sert à rien dans 2 cas sur 5.

Maljuna Kris · 30-07-2011 00:59:22

Pour rester constructif je pense avoir trouvé cette solution globale

(SELECT d.etbid,d.gsid,d.nbsj,d.opt,

       CASE WHEN opt=TRUE

                THEN "midi,soir"

                ELSE CASE WHEN d.nbsj <> "midi,soir"

                               THEN d.nbsj

                               ELSE NULL

                     END

       END AS manquant

FROM def d

LEFT JOIN travail tt

    ON d.etbid=tt.etbid AND d.gsid=tt.gsid

WHERE t.etbid IS NULL

    AND manquant IS NOT NULL)

UNION

(SELECT d.etbid,d.gsid,d.nbsj,d.opt,"soir" AS manquant

FROM def d

INNER JOIN travail t

    ON d.etbid=t.etbid AND d.gsid=t.gsid AND t.nbsj="midi"

LEFT JOIN travail tt

    ON d.etbid=tt.etbid AND d.gsid=tt.gsid AND tt.nbsj="soir"

WHERE d.opt=FALSE AND d.gsid="midi,soir" AND tt.gsid IS NULL)

UNION

(SELECT d.etbid,d.gsid,d.nbsj,d.opt,"midi" AS manquant

FROM def d

INNER JOIN travail t

    ON d.etbid=t.etbid AND d.gsid=t.gsid AND t.nbsj="soir"

LEFT JOIN travail tt

    ON d.etbid=tt.etbid AND d.gsid=tt.gsid AND tt.nbsj="midi"

WHERE d.opt=FALSE AND d.gsid="midi,soir" AND tt.gsid IS NULL)

Mais tu avoueras que cela relève vraiment de la capilotraction.

Maljuna Kris · 30-07-2011 05:24:38

D'autant que, je m'aperçois que cette requête d'artillerie lourde n'embarque pas un cas, qui n'a peut-être pas de sens dans le contexte, mais qui reste plausible dans l'absolu : le cas ou def.opt est false pour nbsj="midi,soir" et où "midi" et "soir" sont tous les deux manquants dans travail pour un couple etbid-gsid.
Là-dessus, je vais essayer d'aller dormir un brin, histoire de ne pas rejoindre Raf en ce bel été 2011.
Je rigole sous cape en imaginant la tête de Pierrot voyant évoluer ce post vers une super usine à gaz.:lol:

Maljuna Kris · 30-07-2011 05:46:59

Si tu acceptes un retour tabulaire on peut modifier la requête comme ceci

    (SELECT d.etbid,d.gsid,d.nbsj,d.opt,

           CASE WHEN opt=TRUE

                    THEN "midi,soir"

                    ELSE CASE WHEN d.nbsj <> "midi,soir"

                                   THEN d.nbsj

                                   ELSE "midi et soir"

                         END

           END AS manquant

    FROM def d

    LEFT JOIN travail tt

        ON d.etbid=tt.etbid AND d.gsid=tt.gsid

    WHERE t.etbid IS NULL)

    UNION

    (SELECT d.etbid,d.gsid,d.nbsj,d.opt,"soir" AS manquant

    FROM def d

    INNER JOIN travail t

        ON d.etbid=t.etbid AND d.gsid=t.gsid AND t.nbsj="midi"

    LEFT JOIN travail tt

        ON d.etbid=tt.etbid AND d.gsid=tt.gsid AND tt.nbsj="soir"

    WHERE d.opt=FALSE AND d.gsid="midi,soir" AND tt.gsid IS NULL)

    UNION

    (SELECT d.etbid,d.gsid,d.nbsj,d.opt,"midi" AS manquant

    FROM def d

    INNER JOIN travail t

        ON d.etbid=t.etbid AND d.gsid=t.gsid AND t.nbsj="soir"

    LEFT JOIN travail tt

        ON d.etbid=tt.etbid AND d.gsid=tt.gsid AND tt.nbsj="midi"

    WHERE d.opt=FALSE AND d.gsid="midi,soir" AND tt.gsid IS NULL)

Jc · 30-07-2011 15:37:10

Merci MK pour les requêtes. Je n'ai pas le temps là d'y jeter un oeil, mais je te fais le retour dessus la semaine prochaine).

citation a écrit :

Ceci démontre le caractère tout à fait factice de cette colonne def.opt qui ne sert à rien dans 2 cas sur 5.

C'est justement la spécificité métier de l'option. Gérer les 3 autres cas.

citation a écrit :

D'autant que, je m'aperçois que cette requête d'artillerie lourde n'embarque pas un cas, qui n'a peut-être pas de sens dans le contexte, mais qui reste plausible dans l'absolu : le cas ou def.opt est false pour nbsj="midi,soir" et où "midi" et "soir" sont tous les deux manquants dans travail pour un couple etbid-gsid.

Remarque judicieuse, car elle fait partie également du cahier des charges. Il faut que la requête retourne les valeurs possibles de t.nbsj (pour un couple etbid-gsid) selon le contexte de def mais aussi selon le contexte de t.

++;)

Jc · 06-08-2011 07:52:32

Bonjour,

Donc comme convenu je reviens vers vous pour partager avec vous la solution que j'ai retenue.
Un grand Merci à MK, car j'avoue que j'avais un peu oublié "l'effet NULL" sur les jointures lorsqu'il n'y a pas d'enregistrement sur la jointure, et c'est la méthode que j'ai choisie car comme je l'ai expliqué précedemment, je me devais d'exclure les requêtes union et celles qui utilisent la forme "not in", pour finalement passer par les requêtes dérivées.
Donc après un petit effort de transposition de la requête de production pour vous voici ce que cela donne :

SELECT t3.gsid, 

    CASE t3.nbsj = 'midi,soir'

          WHEN (t1.nbsj = 'midi,soir') THEN 'Aucun'

          WHEN (isnull( t1.nbsj ) AND t2.nbsj = 'midi') THEN 'soir'

          WHEN (isnull( t1.nbsj ) AND t2.nbsj = 'soir')  THEN 'midi'

          WHEN (isnull( t1.nbsj ) AND isnull( t2.nbsj ) AND NOT opt) THEN 'midi+soir'

          WHEN (isnull( t1.nbsj ) AND isnull( t2.nbsj ) AND opt) THEN 'midi,soir'

    ELSE IF( t2.nbsj, 'Aucun', t3.nbsj ) 

    END AS dispo

FROM def AS t3 LEFT JOIN 

(SELECT gsid, nbsj FROM travail WHERE etbid = x AND (nbsj = 'midi' OR nbsj = 'soir') GROUP BY nbsj HAVING count( nbsj ) =2) AS t1 ON t3.gsid = t1.gsid

LEFT JOIN 

(SELECT gsid, nbsj FROM travail WHERE etbid = x AND (nbsj = 'midi' OR nbsj = 'soir') GROUP BY nbsj HAVING count( nbsj ) =1) AS t2 ON t3.gsid = t2.gsid

WHERE t3.etbid =x

Comme je m'en doutait, elle est très simple et couvre l'ensemble des cas de figure présenté dans le tableau précédent.

Merci à vous,

Cordialement,

Jc

Dernière modification par Jc (06-08-2011 08:14:12)

Jc · 06-08-2011 09:22:06

Bon.. Je viens de me rendre compte que la requête n'est pas stable pour un contexte particulier.
Je suis donc obligé de la complexifier un poil... et voici donc le resultat stable à 100%

SELECT t5.gs_id,
CASEWHEN t5.nbsj='midi,soir'THEN t4.multi ELSE t4.simpleENDas dispo

FROM def as t5 LEFT JOIN

(SELECT t3.gsid, 

   CASE t3.nbsj='midi,soir' 

     WHEN t1.nbsj='midi,soir' THEN 'Aucun' 

     WHEN isnull(t1.nbsj) AND t2.nbsj='midi' THEN 'soir'

     WHEN isnull(t1.nbsj) AND t2.nbsj='soir' THEN 'midi' 

     WHEN (isnull(t1.nbsj) and isnull(t2.nbsj) AND NOT opt) THEN 'midi+soir' 

     WHEN (isnull(t1.nbsj) and isnull(t2.nbsj) AND opt) THEN 'midi,soir' 

   END as multi,IF(t2.nbsj,'Aucun',t3.nbsj) as simple

   FROM def AS t3

   LEFT JOIN (SELECT gsid, nbsj FROM travail WHERE etbid =1 AND (nbsj = 'midi' OR nbsj = 'soir') GROUP BY nbsj HAVING count( nbsj ) =2) AS t1 ON t3.gsid = t1.gsid

   LEFT JOIN (SELECT gsid, nbsj FROM travail WHERE etbid =1 AND (nbsj = 'midi' OR nbsj = 'soir') GROUP BY nbsj HAVING count( nbsj ) =1) AS t2 ON t3.gsid = t2.gsid

 WHERE t3.etbid =1) as t4 ON t5.gsid=t4.gsid WHERE t5.etbid=1

++

EDIT: Comme vous le voyez, la solution stable consiste à dériver la précédente.

Dernière modification par Jc (06-08-2011 09:42:51)

PHP|Débutant :: Forums

Advertisement

#26 24-07-2011 09:32:00

Re : Soucis de requête d'exclusion avec champ SET

#27 24-07-2011 09:58:39

Re : Soucis de requête d'exclusion avec champ SET

#28 24-07-2011 10:06:05

Re : Soucis de requête d'exclusion avec champ SET

#29 24-07-2011 10:45:40

Re : Soucis de requête d'exclusion avec champ SET

#30 24-07-2011 11:22:05

Re : Soucis de requête d'exclusion avec champ SET

#31 24-07-2011 11:57:11

Re : Soucis de requête d'exclusion avec champ SET

#32 24-07-2011 14:11:32

Re : Soucis de requête d'exclusion avec champ SET

#33 24-07-2011 15:27:10

Re : Soucis de requête d'exclusion avec champ SET

#34 25-07-2011 06:05:11

Re : Soucis de requête d'exclusion avec champ SET

#35 29-07-2011 19:10:30

Re : Soucis de requête d'exclusion avec champ SET

#36 30-07-2011 00:10:52

Re : Soucis de requête d'exclusion avec champ SET

#37 30-07-2011 00:59:22

Re : Soucis de requête d'exclusion avec champ SET

#38 30-07-2011 05:24:38

Re : Soucis de requête d'exclusion avec champ SET

#39 30-07-2011 05:46:59

Re : Soucis de requête d'exclusion avec champ SET

#40 30-07-2011 15:37:10

Re : Soucis de requête d'exclusion avec champ SET

#41 06-08-2011 07:52:32

Re : Soucis de requête d'exclusion avec champ SET

#42 06-08-2011 09:22:06

Re : Soucis de requête d'exclusion avec champ SET

Pied de page des forums