SELECT du type many to one to many

**Ni;co)** · 13/10/2009, 09h05

Soient les 2 tables suivantes :

table vehicules

...

Code :

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

1
2
3
4
5
6
 
id	| type          | name
------------------------------------
1	| 0		| voiture1
...
n	| 1		| moto1

table vehicules_attributs

Code :

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

1
2
3
4
5
6
7
8
9
10
 
id     | attributs_name         | attributs_value
----------------------------------------------------
1      | nb_portes              | 4
1      | nb_places              | 5
1      | couleur                |jaune
...
n	| cylindree		| 900
n	| poids                 | 185
n	| couleur		| bleu

Ces tables permettent de décrire des véhicules de différentes natures :
- des voitures avec les attributs nb portes, couleur, nb places, capacité coffre...
- des motos avec les attributs cylindrée, couleur, poids...
...

Le "reqêtage" sql à faire :

- sélectionner tous les véhicules d'un type donné (par ex, vehicules.type = 1 ,moto) avec leurs attributs;
- pouvoir effectuer un tri de cette sélection sur les valeurs d'un attribut spécifique (par exemple effectué un tri par "cylindree" ).

Autre paramètre important, je souhaite décharger ce traitement côté serveur sql, le code appli étant déjà bien gourmand et les données nombreuses...

C'est peut-être tout bête, mais je sèche...

j'ai cherché une bonne partie de la nuit... rien...

Auriz-vous des idées pour remettre de l'ordre dans mes neurones emmêlés ?
C'est ma question/défi du jour pour les pros du sql

D'avance merci...

**Cybher** · 13/10/2009, 09h23

salut,

A l'aide d'une jointure?
Peux tu nous donner le résultat que tu attends?

**CinePhil** · 13/10/2009, 09h28

Le "reqêtage" sql à faire :

- sélectionner tous les véhicules d'un type donné (par ex, vehicules.type = 1 ,moto) avec leurs attributs;
- pouvoir effectuer un tri de cette sélection sur les valeurs d'un attribut spécifique (par exemple effectué un tri par "cylindree" ).

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT v.id, v.name, a.attributs_name, a.attributs_value
FROM vehicule AS v
INNER JOIN attributs AS a ON v.id = a.id
WHERE v.type = 1
ORDER BY 
  CASE
    WHEN a.attributs_name = 'cylindree' THEN 1
    ELSE 99999
  END,
  a.attributs_value

**Ni;co)** · 13/10/2009, 11h59

ben vi, merci, mais non...

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT v.id, v.name, a.attributs_name, a.attributs_value
FROM vehicules AS v
INNER JOIN vehicules_attributs AS a ON v.id = a.id
WHERE v.type =1
ORDER BY CASE WHEN a.attributs_name = 'cylindree'
THEN 1
ELSE 99999
END , a.attributs_value
LIMIT 0 , 30

ne me renvoie pas ce que je souhaite...

parce que je souhaite :
sélectionner tous les véhicules d'un type donné (par ex, vehicules.type = 1 ,moto) avec TOUS leurs attributs; et en plus en plus pouvoir effectuer un tri de cette sélection sur les valeurs d'un attribut spécifique quel qu'il soit...

Bon de mon côté, j'ai trouvé ça, qui marche mais c'est lourd

et je ne crois pas que ce soit franchement optimisé

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT `vehicules`.`id` , `vehicules`.`name` , T1.`attributs_value` AS 'cylindree', T2.`attributs_value` AS 'couleur'
FROM `vehicules`
JOIN `vehicules_attributs` AS T1 ON `vehicules`.`id` = T1.`id`
JOIN `vehicules_attributs` AS T2 ON `vehicules`.`id` = T2.`id`
WHERE `vehicules`.`type` =1
AND T1.`attributs_name` = 'cylindree'
AND T2.`attributs_name` = 'couleur'
ORDER BY CAST( cylindree AS UNSIGNED ) ASC

qui me donne ça (soit ce que je veux au final

)

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
20
21
22
23
24
25
26
27
28
 
