Simplifier requêtes avec jointures [Résolu]

[bibos]

Bonjour à tous,

Je tâtonne avec l'utilisation des jointures.

Et aujourd'hui je cherche à simplifier deux requêtes que j'utilise.

Voilà mon cas :

- J'ai une table "articles"
- une table "categories"
- une table "catart" (qui lie les articles aux catégories - un article peut appartenir à plusieurs catégories : deux colonnes : idcat | idart)

Je dois sortir un listing d'articles avec différents champs, notamment toutes les catégories dans lesquelles l'article apparait.
J'ai plusieurs types dans ma table categories :

- marques (idcat=10)
- accessoires (idcat=12)
- telephones (idcat=11)

Pour compliquer l'affaire, ma catégorie "telephones" est elle même liée à la catégorie "marques" : Exemple: un téléphone "N86" est de marque "Nokia" (dans ma table le "N86" a donc pour "idcat" = 11 et pour "idcat2" = 10)

Et moi je dois sortir :

Article1 | Batterie | Nokia | N86
Article1 | Batterie | HTC | Legend
Article2 | Coque | Samsung | Galaxy SII
etc.

A l'heure actuelle j'utilise donc deux requêtes sans doute archaïques, mais qui ont le mérite de fonctionner... mais qui me prennent un max de ressources et de temps d'exécution.

Je fais donc d'abord :

Code :

1
2
3
4
5
6
7
SELECT A.id, A.denom AS ARTICLE, B.denom AS TELEPHONE, D.denom AS MARQUE
FROM afs_article A 
LEFT JOIN afs_catart C ON A.id=C.idart 
LEFT JOIN afs_categorie B ON B.id=C.idcat 
LEFT JOIN afs_categorie D ON B.idcat2=D.id
WHERE B.idcat=11
ORDER BY ARTICLE

Mais je dois encore aller chercher ma catégorie "accessoires", et c'est pour elle que je butte... Donc pour le moment j'ai cette solution - de bourrin - de lancer ma boucle sur ma première requête, et d'aller pour chaque ligne chercher le type d'accessoires d'appartenance :

Code :

1
2
3
4
SELECT K.id AS ACCESSOIRES 
FROM afs_catart J 
LEFT JOIN afs_categorie K ON K.id=J.idcat 
WHERE J.idart=$IDARTICLE AND K.idcat=12

Et donc ma question est évidemment : comment faire ça en une seule requête.

J'espère que j'ai été assez clair et pas trop long !

Merci d'avance pour toute aide !

[punkoff]

bonjour,

Il faut remonter votre condition dans la clause where, sinon vous allez forcer des jointures internes.


Essayez ceci :

Code :

1
2
3
4
5
6
7
8
 
SELECT A.id, A.denom AS ARTICLE, B.denom AS TELEPHONE, D.denom AS MARQUE, e.denom AS accessoire
FROM afs_article A 
LEFT JOIN afs_catart C ON A.id=C.idart 
LEFT JOIN afs_categorie B ON B.id=C.idcat AND  B.idcat=11 
LEFT JOIN afs_categorie D ON B.idcat2=D.id
LEFT OUTER JOIN afs_categorie e ON e.id = c.idcat AND e.idcat = 12
ORDER BY ARTICLE

Concernant les perf, cette requête ramène beacoup d'enregistrement ?
Pour la table afs_catart je déduis que la primary key est le couple {idart, idcat} ?
Est-ce que votre colonne idcat2 de la table afs_categorie est indéxé ?

[CinePhil]

Dans ta première requête, tu fais des jointures externes mais tu mets une condition dans le WHERE qui porte sur une table externe, transformant ainsi la jointure externe en jointure interne.

Lire mon billet de blog pour plus d'explications.

Par contre j'ai du mal à comprendre ton besoin et à deviner la structure de tes tables au vu de la requête.

Tu peux fournir ce qui manque pour qu'on puisse t'aider plus efficacement ?

[bibos]

La vache vous êtes rapide à répondre ! C'est moi qui suis à la bourre du coup !

Donc pour ta requête punkoff, ça me donne une liste trop longue (130 articles au lieu de 125 000), avec pas mal de champs NULL :

