Discussion :

[fanfouer]

Bonjour,

J'ai l'occasion de me frotter aujourd'hui à une modélisation EAV, consistant en gros à associer de manière creuse des paires de clés/valeurs à des objets.

C'est normalement quelque chose d'assez classique et je ne rencontrais pas de problèmes jusqu'à ce que je cherche à sélectionner des objets possédant une clé portant une valeur égale à une constante donnée ou une valeur nulle.
Le fait de rechercher la clé de valeur nulle me semble compliqué puisqu'elle n'existe pas du tout dans ma table de paires.

Voici un exemple de requête qui me donne les résultats attendus.
Les deux jointures LEFT JOIN sont là pour donner une idée du modèle retenu.

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 DISTINCT
    obj.record_id,T.value
FROM
    objets obj
        LEFT JOIN
    tagsCollection Tc ON Tc.record_id = obj.record_id
        LEFT JOIN
    tags T ON T.tag_id = Tc.tag_id AND T.name = 'key'
WHERE
    'value' NOT IN (SELECT DISTINCT
        T.name
    FROM
        tags T
    JOIN
        tagsCollection Tc ON (Tc.tag_id = T.tag_id AND T.name = 'key')
    WHERE
        Tc.record_id = obj.record_id AND T.value != 'value' AND T.name = 'key')
ORDER BY T.value DESC;

Ici je veux donc tous les objets disposant d'une clé 'key' à la valeur 'value' ou ne disposant pas de la clé 'key'.
Donc en gros je recherche tous les objets qui ne satisfont pas cette contrainte, puis je fais mon 'NOT IN' en prenant l'opposé de cet ensemble.

Cela fonctionne, mais me semble vraiment trop compliqué, d'autant que je veux pouvoir faire ça sur plusieurs champs en même temps (donc autant de requêtes imbriquées pour l'instant). Est-ce possible et comment ?
Je suis sur qu'il y a moyen d'optimiser un peu tout ça et je souhaiterais avoir vos retour d'expérience sur ce point.

Le but derrière tout ça est d'identifier des objets de manière certaine sur la base de champs marquant leur unicité. Sauf que les objets peuvent ne pas avoir tout ces champs définis en même temps.
Par exemple, la plaque d’immatriculation et le modèle d'une voiture marquent son unicité. Les objets qui peuvent correspondre sont donc les enregistrements avec la même plaque d'immatriculation et avec le même modèle ou un modèle nul.


En vous remerciant par avance pour toute indication, bonne après-midi.

[punkoff]

bonjour,


Tant que vos opération sont des "OU", la meilleure approche est à mon sens via des UNION (ou UNION ALL selon les cas).

Code :

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

1
2
3
4
 
select ... from ... condition1
union all
select from .. not exists condition1

ceci permettra d'utiliser les index existants, sans forcer des scannage de table à cause de multiple condition OR sur une même colonne.


Concernant la complexité des requêtes, ceci est entièrement dû à la modélisation EAV (qui est affreuse en terme de perf / recherche multi critere => division relationnel systématique)
Et là pas de solution miracle.

[fanfouer]

Bonjour, merci pour cette réponse.

Tant que vos opération sont des "OU", la meilleure approche est à mon sens via des UNION (ou UNION ALL selon les cas).

Code :

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

1
2
3
4
 
select ... from ... condition1
union all
select from .. not exists condition1

ceci permettra d'utiliser les index existants, sans forcer des scannage de table à cause de multiple condition OR sur une même colonne.

En fait ce sont plutôt des ET :
"Selectionner tous les objets dont (le champ 1 vaut 'value' ou NULL) ET (le champ 2 vaut 'value' OU NULL) ET..."

