Recherche de champs potentiellement non existants dans plusieurs tables

**Schwy** · 26/09/2013, 14h18

Bonjour,

Je m'occupe actuellement du moteur de recherche d'une plateforme de recrutement. Les candidats peuvent renseigner leurs informations : compétences, études suivies, expérience, et ces informations sont stockées dans des tables différentes. Les candidats sont identifiés par un id_membre, stocké dans une table membre. La table cv contient le titre du poste recherché par le candidat et toutes tables contiennent une colonne id_membre, clé primaire du candidat.

Je développe une fonction de recherche par mots-clefs, qui recherche un mot dans toutes les tables. J'ai donc la requête SQL suivante, qui ne retourne que l'ID du candidat :

SELECT DISTINCT(C.id)
FROM cv C, membres M, cv_competence CO, cv_experience EX, cv_etudes ET
WHERE M.id=C.id_membre

AND ( (LOWER(EX.titre)

LIKE '%mot_cle%'

OR LOWER(EX.entreprise)

LIKE '%mot_cle%'

OR LOWER(EX.description)

LIKE '%mot_cle%'

OR LOWER(ET.titre)

LIKE '%mot_cle%'

OR LOWER(ET.ecole)

LIKE '%mot_cle%'

OR LOWER(ET.description)

LIKE '%mot_cle%'

OR LOWER(ET.ville)

LIKE '%mot_cle%'

OR LOWER(CO.titre)

LIKE '%mot_cle%'

OR LOWER(C.titre)

LIKE '%mot_cle%'

OR LOWER(CO.description)

LIKE '%mot_cle%') )

AND M.id=EX.id_membre
AND M.id=CO.id_membre
AND M.id=ET.id_membre

