Jointure sur plusieurs tables

[yuston]

Salut.

Bon, le plus simple, c'est que je vous donne une structure de ma base de données.

TABLE "PAGE STATIQUE"

id (int, primaire) - titre (varchar) - contenu (text) - url (varchar)

TABLE "ARTICLE"

id (int, primaire) - titre (varchar) - contenu (text) - date (time) - url (varchar)

TABLE "EVENEMENT"

id (int, primaire) - titre (varchar)- date (time) - url (varchar)

Vous voyez donc qu'il y a la colonne URL à chaque fois, c'est pour mon URL rewriting en mode seo/user friendly.

J'ai une dernière table, qui est utilisée pour le moteur de recherche interne

TABLE "RECHERCHE"

id (int, primaire) - cle_etrangere (int) - type (varchar) - index (text, fulltext)

Chaque page du site est caractérisée par son type et un id.

La table recherche référence tout le contenu de toutes les pages de mon site, et pour chaque ligne, il prend l'id de la page en question, son type (si c'est un événement ou un article) et un index "alphabétique".

Maintenant, lorsque je fais une recherche sur le site, je fais une requête de ce genre:

SELECT cle_etrangere, type,
MATCH(recherche.index)
AGAINST('UN MOT CLE') AS pertinence
FROM recherche
INNER JOIN XXX ON YYY
WHERE MATCH (recherche.contenu) AGAINST ('UN MOT CLE')
ORDER BY pertinence desc
LIMIT 0,10

Comment faire pour récupérer l'url de la page concernée?

Je vois bien un INNER JOIN à faire mais je vois pas comment je fais pour chaque table (là j'ai mis que 3 tables, mais j'en ai plus....).

[Ernestine]

Salut Yuston,

Tu peux enchaîner des LEFT JOIN sur les différentes tables :

LEFT JOIN
ARTICLE ON ARTICLE.id = RECHERCHE.cle_etrangere
LEFT JOIN
EVENEMENT ON EVENEMENT.id = RECHERCHE.cle_etrangere
etc...

Avec un "LEFT JOIN", s'il n'y a pas de résultat correspondant dans la table jointe, mysql retourne quand même l'enregistrement (contrairement à INNNER JOIN qui s'il n'y a rien de l'autre côté ne renvoie rien, ce qui stoppe la requête en quelque sorte).

[yuston]

Bonsoir Ernestine,

A coup de LEFT JOIN; j'ai autant de colonnes URL en plus que de jointures en plus. Avec l'exemple d'en haut, j'ai donc 3 colonnes en plus :

SELECT recherche.cle_etrangere, recherche.type, article.url,  evenement.url,  page.url, 
MATCH(recherche.index)
AGAINST('un mot clé') AS pertinence
FROM recherche
LEFT JOIN
	article ON article.id = recherche.cle_etrangere
LEFT JOIN
	evenement ON evenement.id = recherche.cle_etrangere
LEFT JOIN
	page ON page.id = recherche.cle_etrangere
WHERE MATCH (recherche.index) AGAINST ('un mot clé')
ORDER BY pertinence desc
LIMIT 0,30

Là j'obtiens donc, par exemple :

cle_etrangere	type 		url 			url 			url 			pertinence
6		article		un-article-cool		NULL			NULL			6
2		evenement	un-article-cool		concert-lorie		NULL			5
8		page		un-autre-article	NULL			mentions		2

Y a des colonnes url qui ne devraient pas se remplir. Comment je fais intervenir la colonne "recherche.type" pour que ça ne va chercher que l'url du type de page concerné?

Par exemple, si j'ai ces enregistrements dans la table recherche :

id		cle_etrangere		type		index
1		1			article		twitter facebook réseau social
2		5			evenement	anniversaire jean luc

Il faudrait par exemple que, lorsqu'un visiteur cherche "twitter", ma requête doit me donner toutes les lignes dont l'index contient twitter. Et me donne l'url correspondant au type de page et à l'id "clé_etrangere"!

En gros, il faut chercher dans la bonne table l'url correspondant à l'id. Et la bonne table est donnée par "type".

Je déteste SQL.

[jcaron]

Si tu utilises le LEFT JOIN, n'oublie pas de vérifier aussi le type retourné.

Tu peux aussi faire un join avec une UNION des différentes tables:

JOIN (SELECT 'article' AS type,id,url FROM article UNION SELECT 'evenement',id,url FROM evenement) s ON s.id=recherche.cle_etrangere AND s.type=recherche.type

Ceci dit, je ne suis pas sûr que le schéma s'adapte parfaitement à une situation où tu aurais vraiment beaucoup de types différents.

Jacques.

[yuston]

Merci à vous deux mais je commence à en souffrir de ces requêtes Je risque d'en ressortir avec des séquelles.

Justement, comment je vérifie le type Jacques? Car j'ai essayé un article ON (article.id = recherche.cle_etrangere AND recherche.type = 'article') par exemple, cela ne semble pas fonctionner :

SELECT recherche.cle_etrangere, recherche.type, article.url,  evenement.url,  page.url, 
MATCH(recherche.index)
AGAINST('un mot clé') AS pertinence
FROM recherche
LEFT JOIN
	article ON (article.id = recherche.cle_etrangere AND recherche.type = 'article')
LEFT JOIN
	evenement ON (evenement.id = recherche.cle_etrangere AND recherche.type = 'evenement')
LEFT JOIN
	page ON (page.id = recherche.cle_etrangere AND recherche.type = 'page')
WHERE MATCH (recherche.index) AGAINST ('un mot clé')
ORDER BY pertinence desc
LIMIT 0,30

Et concernant UNION, je ne vois pas du tout comment faire non plus :@

EDIT:

Ah oui, en fait cela marche, j'ai juste mal recopié un truc plus qu'à en faire un script PHP qui affiche tout ça correctement :/ Le tableau est très grand !

Merci!

EDIT2:

Pour ceux qui se demandent comment j'ai traité toutes les n colonnes URL (en sachant que n-1 colonnes sont NULL), j'ai juste concaténé toutes les colonnes URL en utilisant CONCAT_WS (qui ignore les NULL) et pis voilà, y a peut-être plus propre....

Modifié 9 Décembre 2011 par yuston

[jcaron]

COALESCE est ton ami...

Jacques.

[yuston]

Ou COALESCE effectivement, qui est plus facile que CONCAT_WS

Merci à tous.

Je n'avais jamais fait ce genre de jointure encore. J'espère qu'avec le fulltext ça ne va pas trop coûter en ressource.