Bonjour,
J'ai une grosse requête qui ne fonctionne pas encore sur MySQL 5.0.22.  
 

 
En fait, j'ai une table Aow à laquelle on peut ajouter des champs personnalisés (champs en plus des champs présents dans la table Aow). La table CustomFields contient entre autre l'ID et le nom (unique) de chaque champ personnalisé. La table CustomFieldsValues contient la ou les valeurs associées à chaque champ personnalisé et à une entrée dans la table Aow (jointure via le champ ObjectID qui contient la valeur de AowID). Un couple [champ personnalisé, ObjectID] peut avoir plusieurs valeurs dans le cas où le champ est paramétré pour historiser les changements de valeurs du champ.
 
Mon problème se situe au niveau de la partie "AS cfv, CustomFields cf0, cfv AS cfv1, CustomFields cf1". cfv, via une sous-requête, contient la dernière valeur de chaque couple [champ perso,ObjectID]. Quand je recherche sur 1 seul champ personnalisé, j'avais trouvé une requête qui fonctionnait. Mais si j'ai comme critères de recherche 2 champs personnalisés, je me retrouve à devoir multiplier les jointures sur mes tables liées aux champs personnalsiés et à leurs valeurs. Comem une sous-requête coûte cher en temps, je voudrais pouvoir dupliquer facilement le contenu de la table cfv pour pouvoir effectuer d'autres jointures dessus.
 
Avez-vous une solution, svp? Merci

J'ai essayé de stocker le résultat de cette requête dans une table temporaire :

Et ensuite de l'appeler plusieurs fois avec des alias différents mais en ça marche pas à cause d'une limitation de MySQL sur les tables temporaires
http://forums.mysql.com/read.php?20,198873
 
Là, je suis bien eu. Va falloir que je fasse plusieurs tables temporaires (une par champ personnalisé) ...

Bon ben en créant 1 table temporaire par champ perso, ça marche, mais j'ai un peu peur des perfs par la suite quand ma table contenant les valeurs des champs perso va être pas mal remplie ...
 
Vous n'auriez pas mieux à me proposer, svp?

up

Je viens seulement de voir ton PM.
 
Honnêtement, j'ai rien pigé à ton problème, et j'ai pas trop insisté tu me diras...
 
C'est quoi le souci ?
 
Genre tu cherches les lignes de ta table pour lesquelles tu as les champs personalisés "toto" et "titi" avec les valeurs respectives "1" et "2", c'est ça ? (et ceci avec un nombre indéfini de champs personnalités ?)

oui, en gros, mais j'ai une table qui contient le paramétrage des champs personnalisés (ID, nom, type de valeur...), donc entre autre les nom "titi" et "toto" et une autre table qui contient leurs valeurs associées, un champ perso pouvant avoir plusieurs lignes dans cette table de valeurs si on a définit que les changements de valeurs du champ perso pouvaient être historisés.
 
Donc :
Table CustomFields
CustomFieldID
CustomFieldName
....
 
Table CustomFieldsValues
CustomFieldValueID      <- clé primaire
CustomFieldValueDate   <- date/heure à laquelle la valeur a été saisie
CustomFieldValueData   <- valeur du champ perso à une date donnée
CustomFieldID              <- Clé étrangère
ObjectID                     <- Clé étrangère pointant sur une autre table, ici, la table Aow, donc ObjectID pointe sur AowID
 
Table Aow (tickets)
AowID
AowRef
...
 
Aow contient des tickets (help-desk) et certains types de tickets peuvent avoir, en plus des champs définis dans la table Aow, des champs supplémentaires, dits champs personnalisés. Une ligne dans Aow peut avoir 0 ou plusieurs lignes dans CustomFieldsValues. Mon pb de pouvoir sortir la liste des tickets de la table Aow qui  ont pour champs personnalisés "toto" qui a comme valeur courante "1" et "titi" qui a comme valeur courante "2".
 
C'est plus clair?
 
Merci en tout cas de ton aide, mon pb est toujours d'actualité.

J'ai l'impression que le problème se trouve davantage au niveau du schéma relationnel qu'au niveau de la requête...  
 
SELECT A.AowID, CF.CustomFieldName, CFV.CustomVieldValueData FROM Aow A, CustomFields CF, CustomFieldsValues CFV WHERE CFV.ObjectID = A.AowID AND CFV.CustomFieldID = CF.CustomFieldID AND CF.CustomFieldName="titi" AND CFV.CustomFieldValueData="1"
 