ID | Article | Telephone | Marque | Accessoire
1000 	Bluetooth Mono sans fil HTC 	NULL 	NULL 	NULL
1000 	Bluetooth Mono sans fil HTC 	NULL 	NULL 	NULL
1000 	Bluetooth Mono sans fil HTC 	NULL 	NULL 	Bluetooth
1000 	Bluetooth Mono sans fil HTC 	NULL 	NULL 	NULL
1000 	Bluetooth Mono sans fil HTC 	NULL 	NULL 	NULL
1000 	Bluetooth Mono sans fil HTC 	NULL 	NULL 	NULL
1000 	Bluetooth Mono sans fil HTC 	NULL 	NULL 	NULL
1000 	Bluetooth Mono sans fil HTC 	Z6 	MOTOROLA 	NULL
1000 	Bluetooth Mono sans fil HTC 	Z3 	MOTOROLA 	NULL

"Bluetooth Mono sans fil HTC" c'est le nom de l'article.
Et surtout je n'ai jamais à la fois le téléphone, la marque et l'accessoire sur la même ligne.

Oui c'est bien le couple id_art/id_cat le primary key sur afs_catart, et ma colonne idcat2 sur afs_categorie n'est pas indexée...

[punkoff]

fournissez un jeu de donnée test + résutlat attendu + structure comme le suggère CinePhil

[bibos]

Envoyé par CinePhil

Dans ta première requête, tu fais des jointures externes mais tu mets une condition dans le WHERE qui porte sur une table externe, transformant ainsi la jointure externe en jointure interne.

Lire mon billet de blog pour plus d'explications.

Par contre j'ai du mal à comprendre ton besoin et à deviner la structure de tes tables au vu de la requête.

Tu peux fournir ce qui manque pour qu'on puisse t'aider plus efficacement ?

Ok donc ma requête revient à faire :

Code :

1
2
3
4
5
6
SELECT A.id, A.denom AS ARTICLE, B.denom AS TELEPHONE, D.denom AS MARQUE
FROM afs_article A
LEFT JOIN afs_catart C ON A.id = C.idart
INNER JOIN afs_categorie B ON B.id = C.idcat AND B.idcat =11
LEFT JOIN afs_categorie D ON B.idcat2 = D.id
ORDER BY ARTICLE

Mais après je bloque toujours...

J'ai essayé d'ajouter la ligne de punkoff, ce qui me donne le bon nombre de lignes, sans champs NULL... mais avec toujours le même type d'accessoire remonté !

Code :

1
2
3
4
5
6
7
8
SELECT A.id, A.denom AS ARTICLE, B.denom AS TELEPHONE, D.denom AS MARQUE, E.denom AS ACCESSOIRE
FROM afs_article A
LEFT JOIN afs_catart C ON A.id = C.idart
INNER JOIN afs_categorie B ON B.id = C.idcat
AND B.idcat =11
LEFT JOIN afs_categorie D ON B.idcat2 = D.id
LEFT OUTER JOIN afs_categorie E ON E.id = E.idcat AND E.idcat = 12
ORDER BY ARTICLE

--------

id 	ARTICLE 	TELEPHONE 	MARQUE 	ACCESSOIRE
1000 	Bluetooth Mono sans fil HTC 	Iolite T4242 	HTC 	Bluetooth
1000 	Bluetooth Mono sans fil HTC 	6280 	BLACKBERRY 	Bluetooth
1000 	Bluetooth Mono sans fil HTC 	E60 	NOKIA 	Bluetooth
1000 	Bluetooth Mono sans fil HTC 	L600 	SAMSUNG 	Bluetooth

Pour plus de détail sur la structure de mes tables :

afs_article
ID | DENOM

afs_catart
IDCAT | IDART

afs_categorie
ID | DENOM | IDCAT | IDCAT2

[bibos]

Envoyé par bibos

J'ai essayé d'ajouter la ligne de punkoff, ce qui me donne le bon nombre de lignes, sans champs NULL... mais avec toujours le même type d'accessoire remonté !

SELECT A.id, A.denom AS ARTICLE, B.denom AS TELEPHONE, D.denom AS MARQUE, E.denom as ACCESSOIRE
FROM afs_article A
LEFT JOIN afs_catart C ON A.id = C.idart
INNER JOIN afs_categorie B ON B.id = C.idcat
AND B.idcat =11
LEFT JOIN afs_categorie D ON B.idcat2 = D.id
LEFT OUTER JOIN afs_categorie E ON E.id = E.idcat AND E.idcat = 12
ORDER BY ARTICLE

Forcément je me suis planté en mettant E.id=E.idcat au lieu de E.id=C.idcat...

Bref, si je corrige, j'ai des champs null dans la colonne accessoire.

[bibos]

Envoyé par punkoff

fournissez un jeu de donnée test + résutlat attendu + structure comme le suggère CinePhil

Les quelques lignes que j'ai donné ci-dessus suffisent ou non ?
Pour les structures de tables, vous avez besoin de quoi d'autre ?