id        | name          | cylindree   | couleur
----------------------------------------------------
7         | moto3         | 600         | rouge/noir
7         | moto3         | 600         | rouge/noir
7         | moto3         | 600         | rouge/noir
7         | moto3         | 600         | rouge/noir
7         | moto3         | 600         | rouge/noir
7         | moto3         | 600         | rouge/noir
7         | moto3         | 600         | rouge/noir
7         | moto3         | 600         | rouge/noir
8         | moto4         | 750         | noir
5         | moto1         | 900         | bleu
5         | moto1         | 900         | bleu
5         | moto1         | 900         | bleu
5         | moto1         | 900         | bleu
5         | moto1         | 900         | bleu
5         | moto1         | 900         | bleu
5         | moto1         | 900         | bleu
5         | moto1         | 900         | bleu
6         | moto2         | 1000        | blanc/noir
6         | moto2         | 1000        | blanc/noir
6         | moto2         | 1000        | blanc/noir
6         | moto2         | 1000        | blanc/noir
6         | moto2         | 1000        | blanc/noir
6         | moto2         | 1000        | blanc/noir
6         | moto2         | 1000        | blanc/noir
6         | moto2         | 1000        | blanc/noir

je dois faire une jointure pour chaque attributs_name... alors qd j'en ai une dizaine, ça devient vraiment lourdingue comme requête

si vous voulez jouer un peu ou comme moi vous rendre chauve...

voici les tables exemples pour illustrer mon pb :

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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
 
