Eviter les join multiples

Bonjour à tous,

j'ai actuellement une base sqlite avec 2 tables :
Les tables
detection :

Code:

---

1 2 3 4 5

CREATE TABLE `detection` ( `created_at` TEXT NOT NULL, `count_value` INTEGER NOT NULL, `type_obj` INTEGER NOT NULL DEFAULT 0 );

---

et caracteristique :

Code:

---

1 2 3 4 5

CREATE TABLE `caracteristique ` ( `key` TEXT NOT NULL, `value` TEXT NOT NULL, `id_datas`INTEGER NOT NULL );

---

Il n'ya pas de clé étrangère entre ces deux tables car la table caracteristique peux contenir des données pour d'autres tables (j'ai volontairement exclu les champs non utile à ma question).

Donc une ligne de la table "detection" peux avoir n lignes dans la table "caracteristique". L'idée derrière les champs key et value c'est que les caracterisitques et valeurs sont des champs "libre" et donc les utilisateur peuvent créer des caracteristique sans avoir à intervenir sur la structure de la bdd.

Les données

Exemples de données pour bien comprendre :
detection :
"2019-06-26 09:40:00" "1" "1"

caracteristique :
"age" "35" "4"
"gender" "M" "4"

Avec cette architecture je peux donc facilement trouver toutes les détections qui ont par exemple un genre égal à "M" :

Code:

---

SELECT * FROM detection d LEFT JOIN caracteristique ca ON d.rowid = ca.id_datas AND ca.type_table = 1 WHERE ca.key = 'gender' AND ca.value = 'M';

---

Le select * est juste là pour la démo , pas tapé :D

Mon problème arrive quand je veux combiner plusieurs "caracteristiques" , par exemple faire la somme de toutes les détections ayant un genre à 'M' et un age égale à "35".
J'ai réussi en faisant une jointure supplémentaire mais je trouve ça fastidieux (surtout si j'ai bcp de caracteristiques à combiner) :

Code:

---

SELECT * FROM detection d LEFT JOIN caracteristique ca ON d.rowid = ca.id_datas AND ca.type_table = 1 LEFT JOIN carac ca2 ON d.rowid = ca2.id_datas AND ca2.type_table = 1 WHERE ca.key = 'gender' AND ca.value = 'M' AND ca2.key = 'age' AND ca2.value='35';

---

Y'a t'il moyen de faire mieux ?

Merci

bonjour,

Vous avez une alternative avec un regroupement, mais pas sûr du coup que ça soit plus efficace, surtout si la table caracteristique est correctement indexée

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT rowid , SUM(count_value) AS somme FROM detection d LEFT JOIN caracteristique ca ON d.rowid = ca.id_datas AND ca.type_table = 1 WHERE ca.key IN ('gender', 'age') GROUP BY rowid HAVING MAX(CASE WHEN ca.key = 'gender' AND ca.value = 'M' THEN 1 END) = 1 AND MAX(CASE WHEN ca.key = 'age' AND ca.value = '35' THEN 1 END) = 1 ;

---

Merci de la réponse.

La table caracteristique est indexée avec un index unique (key,id_datas).

En passant par un regroupement la requête semble être plus facile à générer dans le code , c'est une alternative intéressante.

En revanche dans ton exemple le WHERE n'a pas lieux d'être je pense.

Concernant le WHERE, le but et de permettre enventuellement un filtre en amont, pour manipuler moins de données. il est donc là juste pour aider l'optimiseur.

Cela dit, je ne sais pas si le résultat de ma requete est vraiment exact : suite à la jointure, il y aura donc deux lignes pour chaque ligne dans la table detection, chaque valeur sera donc comptée deux fois.

quelle somme voulez-vous faire exactement ?

un petit jeu d'essai + résultat attendu aiderait certainement.

Enfin, concernant l'index, mettre id_datas en premier serait peut-être préférable (certainement plus discriminant), mais surtout il faudrait ajouter la colonne type_table afin que l'index soit couvrant

Je suis pas non plus certains que les résultats soient juste.
Je n'arrive pas aux même résultat qu'avec les mutiples join sur certaines recherche.

Citation:

---

mais surtout il faudrait ajouter la colonne type_table afin que l'index soit couvrant

---

il l'est , je l'ai pas précisé car je l'avais pas mis dans mon create table , mais sur le jeux de données réel il est bien présent.

Le regroupement avec having n'est pas forcément plus claire à la lecture, donc je vais peut être choisir la simplicité et resté avec les join.

Citation:

---

Envoyé par grunk

il l'est , je l'ai pas précisé car je l'avais pas mis dans mon create table , mais sur le jeux de données réel il est bien présent.

---

je voulais dire : ajouter la colonne type_table à l'index
ainsi, il ne devrait plus y avoir d’accès à la table caractéristique, l'index suffira