Pour donner toute l'histoire, je cherche à écrire dans ma bdd des objets que j'ai déjà initialisés dans mon code métier sans pour autant connaître leur identifiant.
Ces objets peuvent avoir des données plus complètes que ce qui existe dans la bdd (l'exemple de la voiture avec seulement l’immatriculation mais pas le modèle). Il faut pourtant écrire ces informations au bon endroit, donc trouver cet endroit, sans recréer quelque chose de nouveau.
Ainsi l'objet qui sera élu au terme de cette recherche devra être celui qui a le plus de champ en commun avec le jeu de données présenté en entrée.

Selon la requête que vous avez donné, je vois que l'on fait l'union de l'ensemble "condition1" avec un de ses sous-ensemble (sur la clause not exists), on va donc avoir tout l'ensemble "condition1".
A moins que je n'ai pas compris ?

Concernant la complexité des requêtes, ceci est entièrement dû à la modélisation EAV (qui est affreuse en terme de perf / recherche multi critere => division relationnel systématique)
Et là pas de solution miracle.

En effet, elle présente beaucoup d'avantages en termes d'extensivité et pour des modèles creux. Là je comprends bien qu'il faille négocier avec l'une de ses faiblesses. C'est toujours dommage

[punkoff]

non je ne vois pas d'avantage à la modélisation EAV à part faire des plats de spaghetti.


Une autre approche a tester :

Code :

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

1
2
3
4
5
 
select ...
from ma_table a
left outer join table_options opt on a.id = opt.id
where (opt.ma_cond1 = 'x' or opt.ma_cond1 is null) AND (....)

on part sur du scannage de table mais au moins on ne le fera qu'une seule fois.
=> plus votre volumétrie grossira et plus les perfs seront déplorable.

[fanfouer]

non je ne vois pas d'avantage à la modélisation EAV à part faire des plats de spaghetti.

Celui que je vois en premier reste le moyen de faire des modèles creux.
A moins de faire des tables pour chacun des types d'objet à stocker, on ne peut pas vraiment faire autrement.
Surtout si on ne connait pas le formalisme de l'objet en question au moment de la conception (systèmes de e-commerce typiquement).
Je me trouve dans ce cas là.

Une autre approche a tester :

Code :

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

1
2
3
4
5
 
select ...
from ma_table a
left outer join table_options opt on a.id = opt.id
where (opt.ma_cond1 = 'x' or opt.ma_cond1 is null) AND (....)

Dans cette requête je suppose que opt est ma table de paires de clés/valeurs.
Quelle est la signification de ma_cond1 ?
On risque d'avoir un problème car si la clé n'est pas effectivement définie elle n'existe pas du tout dans la table, je ne peux pas faire de IS NULL puisqu'il n'y a rien à comparer.
C'est bien là qu'est mon problème.

[punkoff]

Celui que je vois en premier reste le moyen de faire des modèles creux.
A moins de faire des tables pour chacun des types d'objet à stocker, on ne peut pas vraiment faire autrement.
Surtout si on ne connait pas le formalisme de l'objet en question au moment de la conception (systèmes de e-commerce typiquement).
Je me trouve dans ce cas là.

on ne modélise pas une bdd en fonction de l'applicatif (objet).
L'applicatif, quand à lui, s'adaptera à la bdd sans aucun probleme.
Apres, bien sur, l'objectif n'est pas le même.
Soit on recherche les perfs soit la vitesse de developement / évolution, deux notions qui sont totalement incompatible. (fin de la parenthèse pour ma part)

Dans cette requête je suppose que opt est ma table de paires de clés/valeurs.
Quelle est la signification de ma_cond1 ?
On risque d'avoir un problème car si la clé n'est pas effectivement définie elle n'existe pas du tout dans la table, je ne peux pas faire de IS NULL puisqu'il n'y a rien à comparer.
C'est bien là qu'est mon problème.

ma_cond1 sera la valeur recherchée, tagCollection si je ne m'abuse.
Ma_table correspond à la table des objets.

La jointure externe + le test "IS NULL" modélise la condition "n'existe pas" ou exite
Cette approche à néanmoins un inconveignant, si un objet n'a aucune des conditions cités, il sera quand même selectionné (mais le probleme vient de l'algo que vous exposé, non de la requete)


edit : on peut "simplifier" ceci dit :

Code :

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

1
2
3
4
5
 
select ..
from ma_table a
left outer join table_options opt on ... 
where opt.id is null or opt.options in ('', '', '', '')