Ca ne va pas marcher car dans le cas de champs ayant leurs changements de valeur historisés, je vais avoir la première valeur et non la valeur courante (la dernière).

Non, c'est une jointure libre. Tu auras une ligne de résultats par valeur historisée.  
 
Si cependant tu ne veux que la dernière, rien ne t'empêche d'ajouter une condition sur une sous-requête, ou si un seul résultat t'intéresse, de faire un order by et un limit. Mais c'est plus laid que de profiter de la jointure.

c'est ce que j'ai fait (voir mes requêtes postés précédemment). Mon pb est de pouvoir retrouver des enregistrements de la table Aow qui sont liés à plusieurs champs perso dont la dernière valeur de chaque champ perso vaut une valeur donnée par l'utilisateur (moteur de recherche).
Ta requête ne travaille que sur 1 champ perso, mois, j'en veux plusieurs.

Et bien tu fais une simple jointure comme j'ai fait en retirant la condition et tu auras accès à tous tes champs personnalisés.

Bon, apparemment t'as pas compris mon pb ou alors, le SQL, c'est pas ton truc. Je vais donc faire un ex concret.
Table Aow

Table CustomFields

Table CustomFieldsValues

Donc j'ai 3 tickets dans ma table Aow. Tous les 3 ont, en plus de leurs champs dans la table Aow, des champs personnalisés. Les 2 premiers tickets (1 et 2) sont liés à 2 champs personnalisés (le 1 et le 2), le 3ième ticket est lié au champ perso n° 3. De plus, le champ personnalsié n°2 a ses changements de valeur historisés.
Dans mon moteur de recherche, je veux pouvoir lister les tickets (de la table Aow, donc)  qui ont comme valeur courante "Site%" pour le champ perso "Site" et comme valeur courante 5 pour le champ perso "Magasin". Le résultat est donc seulement le ticket n°2. Le ticket 1 ne correspondant pas car la valeur courante de son champ perso "Magasin" est 1 (bien qu'il soit passé à un moment donné à 5) et le ticket 3 n'est pas lié au champ perso "Magasin".
 
C'est plus clair, là? Tu vois donc que ta requête n'est pas bonne.

 
Select Aow.*
From Aow inner join  
 (
 select  
  CF.CustomFieldID ,
  CF.CustomFieldName ,
  max(CFV.CustomFieldValueDate) as CustomFieldValueDate ,
  CFV.ObjectID
 from  CustomFields CF inner join CustomFieldsValues CFV
  on CF.CustomFieldID = CFV.CustomFieldID
  and  (  (CF.CustomFieldID = 1 and CFV.CustomFieldData like 'Site%')  
  or  (CF.CustomFieldID = 2 and CFV.CustomFieldData = '5')  
  )
 group by  
  CF.CustomFieldID ,  
  CF.CustomFieldName ,  
  CFV.ObjectID
 ) A
 on Aow.AowID = A.ObjectID
 

Ou peut-être pourrais-tu envisager le fait que tu n'aies pas expliqué clairement ton problème ?

kler! t'es un peu brut de pomme rufo ! :-)

 
Désolé pour mon énervement, mais tu m'as proposé des requêtes tellement simple que que si mon pb était si simple, je ne serais pas venu exposer mon pb ici (et j'aurais pas appelé MagicBuzz à l'aide par MP). T'as vu la tronche de ma première requête : sans être un pro du SQL, je ne suis pas non plu un débutant
 
Et effectivement, je me suis douté que je ne m'étais peut-être pas assez bien exprimé, d'où mon ex.

 
Cette requête ne répond juste à tous les coup à mon pb à cause du OR. En effet, les tickets qui vont avoir juste 1 seul champ perso ayant la bonne valeur seront aussi retournés et moi, je veux que seuls les tickets répondant à tous les critères sur les champs perso soient retournés. Petit indice : à ma connaissance (si y'a une autre technique, je suis preneur), quand on veut faire des jointures sur plusieurs ligne d'une même table mais avec des conditions différentes portant sur un même champ (ici, je veux les champs persos dont le nom est "Site" ou "Magasin" mais les tickets doivent être liés à ces 2 champs), on doit ouvrir plusieurs fois la même table :

Maintenant, y'a plus qu'à faire en sorte que les CustomFieldData pointent sur la dernière valeur (leur valeur courante) de chaque champ perso et ce sera bon. Et pour faire ça, faut passer par une sous-requête et le résultat de cette sous-requête, faut le dupliquer pour chaque champ perso (d'où mes tables temporaires) : et c'est là que ma méthode, je la trouve pas terrible et que j'aimerais en avoir une meilleure

Tu peux toujours remplacer une table tempo par un select imbriqué

pour que "CustomFieldData pointent sur la dernière valeur", utilise :
 
 select  
  CF.CustomFieldID ,
  CF.CustomFieldName ,
  max(CFV.CustomFieldValueDate) as CustomFieldValueDate ,
  CFV.ObjectID
 from  CustomFields CF inner join CustomFieldsValues CFV
  on CF.CustomFieldID = CFV.CustomFieldID
  )
 group by  
  CF.CustomFieldID ,  
  CF.CustomFieldName ,  
  CFV.ObjectID
 
ensuite fait ta jointure avec tes tickets de la table Aow, puis greffe tes conditions sur le resultat dans ta clause where
 
rien de bien compliqué
 

Pour te faciliter la vie, est il possible de rajouter un champ dans la table des CustomFieldData ?
Tu pourrais ajouter un IS_LAST qui vaudrait 1 pour les dernieres valeurs et 0 pour toutes les valeurs historisées.
Tu n'as plus qu'a ajouter un critere dans les jointures ?
 
Je prends le probleme en cours de route, donc désolé si ma proposition est inadéquoite.

 
surtout pas! c'est une rustine qui ajoute du temps de traitement ! a bannir

L'idée n'est pas forcément bête mais est contraire au principe de la conception d'une BD : tous ce qui peut être calculé ou redondant ne doit pas être stocké. Après, comme toute règle, il y a des exceptions, genre rajouter un champ pour stocker une valeur souvent calculée/demandée... Dans mon cas, je ne pense pas que ça se justifie.
 
Pour rappel, j'ai une solution qui marche à base de tables temporaires. Mais je n'en suis pas satisfait sur le plan intellectuel et donc je cherche à trouver une meilleure solution.
 
Akario, si tu regardes la requête de mon 2ième post, c'est ce que je fais déjà pour récupérer la valeur courante de chaque champ personnalisé. Maintenant, si on applique ça à la requête de mon dernier post, ça veut dire que je duplique la requête de mon 2ième post autant de fois que j'ai de champs persos dans ma clause where. Et c'est là mon pb. Normalement, je devrais pouvoir stocker le résultat de cette requête dans une seule table temporaire et lui donner plusieurs alias pour faire comme dans la requête de mon dernier post (elle jouerait le rôle des cfv1, cfv2). Et c'est là que ça coince : Mysql empêche de mettre plusieurs alias sur une table temporaire. Je dois donc la dupliquer dans plusieurs tables temporaires

j'ai du mal à comprendre le résultat que tu veux...
 
je vais donc exprimer ma pensée, et tu me réponds oui ou non! :-)
Le jeu de résultat que tu obtiens avec ce que je t'ai envoyé, permet d'obtenir toutes les informations que tu souhaites, versionning compris.
 
Ce jeu de résultat recupere n lignes pour un ticket  
ex (avec ton jeu de test):  je recupere les infos du ticket '0903001'
# 1                  | 2009-03-01 09:01:01  | "Site n°1"      | 1             | 1
# 2                  | 2009-03-01 09:01:01  | 5               | 2             | 1
# 6                  | 2009-03-01 11:31:01  | 1               | 2             | 1
 
j'obtiens 2 lignes :
  0903001, 1 (magasin)
  0903001, Site n°1 (site)
 
Souhaites-tu obtenir ces 2 infos sur une seule ligne?  
ex:
ticket,     magasin, site
0903001, 1          , Site n°1
 
oui ou non ?

J'ai corrigé la ligne iD=7 de la table CustomFieldsValues, y'avait une erreur pour que le résultat fonctionne avec mon ex. Le bon résultat est le ticket 0903002 (ID=2) avec les champs persos ayant les entrées dans la table CustomFieldsValues ID=3 (valeur "Site n°3" ) et ID=5 (valeur=5).
 
Dans le résultat de ta requête,  on voit que le champ perso 1 a 2 valeurs de remontées (l'une n'étant pas la valeur courante, mais une valeur précédente). Ce n'est donc pas bon

 
Mouais, entre ajouter un champ qui oblige une petite manip a l'insertion des données et accelere les requetes de selection ; et une solution qui facilite l'insertion mais qui t'oblige a te casser la tete pour faire un select avec des sous requetes complexes dans tous les sens...
Apres, j'ai pas inventé ça, notre appli est en prod depuis un paquet de temps deja et c'est les dba qui nous ont fait rajouté ce champ quand la volumetrie a grossi.
Apres je suis pas dba.

En formation MySQL, le formateur nous a demandé au début si y'avait des dba dans la salle. Y'avait que des développeurs en l'occurrence. Il nous a expliqué que les dba, ben ils aimaient pas les formes normalisées (style 3NF...) et que quand il en avait dans la salle, les dba lui disaient de faire son speetch sur les formes normalisées vite fait et qu'ils s'en tapaient parce que côté perfs, les formes normalisées, c'est pas toujours tip-top...

J'ai un peu de mal à comprendre les tables :
- La liaison Aow <-> CustomFields, tu la fais comment ?
J'ai l'impression qu'il manque une table de liaison entre les deux ?
 
- L'historisation dans la même table que les données courantes, c'est indispensable ?
Il n'y aurait pas moyen de faire une table CustomFieldsHisto qui contiendrait les données historisées, et de ne garder que les données courantes dans CustomFields ?
Ou au moins, de rajouter un champ "ActiveCustomField" ?
Parce que, là, tu es obligé de faire un test sur la date

[HS]

C'est aussi que les DBA, à chaque formation qu'ils font, ils ont droit aux formes normalisées, et au laïus comme quoi il faut normaliser à tout prix.
 
Des formateurs qui pensent à préciser que les formes normales, c'est bien mais qu'il faut savoir dénormaliser ensuite, je n'en ai pas vu beaucoup    
[/HS]

La liaison entre Aow et CustomFields se fait via CustomFieldsValues (avec les champs ObjectID qui pointe sur une ID de Aow et CustomFieldID).
CustomFields est un table contenant le paramétrage des champs persos. CustomFieldsValues contient des valeurs pour des couples [champ perso, objet] (dans notre cas, l'objet est un ticket dans la table Aow).
 
Donc, pour récupérer l'ID de la valeur cournate de chaque champ de CustomFieldsValues, il "suffit" de faire cette requête :

Mais du coup, la relation Aow <-> CustomFieldsValues est du type N,N    
C'est - entre autres - pour ça que je propose une table d'historisation à part.
Sans l'historisation, on retrouve une relation correcte...  Et je te laisse le soin de regarder à quoi se réduirait la requête que tu proposes !  

Oui, c'est une relation N,N, je ne vois pas où est le pb. Qu'as-tu contre les relations N,N?

bin juste qu'une relation N,N c'est pas trivial de selectionner dans une sous requete la ligne ou la date est la plus recente

   
Le SQL est fait pour gérer des BDD Relationnelles.
A partir du moment où on a des relations N,N on abandonne le relationnel, et du coup le SQL perd de son intérêt.
La preuve, tu te retrouves, pour quelque chose qui devrait être trivial (récupérer l'ID de la valeur courante de chaque champ de CustomFieldsValues), avec des sous-requêtes et des groupements.
 
Donc, deux solutions :
1. Tu as une très bonne raison, ailleurs dans ton programme, de te retrouver avec une relation N,N
=> Il faut comparer les gains/pertes de performances, et arbitrer, en tenant compte de la complexité des solutions.
 
2. Tu n'as pas de raison particulière de conserver une relation N,N
=> Problème réglé : il faut passer à du "vrai" relationnel    
 

Bon, j'ai trouvé une méthode qui marche sans tables temporaires, par contre, c'est bête parce qu'il y a un bout de la requête qui est exécuté plusieurs fois (la partie justement qui était stockée dans les tables temporaires pour récupérer les ID des valeurs courantes des champs persos). Par contre, l'avantage, c'est que la ré-exécution de la requête est très rapide puisque mise en cache, ce qui n'est pas le cas avec la méthode des tables temporaires.