Pour le résultat attendu, et bien ce serait ça :

id ARTICLE TELEPHONE MARQUE ACCESSOIRE
1000 Bluetooth Mono sans fil HTC T4242 HTC Bluetooth
1000 Bluetooth Mono sans fil HTC 6280 BLACKBERRY Bluetooth
1000 Bluetooth Mono sans fil HTC E60 NOKIA Bluetooth
1000 Bluetooth Mono sans fil HTC L600 SAMSUNG Bluetooth
(...)
69 	Adaptateur allume cigare HTC 	P3300 	HTC 	Divers
69 	Adaptateur allume cigare HTC 	MAX 4G 	HTC 	Divers
69 	Adaptateur allume cigare HTC 	TYTN II 	HTC 	Divers
90 	Adaptateur allume cigare Nokia 	6310 	NOKIA 	Divers
89 	Adaptateur allume cigare Nokia 	X3 	NOKIA 	Divers

[bibos]

Si ça peut simplifier les choses, je n'ai pas forcément besoin du DENOM de la catégorie ACCESSOIRE.
Je peux récupérer simplement son ID, dans la table afs_catart.

Mais bon... je sais pas si ça simplifie les choses...

[punkoff]

Envoyé par bibos

Les quelques lignes que j'ai donné ci-dessus suffisent ou non ?
Pour les structures de tables, vous avez besoin de quoi d'autre ?

Non, pas suffisant, car votre résultat est incohérant par rapport à votre présentation.

Dans votre 1er poste, j'ai simplement repris votre 2eme requête pour la joindre avec votre 1ere requête.

Donc si la requête que je vous ai posté ne retourne que des valeurs null pour votre accessoire cela veut dire que votre 2eme requête initial ne renvoie rien.

[bibos]

Envoyé par punkoff

Donc si la requête que je vous ai posté ne retourne que des valeurs null pour votre accessoire cela veut dire que votre 2eme requête initial ne renvoie rien.

En effet........
Bon et bien on peut oublier cette deuxième requête.