CREATE TABLE `vehicules` (
  `id` int(11) NOT NULL auto_increment,
  `type` int(2) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
 
INSERT INTO `vehicules` (`id`, `type`, `name`) VALUES
(1, 0, 'voiture1'),
(2, 0, 'voiture2'),
(3, 0, 'voiture3'),
(4, 0, 'voiture4'),
(5, 1, 'moto1'),
(6, 1, 'moto2'),
(7, 1, 'moto3'),
(8, 1, 'moto4');
 
CREATE TABLE `vehicules_attributs` (
  `id` int(11) NOT NULL,
  `attributs_name` varchar(255) NOT NULL,
  `attributs_value` varchar(255) NOT NULL,
  KEY `attributs_name` (`attributs_name`),
  KEY `attributs_value` (`attributs_value`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
INSERT INTO `vehicules_attributs` (`id`, `attributs_name`, `attributs_value`) VALUES
(1, 'nb_portes', '4'),
(1, 'couleur', 'jaune'),
(1, 'nb_places', '5'),
(2, 'nb_places', '7'),
(2, 'couleur', 'vert'),
(2, 'nb_portes', '5'),
(5, 'couleur', 'bleu'),
(5, 'cylindree', '900'),
(5, 'poids', '185'),
(6, 'couleur', 'blanc/noir'),
(6, 'cylindree', '1000'),
(6, 'poids', '175'),
(7, 'couleur', 'rouge/noir'),
(7, 'cylindree', '600'),
(7, 'poids', '172'),
(5, 'couleur', 'bleu'),
(5, 'cylindree', '900'),
(5, 'poids', '185'),
(6, 'couleur', 'blanc/noir'),
(6, 'cylindree', '1000'),
(6, 'poids', '175'),
(7, 'couleur', 'rouge/noir'),
(7, 'cylindree', '600'),
(7, 'poids', '172'),
(8, 'couleur', 'noir'),
(8, 'cylindree', '750'),
(8, 'poids', '178'),
(3, 'couleur', 'noir'),
(3, 'nb_places', '4'),
(3, 'nb_portes', '2'),
(4, 'couleur', 'gris'),
(4, 'nb_places', '4'),
(4, 'nb_portes', '3');

merci encore de partager mon tourment

**CinePhil** · 13/10/2009, 14h30

On voit dans ta table vehicules_attributs que le même attribut revient plusieurs fois. Il aurait donc fallu externaliser les attributs.
Peut-être que tu aurais dû utiliser un concept d'héritage plutôt qu'une table d'attributs pour tous les types de vénicules ?
Une moto est un véhicule.
Une voiture est un véhicule.

Si ton domaine d'étude est seulement les véhicules, tu fais des entités spécialisées pour les attributs qui sont spécifiques à chaque type de véhicule. Il n'y en a quand même pas des dizaines !

MCD :
Voiture -(1,1)----Etre----0,1- Véhicule
Moto -(1,1)----Etre----0,1--------|
Vélo -(1,1)----Etre----0,1---------|

Tables :
Vehicule (V_Id, V_Nom, V_DateAchat...)
Voiture (VT_IdVehicule, VT_Immatriculation, VT_NbChevaux...)
Moto (MT_IdVehicule, MT_Immatriculation, MT_Cylindree...)
Velo (VL_IdVehicule, VL_TailleRoues...)

Dès lors il très facile d'avoir toutes les motos avec tous les attributs souhaités et de trier par cylindrée :

Code :

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

1
2
3
SELECT MT_IdVehicule, MT_Cylindree...
FROM Moto
ORDER BY MT_Cylindree

**Ni;co)** · 13/10/2009, 15h05

Envoyé par CinePhil

Si ton domaine d'étude est seulement les véhicules, tu fais des entités spécialisées pour les attributs qui sont spécifiques à chaque type de véhicule. Il n'y en a quand même pas des dizaines !

MCD :
Voiture -(1,1)----Etre----0,1- Véhicule
Moto -(1,1)----Etre----0,1--------|
Vélo -(1,1)----Etre----0,1---------|

Tables :
Vehicule (V_Id, V_Nom, V_DateAchat...)
Voiture (VT_IdVehicule, VT_Immatriculation, VT_NbChevaux...)
Moto (MT_IdVehicule, MT_Immatriculation, MT_Cylindree...)
Velo (VL_IdVehicule, VL_TailleRoues...)

Dès lors il très facile d'avoir toutes les motos avec tous les attributs souhaités et de trier par cylindrée :

Code :

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

1
2
3
SELECT MT_IdVehicule, MT_Cylindree...
FROM Moto
ORDER BY MT_Cylindree

non les véhicules ne sont qu'une illustration pratique...
Et Oui, je suis bien d'accord avec toi, mais je ne voulais pas créer une table à chaque ajout d'héritage... simplement parce que je n'ai pas de visibilité sur les futures branches d'héritage : c'est l'utilisateur final lui-même qui créer de nouveaux modèles : ceux-ci héritent et étendent un modèle de base avec des attributs et méthodes (en fait implémentent une interface)...
le risque est donc de se retrouver avec une appli encore + difficile à maintenir avec des tables dans tous les sens...

d'où ma tentative "d'abstraction" maximale, je ne me base que sur l'interface que j'ai modélisée... afin de gérer "facilement" l'ajout ou la suppression d'un modèle fils...
Et je dois gérer des données spécifiques déjà existentes, que je migre en utilisant l'interface afin d'en faire un modèle fils...
je ne sais pas si je suis clair là...

je me fais mal au crâne tout seul

enfin merci de ton aide...

**CinePhil** · 13/10/2009, 15h48

qui me donne ça (soit ce que je veux au final

Surtout que ce que tu cherches à avoir comme résultat est de la cosmétique qui n'est pas le boulot du SQL mais du programme qui utilise la BDD et donc de son langage de programmation.

sélectionner tous les véhicules d'un type donné (par ex, vehicules.type = 1 ,moto) avec TOUS leurs attributs;

Jusque là pas de problème avec SQL avec une jointure entre les véhicules et les attributs.

et en plus en plus pouvoir effectuer un tri de cette sélection sur les valeurs d'un attribut spécifique quel qu'il soit...

On cherche d'abord à trier les motos par cylindrée :

Code :

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

1
2
3
4
5
6
SELECT v.id, v.name, a.attributs_value AS Cylindree
FROM vehicules AS v
INNER JOIN vehicules_attributs AS a ON v.id = a.id
WHERE v.type = 1
  AND a.attributs_name = 'cylindree'
ORDER BY CAST(a.attributs_value AS UNSIGNED)

A noter que dans le jeu de données que tu fournis, tu as plusieurs fois les mêmes attributs pour la même moto !

Ensuite on récupère les autres attributs des motos en faisant une jointure de cette requête avec les vehicules_attributs :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT t.id, t.name, t.Cylindree, a1.attributs_name, a1.attributs_value
FROM (
  SELECT v.id, v.name, CAST(a.attributs_value AS UNSIGNED) AS Cylindree
  FROM vehicules AS v
  INNER JOIN vehicules_attributs AS a ON v.id = a.id
  WHERE v.type = 1
    AND a.attributs_name = 'cylindree'
) AS t
INNER JOIN vehicules_attributs AS a1 ON t.id = a1.id
WHERE a1.attributs_name <> 'cylindree'
ORDER BY t.Cylindree