Cependant, il m'arrive d'avoir des requêtes ne retournant rien alors qu'elles devraient.
J'ai identifié ce problème comme venant du fait que tous les candidats ne remplissent pas toutes les tables. Par exemple, ils vont remplir leur études mais pas leurs compétences. A ce moment-là je pense que c'est la partie de la requête AND M.id=CO.id_membre qui pose problème (puisqu'il n'existe pas d'id_membre correspondant à M.id dans la table des compétences)

J'ai alors essayé de faire la requête suivante, qui prendrait en compte le fait qu'un champ ne soit pas renseigné :

SELECT DISTINCT(C.id)
FROM cv C, membres M, cv_competence CO, cv_experience EX, cv_etudes ET
WHERE

(((LOWER(EX.titre)

LIKE '%php%') OR (M.id=EX.id_membre))

OR ((LOWER(EX.entreprise)

LIKE '%php%') OR (M.id=EX.id_membre))

OR ((LOWER(EX.description)

LIKE '%php%') OR (M.id=EX.id_membre))

OR ((LOWER(ET.titre)

LIKE '%php%') OR (M.id=ET.id_membre))

OR ((LOWER(ET.ecole)

LIKE '%php%') OR (M.id=ET.id_membre))

OR ((LOWER(ET.description)

LIKE '%php%') OR (M.id=ET.id_membre))

OR ((LOWER(ET.ville)

LIKE '%php%') OR (M.id=ET.id_membre))

OR ((LOWER(CO.titre)

LIKE '%php%') OR (M.id=CO.id_membre))

OR ((LOWER(C.titre)

LIKE '%php%') OR (M.id=C.id_membre))

OR ((LOWER(CO.description)

LIKE '%php%') OR (M.id=CO.id_membre)))

Mais cette dernière ne charge pas quand je la teste au préalable sur phpMyAdmin, le site bug et ne répond plus après avoir demandé l’exécution de la requête. Je pense donc que ma requête pose problème, mais je n'arrive pas à savoir en quoi !

En résumé : je souhaite rechercher dans plusieurs champs dans différentes tables une expression pour un moteur de recherche. Seulement il arrive que certains champs n'existent pas, ce qui a pour effet d'altérer les résultats de la recherche.

Je m'excuse si mon message est trop long, mais peut-être cela aidera plus à la compréhension qu'un court message évasif !

Je vous remercie par avance pour toute aide apportée !

edit : mince je n'avais pas vu qu'il y avait une partie du forum dédiée aux problèmes de requêtes désolé !

**punkoff** · 26/09/2013, 14h24

bonjour,

A chaque besoin sa techno et un sgbd n'est pas ce qu'il y a de mieux pour ce genre de besoin.

Tournez vous vers des techno comme Sphinxe, Solr (lucene), ... qui répondront beaucoup mieux à ce genre de requete.

Bref, si vous etes bridé au niveau de la solution, la seule approche viable sera avec cette structure :

Code :

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

1
2
3
4
5
 
select id from table1 where ma_condition
union 
select id from table2 where ma_condition
...

**Schwy** · 26/09/2013, 14h41

Bonjour,

Je vous remercie pour votre réponse. Etant en effet bridé au niveau de vos propositions, je me tourne vers votre solution et teste avec l'union de ce pas.

Je vous tiens au courant,

Merci.

**Schwy** · 26/09/2013, 14h51

Je viens de tester avec UNION et le mot-clef "php". La requête est encore boudée par phpMyAdmin, de la même façon qu'elle l'était précédemment. J'ai testé la requête suivante :

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
SELECT DISTINCT(C.id)
FROM cv C, membres M
WHERE
 ((		LOWER(C.titre)		LIKE '%php%') OR (M.id=C.id_membre))
AND M.publier = '1'
UNION
SELECT DISTINCT(C.id)
FROM cv C, membres M, cv_competence CO
WHERE
 (((	LOWER(CO.titre) 		LIKE '%php%') OR (M.id=CO.id_membre))
	OR ((	LOWER(CO.description) 	LIKE '%php%') OR (M.id=CO.id_membre)))
AND M.publier = '1'
UNION
SELECT DISTINCT(C.id)
FROM cv C, membres M, cv_experience EX
WHERE
 (((	 LOWER(EX.titre) 		LIKE '%php%') OR (M.id=EX.id_membre))
	OR ((	LOWER(EX.entreprise)	LIKE '%php%') OR (M.id=EX.id_membre))
	OR ((	LOWER(EX.description)	LIKE '%php%') OR (M.id=EX.id_membre)))
AND M.publier = '1'
UNION
SELECT DISTINCT(C.id)
FROM cv C, membres M, cv_etudes ET
WHERE
 (((	LOWER(ET.titre) 		LIKE '%php%') OR (M.id=ET.id_membre))
	OR ((	LOWER(ET.ecole)		LIKE '%php%') OR (M.id=ET.id_membre))
	OR ((	LOWER(ET.description) 	LIKE '%php%') OR (M.id=ET.id_membre))
	OR ((	LOWER(ET.ville) 		LIKE '%php%') OR (M.id=ET.id_membre)))
AND M.publier = '1'

Savez-vous à quoi cela peut-il être dû ?

Merci par avance.

EDIT : Je vais essayer en partant de ma première requête plutôt. Je vous tiens au courant.

**punkoff** · 26/09/2013, 15h02

y a plein de jointure inutile ...

le principe de l'union est de dégager les jointures foireuse et les conditions à rallonge avec les OR qui n'ont pas lieu d'être

Bref prenez le temps de comprendre ce que vous essayez de faire.

**Schwy** · 26/09/2013, 15h06

Voilà je suis reparti de ma première requête en utilisant les union et ça marche !

Le code complet pour les intéréssés, au cas où :

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
SELECT DISTINCT (
C.id
)
FROM cv C, membres M, cv_competence CO
WHERE M.id = C.id_membre
AND (
LOWER( CO.titre ) LIKE '%sap%'
OR LOWER( C.titre ) LIKE '%sap%'
OR LOWER( CO.description ) LIKE '%sap%'
)
AND M.id = CO.id_membre
AND M.publier = '1'
UNION
SELECT DISTINCT (
C.id
)
FROM cv C, membres M, cv_experience EX
WHERE M.id = C.id_membre
AND (
LOWER( EX.titre ) LIKE '%sap%'
OR LOWER( EX.entreprise ) LIKE '%sap%'
OR LOWER( EX.description ) LIKE '%sap%'
)
AND M.id = EX.id_membre
AND M.publier = '1'
UNION
SELECT DISTINCT (
C.id
)
FROM cv C, membres M, cv_etudes ET
WHERE M.id = C.id_membre
AND (
LOWER( ET.titre ) LIKE '%sap%'
OR LOWER( ET.ecole ) LIKE '%sap%'
OR LOWER( ET.description ) LIKE '%sap%'
OR LOWER( ET.ville ) LIKE '%sap%'
)
AND M.id = ET.id_membre
AND M.publier = '1'
UNION
SELECT DISTINCT (
C.id
)
FROM cv C, membres M
WHERE M.id = C.id_membre
AND (
LOWER( C.titre ) LIKE '%sap%'
)
AND M.publier = '1'

Ma requête est peut-être lourde mais quand j'ai du mal je préfère procéder en détaillant tout (quitte à faire des trucs inutiles) pour optimiser une fois que je suis sûr que ça marche !

Merci pour votre aide, punkoff.

Bonne journée !

**punkoff** · 26/09/2013, 15h21

c'est mieux mais il y a encore des jointures inutiles sur la table des cv.

**Schwy** · 26/09/2013, 15h27

Pouvez-vous s'il vous plaît m'indiquer précisément à quel endroit ?

**punkoff** · 26/09/2013, 15h33

Code :

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

1
2
3
4
5
6
7
8
9
SELECT DISTINCT C.id
FROM cv C, membres M, cv_competence CO
WHERE M.id = C.id_membre
AND (LOWER( CO.titre ) LIKE '%sap%'
OR LOWER( C.titre ) LIKE '%sap%'
OR LOWER( CO.description ) LIKE '%sap%'
)
AND M.id = CO.id_membre
AND M.publier = '1'

La jointure sur la table CV dans ce cas là par exemple, vous sert à quelque chose ?

la condition LOWER( C.titre ) n'a rien à faire ici.

**Schwy** · 26/09/2013, 15h37

Effectivement elle m'a échappé, je n'avais nullement l'intention de la laisser là.

Merci !

**Schwy** · 27/09/2013, 15h51

Bonjour,

Je reviens vers vous car en fait la construction de la requête avec les UNION ne me convient pas (je ne m'en sors pas dans mon code php, je voudrais juste avoir une clause WHERE à ajouter, même si elle est moche, plutôt que des UNION qui n'en finissent pas). Je voudrai donc retourner vers une requête unique n'étant pas la jonction de plusieurs requêtes.

Quand j'essaye ma requête, phpMyAdmin bug et je suis obligé de fermer mon navigateur pour pouvoir l'utiliser de nouveau, et pareil pour le site sur lequel je travaille : le site ne répond plus jusqu'à ce que je ferme mon navigateur en entier.

La requête faisant défaut est la suivante :

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
SELECT DISTINCT (C.id)
FROM cv_experience EX, cv_etudes ET, cv_competence CO, cv C, membres M
WHERE
	((LOWER(EX.titre) 		LIKE '%mot_cle%' OR
	LOWER(EX.entreprise) 	LIKE '%mot_cle%' OR
	LOWER(EX.description) 	LIKE '%mot_cle%')
	AND M.id=C.id_membre
	AND M.id=EX.id_membre)
OR
	((LOWER(ET.titre) 		LIKE '%mot_cle%' OR
	LOWER(ET.ecole) 		LIKE '%mot_cle%' OR
	LOWER(ET.description) 	LIKE '%mot_cle%' OR
	LOWER(ET.ville) 		LIKE '%mot_cle%')
	AND M.id=C.id_membre
	AND M.id=ET.id_membre)
OR
	((LOWER(CO.titre) 		LIKE '%mot_cle%' OR 
	LOWER(CO.description) 	LIKE '%mot_cle%')
	AND M.id=C.id_membre
	AND M.id=CO .id_membre)
OR
	(LOWER(C.titre) 			LIKE '%mot_cle%'
	AND M.id=C.id_membre)

Pouvez-vous m'indiquer pourquoi cette requête fait tout crasher quand exécutée ?

Merci !

**punkoff** · 27/09/2013, 16h07

votre requete force des jointures cartésiennes.

L'approche n'est pas bonne.

Rajouter une conditions where est simple, décomposez votre requete dans différente string.
Puis reconstruisez-la

**Schwy** · 27/09/2013, 16h55

Merci de votre réponse. Je ne suis pas une brute du SQL donc je me suis renseigné pour essayer de comprendre au mieux votre dernier message.

Si j'ai bien compris, ce que vous appelez jointures cartésiennes sont les parties de code telles que :

Code :

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

1
2
AND M.id=C.id_membre
AND M.id=EX.id_membre

J'ai décomposé ma requête en plusieurs requêtes différentes, par exemple :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT DISTINCT (C.id)
FROM cv_etudes ET, cv C, membres M
WHERE
	((LOWER(ET.titre) 		LIKE '%mot_cle%' OR
	LOWER(ET.ecole) 		LIKE '%mot_cle%' OR
	LOWER(ET.description) 	LIKE '%mot_cle%' OR
	LOWER(ET.ville) 		LIKE '%mot_cle%')
AND M.id=C.id_membre
AND M.id=ET.id_membre)

Les requêtes marchent séparément, mais je ne peux pas les concaténer, le FROM posant problème, du moins je ne vois pas comment faire (si c'est bien ce que vous entendiez par une reconstruction). De toute façon, si je reconstruis la requête "à la main", ça crash.

Je ne suis donc pas sur de bien comprendre l'approche a adapter. Avez-vous un exemple à me donner, ou une documentation à lire ?

Merci pour votre aide quoi qu'il en soit

**punkoff** · 27/09/2013, 17h10

en faisant toutes les jointures , si un cancdidat à :
- 10 experiences pro
- 3 études
- 2 cv
- 5 compétence

on se retrouve avec : 10*3*2*5 = 300 lignes pour un candidat.

si vous ne voulez pas faire d'union, qui est la seule solution potable pour votre cas, il vous restera les tests d'existances (mot clef EXISTS).

Il faut partir de la table membre et vérifier, pour chaque table, l'existance ou non de vos conditions de recherche.

**Schwy** · 30/09/2013, 11h28

Bonjour,

Une fois de plus merci pour votre réponse. Je dois mal m'y prendre pour expliquer vu ce que vous me dites, puisque je souhaiterai que si un candidat a :
- 10 experiences pro
- 3 études
- 2 cv
- 5 compétence

On se retrouve avec une ligne, contenant uniquement l'id du candidat ! NB : un candidat ne peux avoir qu'un CV. J'en déduis donc que je m'y prends mal (je pensais que ça allait pourtant, puisque je fais un SELECT DISTINCT (C.id).

Je ne comprends donc toujours pas pourquoi ma requête ne marche pas, puisqu'elle ne devrait pas retourner autant de résultats. Pouvez-vous m'éclairer ?

**punkoff** · 30/09/2013, 11h57

bah au final vous n'aurez qu'un id effectivement.

Mais il faut traiter les données à un moment ou un autre, et la MySql déraille dès que les requetes deviennent complexe.

Bref, dans tous les cas, quelque soit le sgbd l'approche avec l'union est la meilleur méthode.

Si vous voulez implémenter une autre manière de faire, je vous laisse à vos tests

**Schwy** · 30/09/2013, 12h10

Ok donc vous confirmez que la requête dans le post #11 ne fonctionne pas et fait tout crasher uniquement parce qu'elle est trop complexe et que MySql ne suit pas ?

**Schwy** · 02/10/2013, 17h49

Bonjour,

J'ai finalement réussi à m'en sortir avec des UNION

, ce qui me donne la requête suivante :

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
SELECT DISTINCT (C.id), C.titre, M.date_creation, M.nom, M.ville, M.code_postal
FROM cv C, membres M
 
WHERE 
(C.id)
IN
(
SELECT DISTINCT C.id
FROM cv C, membres M, cv_competence CO
WHERE M.id = C.id_membre
AND ( LOWER( CO.titre ) LIKE '%mot_cle%'
OR LOWER( CO.description ) LIKE '%mot_cle%')
AND M.id = CO.id_membre AND M.publier = '1'
 
UNION
SELECT DISTINCT C.id
FROM cv C, membres M, cv_competence CO
WHERE M.id = C.id_membre
AND ( LOWER( CO.titre ) LIKE '%mot_cle%'
OR LOWER( CO.description ) LIKE '%mot_cle%')
AND M.id = CO.id_membre AND M.publier = '1'
 
UNION
SELECT DISTINCT C.id
FROM cv C, membres M, cv_experience EX
WHERE M.id = C.id_membre
AND (LOWER( EX.titre ) LIKE '%mot_cle%'
OR LOWER( EX.entreprise ) LIKE '%mot_cle%'
OR LOWER( EX.description ) LIKE '%mot_cle%' )
AND M.id = EX.id_membre AND M.publier = '1'
 
UNION
SELECT DISTINCT (C.id)
FROM cv C, membres M
WHERE M.id = C.id_membre
AND (LOWER( C.titre ) LIKE '%mot_cle%' )
AND M.publier = '1')
 
AND M.id = C.id_membre
ORDER BY M.date_creation DESC,C.titre ASC LIMIT 0,5

Est-ce normal que j'aie un temps de réponse assez long (jusqu’à plus de dix secondes) ?
Y a-t-il une façon d'optimiser cela ?

Merci,

**punkoff** · 02/10/2013, 21h31

Envoyé par punkoff

b

A chaque besoin sa techno et un sgbd n'est pas ce qu'il y a de mieux pour ce genre de besoin.

Tournez vous vers des techno comme Sphinxe, Solr (lucene), ... qui répondront beaucoup mieux à ce genre de requete.

[/code]

Les like %valeur% force des scananges de table systématiques.

La seule "optimisation" possible c'est d'avoir des index sur les colonnes de jointures.

(regardez sinon les recherche full text de mysql ca devrait tourner si vous n'avez pas beaucoup de donnée)