Bon on va reprendre autrement, si je fais (j'ai changé toutes mes jointures en internes... c'est ce qu'il me faut en fait non ?):

Code :

1
2
3
4
5
6
SELECT A.id, A.denom AS ARTICLE, B.denom AS TELEPHONE, D.denom AS MARQUE
FROM afs_article A
INNER JOIN afs_catart C ON A.id = C.idart
INNER JOIN afs_categorie B ON B.id = C.idcat AND B.idcat =11
INNER JOIN afs_categorie D ON B.idcat2 = D.id
ORDER BY ARTICLE

J'ai ce qu'il me faut, sauf qu'il me manque la colonne accessoire :

id 	ARTICLE 	TELEPHONE 	MARQUE
1000 	Bluetooth Mono sans fil HTC 	Desire Z 	HTC
1000 	Bluetooth Mono sans fil HTC 	W980i 	SONY ERICSSON
1000 	Bluetooth Mono sans fil HTC 	U900 	LG
(...)
1133 	Vitre tactile Samsung S5600 	Player Star 	SAMSUNG
1501 	vitre tactile samsung s8000 je 	S8000 jet 	SAMSUNG
1515 	Vitre tactile sony  x10 	X10 	SONY ERICSSON
1517 	Vitre tactile sony  x10 mini 	X10 mini 	SONY ERICSSON

Et si je fais :

Code :

1
2
3
4
5
SELECT A.id, A.denom AS ARTICLE, E.denom AS ACCESSOIRE
FROM afs_article A
INNER JOIN afs_catart C ON A.id = C.idart
INNER JOIN afs_categorie E ON E.id = C.idcat AND E.idcat =12
ORDER BY ARTICLE

J'ai les correspondances ARTICLE / ACCESSOIRE, sauf que mon listing est limité au nombre d'articles (1900), et non pas multiplié pour chaque correspondance avec un téléphone (125 000).

id 	ARTICLE 	ACCESSOIRE
1000 	Bluetooth Mono sans fil HTC 	Bluetooth
1848 	lcd complet HTC Desire HD 	Pieces Detachees
1894 	Vitre tactile Htc wildfire 	Pieces Detachees
75 	Accès aux ports et au dock 	Housses

[bibos]

En fait, je ne sais pas si ça va aider encore, mais c'est ce genre de requête que j'aimerais faire... sauf que dans mon cas ça ne me retourne aucun enregistrement...

Code :

1
2
3
4
5
6
7
SELECT A.id, A.denom AS ARTICLE, B.denom AS TELEPHONE, D.denom AS MARQUE, E.denom AS ACCESSOIRE
FROM afs_article A
JOIN afs_catart C ON A.id = C.idart
JOIN afs_categorie B ON B.id = C.idcat AND B.idcat=11
JOIN afs_categorie D ON B.idcat2 = D.id
JOIN afs_categorie E ON E.id = C.idcat AND E.idcat=12
ORDER BY ARTICLE

[Waldar]

Vous n'avez toujours pas donné, table par table, les données sources et ce que vous attendez en sortie, comme demandé par Cinephil et Punkoff.

On peut continuer à jouer aux devinettes longtemps.

[bibos]

Et bien alors c'est moi qui ne comprend pas ce qu'il faut...

Ce que j'attends en sortie, je l'ai copié plus haut :

Envoyé par bibos

Pour le résultat attendu, et bien ce serait ça :

id 	ARTICLE 					TELEPHONE MARQUE 		ACCESSOIRE
1000 	Bluetooth Mono sans fil HTC 		T4242 	HTC 			Bluetooth
1000 	Bluetooth Mono sans fil HTC 		6280 		BLACKBERRY 	Bluetooth
1000 	Bluetooth Mono sans fil HTC 		E60 		NOKIA 		Bluetooth
1000 	Bluetooth Mono sans fil HTC 		L600 		SAMSUNG 		Bluetooth
(...)
69	Adaptateur allume cigare HTC		P3300	HTC 			Divers
69	Adaptateur allume cigare HTC		MAX 4G 	HTC 			Divers
69	Adaptateur allume cigare HTC		TYTN II 	HTC 			Divers
90	Adaptateur allume cigare Nokia	6310 		NOKIA 		Divers
89	Adaptateur allume cigare Nokia	X3 		NOKIA 	Divers

Et j'attends 125 000 lignes comme ça... (tous mes articles X toutes leurs compatibilités par téléphone)
Qu'est-ce qu'il faut comme précisions supplémentaires ?

Quant à mes données sources, on ne parle pas de la stucture, mais du contenu alors ?

Donc dans ma table afs_article, j'ai ça :

Code :

1
2
3
4
5
6
id 	denom
2 	Batterie d'origine NOKIA BLB-2
23 	Batterie Nokia 6610i,7210,3200
28 	Batterie Nokia 3606, N76, 7500
29 	Chargeur Secteur Nokia ACP-12E
etc. = 1928 lignes

Ma table afs_catart, j'ai ça :

Code :

1
2
3
4
5
6
7
8
idcat 	idart
1 	30
1 	51
1 	52
(...)
1188 	2251
1188 	2252
etc. = 130 310 lignes

Ma table afs_categorie :

Code :

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
id 	denom 			idcat 	idcat2
1 	MOTOROLA 		10 	NULL
3 	NOKIA 			10 	NULL
4 	Batteries téléphones 	12 	NULL
8 	Chargeurs secteur 	12 	NULL
9 	Kit_Piétons 		12 	NULL
10 	Housses 		12 	NULL
11 	Façades 		12 	NULL
12 	Bluetooth 		12 	NULL
13 	Pieces Detachees 	12 	NULL
14 	Divers 			12 	NULL
15 	LG 			10 	NULL
16 	SAMSUNG 		10 	NULL
17 	SONY ERICSSON 		10 	NULL
18 	HTC 			10 	NULL
27 	Chargeur voiture 	12 	NULL
28 	A910 			11 	1
29 	C139 			11 	1
30 	F3 			11 	1
31 	K1 			11 	1
32 	L6 			11 	1
33 	L7 			11 	1
34 	L9 			11 	1
35 	M930 			11 	1
etc. = 1117 lignes

Qu'est-ce que je peux donner de plus comme infos ?

[Rei Ichido]

Envoyé par bibos

A l'heure actuelle j'utilise donc deux requêtes sans doute archaïques, mais qui ont le mérite de fonctionner... mais qui me prennent un max de ressources et de temps d'exécution.

Un max à quel point ? Avez-vous des index sur les tables ? Les requêtes me semblent correctes, les volumétries sont faibles, ça devrait tourner tout seul si c'est bien indexé.

EDIT :
Votre requête ne retourne rien parce que de la table C, vous tentez une jointure simultanée avec deux fois la même table sur la même colonne. Aucune ligne ne peut a priori avoir un idcat qui corresponde à simultanément un id de categorie ayant un idcat de 11 et un autre ayant un idcat de 12. Je me permet d'ailleurs de signaler que le nommage des colonnes prête hautement à confusion !
Quelque chose du genre (avant de regarder à faire de l'optimisation) vous renvoie-t-il des résultats corrects :

Code :

1
2
3
4
5
6
7
8
9
10
 
SELECT A.id, A.denom AS ARTICLE, B.denom AS TELEPHONE, D.denom AS MARQUE, E.denom AS ACCESSOIRE
FROM afs_article A
JOIN afs_catart C ON A.id = C.idart
JOIN afs_categorie B ON B.id = C.idcat AND B.idcat=11
JOIN afs_categorie D ON B.idcat2 = D.id
-- là il faut refaire une jointure avec catart
JOIN afs_catart C2 ON A.id = C2.idart
JOIN afs_categorie E ON E.id = C2.idcat AND E.idcat=12
ORDER BY ARTICLE

[Waldar]

Envoyé par bibos

Quant à mes données sources, on ne parle pas de la stucture, mais du contenu alors ?

Les deux !

Envoyé par bibos

Qu'est-ce que je peux donner de plus comme infos ?

Pas en plus, plutôt en moins et en mieux.
Un exemple cohérent entre quelques données sources pertinentes, vérifiables à l'œil et le résultat attendu.
Si vous me parlez de l'article 2 dans la table article mais que je ne le retrouve pas dans la table catart, on ne peut rien en faire.

Fournissez un jeu de test complet comme prévu dans les règles du forum, §5 :
http://www.developpez.net/forums/a69...gage-sql-lire/

Notez que les données en elle-même n'ont pas à être forcément vos vraies données, il faut juste que la logique soit la même afin que vous puissiez derrière résoudre votre problème.

[bibos]

Envoyé par Rei Ichido

Un max à quel point ? Avez-vous des index sur les tables ? Les requêtes me semblent correctes, les volumétries sont faibles, ça devrait tourner tout seul si c'est bien indexé.

Ca met environ 140 secondes à s'exécuter quand même...
Et ça utilise plus de 800Mo de mémoire...

[Rei Ichido]

J'ai édité mon post plus haut pour suggérer une requête.

Sur la question des perfs, il ya des index ?

[bibos]

Envoyé par Waldar

Les deux !


Pas en plus, plutôt en moins et en mieux.
Un exemple cohérent entre quelques données sources pertinentes, vérifiables à l'œil et le résultat attendu.
Si vous me parlez de l'article 2 dans la table article mais que je ne le retrouve pas dans la table catart, on ne peut rien en faire.

Ok alors, on repart...

afs_article

Code :

1
2
3
4
ID	DENOM
1	Coque violette
2	Housse verte

afs_categorie

Code :

1
2
3
4
5
6
7
8
9
ID	DENOM		IDCAT	IDCAT2
5	MOTOROLA	10		NULL
10	NOKIA		10		NULL
15	Housses		12		NULL
20	Coques		12		NULL
25	X23		11		5
30	Z8		11		10
35	Z6		11		10

Motorola et Nokia sont des MARQUES (10), Housses et Coques sont des ACCESSOIRES (12), le reste ce sont des TELEPHONES (11).
Le téléphone X23 appartient à la marque Motorola (idcat2).
Les téléphones Z8 et Z6 appartiennent à la marque Nokia.

afs_catart

Code :

1
2
3
4
5
6
7
8
9
10
11
IDART	IDCAT
1	5
1	25
1	20
2	5
2	10
2	15
2	25
2	30
2	35

L'article "Coque Violette" est compatible : "Motorola", "Coques" et "X23".
L'article "Housse verte" est compatible avec tout sauf avec la catégorie "Coques".

Ce que je souhaite obtenir :

Code :

1
2
3
4
5
6
ID	DENOM		TELEPHONE		MARQUE		ACCESSOIRE
1	Coque Violette	X23			MOTOROLA	Coques
1	Housse verte	X23			MOTOROLA	Housses
1	Housse verte	Z8			NOKIA		Housses
1	Housse verte	Z6			NOKIA		Housses

[bibos]

Envoyé par Rei Ichido

Votre requête ne retourne rien parce que de la table C, vous tentez une jointure simultanée avec deux fois la même table sur la même colonne. Aucune ligne ne peut a priori avoir un idcat qui corresponde à simultanément un id de categorie ayant un idcat de 11 et un autre ayant un idcat de 12. Je me permet d'ailleurs de signaler que le nommage des colonnes prête hautement à confusion !
Quelque chose du genre (avant de regarder à faire de l'optimisation) vous renvoie-t-il des résultats corrects :

Code :

J'ai tout compris, et ça marche impeccable... Il fallait refaire une jointure sur catart!

Et donc on peut encore optimiser cette requête ?
Parce que là elle est traitée en 0.0001 sec !