[GROUP BY/JOIN] Utilisation mots clés

**windmastr26** · 19/08/2010, 00h07

Bonsoir à tous,

Cela fait 2h que je bosse sur une requête qui, en MySQL, me poserait sûrement moins de problèmes. Mais bon voilà, le client a un serveur sous Firebird donc pas trop le choix !

J'ai 2 tables :

Code :

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

1
2
Documents (ID,TITRE,....)
MotsCles(IDDOCUMENT,MOT)

Mon but, c'est de sortir tous les documents (table Documents) répondant aux mots clés (table MotsCles) saisis par l'utilisateur.

Si l'utilisateur saisi "coussin" et "lit", il aura les documents qui ont comme mot clé "coussin" ET "lit" (les deux).

J'ai essayé bon nombre de requêtes mais firebird semble fonctionner différemment au niveau des "group by", car j'ai souvent le message d'erreur suivant :

-SQL error code = -104
-Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)

Pour le moment, voici ma requête :

Code :

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

1
2
3
4
5
SELECT ID,TITRE,AUTEUR 
FROM DOCUMENTS 
LEFT JOIN MOTSCLES ON DOCUMENTS.ID=MOTSCLES.IDDOCUMENT 
WHERE UPPER(MOT) LIKE UPPER('%lit%')
AND UPPER(MOT) LIKE UPPER('%coussin%')

La requête ne me renvoi rien du tout... Et si je mets juste "lit", j'ai souvent des occurrences en double.

Je peine un peu... Alors si une âme charitable passe par là... Dans le cas contraire j'espère y voir un peu plus clair demain.

La nuit porte conseil...

Merci

**makowski** · 19/08/2010, 08h54

Firebird respecte la norme SQL

dans ces cas là donne nous :
- la définition des tables (create table ..)
- un jeu d'essai : insert into ...
pour que l'on y voit clair et que l'on puisse t'aider
et éventuellement la requete telle que tu l'écrivais avec MySQL

**SergioMaster** · 19/08/2010, 09h23

La requête me semble correcte , j'en ai fait une quasiment identique

Code :

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

1
2
3
4
select i.CHANTIER,d.num_intervention,d.REF from INTERVENTIONS i join DETAIL_INTERVENTION d
on d.NUM_INTERVENTION=i.NUM_UNIQUE
where UPPER(d.REF) containing upper('ref')
or UPPER(d.REF) containing upper('kil')

a quelques détails près

je préfère utiliser des alias aux tables , containing plutôt que like '%..%'
et être très explicite sur les données (d.REF au lieu de REF)

après un gros dodo peut-être que

Code :

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

1
2
3
4
5
SELECT D.ID,D.TITRE,D.AUTEUR 
FROM DOCUMENTS D
LEFT JOIN MOTSCLES M ON D.ID=M.IDDOCUMENT 
WHERE UPPER(M.MOT) LIKE UPPER('%lit%')
AND UPPER(M.MOT) LIKE UPPER('%coussin%')

fonctionnera

**windmastr26** · 19/08/2010, 10h22

Hello,

Merci déjà pour vos réponses.

SergioMaster, je ne connaissais pas "CONTAINING" qui est bien plus pratique car évitant l'utilisation des %, merci

Malheureusement, même avec les Alias, le résultat n'est pas plus causant...

Makowski, voilà la définition de mes tables :

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE DOCUMENTS (
   ID INT NOT NULL,
   TITRE VARCHAR(155),
   ...
   PRIMARY KEY (ID));
 
CREATE TABLE MOTSCLES (
   IDDOCUMENT NOT NULL,
   MOT VARCHAR(155) NOT NULL,
   PRIMARY KEY (IDDOCUMENT,MOT));

Il y a un générateur sur Documents, avec un trigger qui incrémente automatiquement l'ID.

Et voici quelques insertions basiques :

Code :

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

1
2
3
4
5
6
7
8
INSERT INTO DOCUMENTS VALUES (1,'Il était une fois',...);
INSERT INTO MOTSCLES VALUES (1,'Fée');
INSERT INTO MOTSCLES VALUES (1,'Chevalier');
INSERT INTO MOTSCLES VALUES (1,'Princesse');
INSERT INTO DOCUMENTS VALUES (2,'Le jardin pour les nuls',...);
INSERT INTO MOTSCLES VALUES (2,'Jardin');
INSERT INTO MOTSCLES VALUES (2,'Jardiner');
INSERT INTO MOTSCLES VALUES (2,'Plantes');

Si j'utilise ma requête avec les mots clés "Jardin" et "Plantes", je devrais avoir le document n°2, et pourtant je n'ai aucun résultat...

Code :

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

1
2
3
4
5
SELECT D.ID,D.TITRE,D.AUTEUR,D."PAGES",M.MOT 
FROM DOCUMENTS D
JOIN MOTSCLES M ON D.ID=M.IDDOCUMENT 
WHERE UPPER(M.MOT) CONTAINING UPPER('Jardin')
AND UPPER(M.MOT) CONTAINING UPPER('Plantes')

Je ne sais pas en revanche ce que j'aurais utilisé sur MySQL. Je parlais juste des requêtes d'une manière générale

Je vais continuer à chercher. Il me reste que 2 jours ça va

**makowski** · 19/08/2010, 11h37

D'abort quelle version de Firebird ?
Quel jeu de caractère et quel ordre de tri pour tes champs ?
quelle idée de faire des UPPER de partout ...

**SergioMaster** · 19/08/2010, 12h22

Si j'utilise ma requête avec les mots clés "Jardin" et "Plantes", je devrais avoir le document n°2, et pourtant je n'ai aucun résultat...

normal , d'après le jeu d'essai
un OR par contre ...

Code :

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

1
2
3
4
5
SELECT D.ID,D.TITRE,M.MOT 
FROM DOCUMENTS D LEFT
JOIN MOTSCLES M ON D.ID=M.IDDOCUMENT 
WHERE UPPER(M.MOT) CONTAINING UPPER('Jardin')
OR UPPER(M.MOT) CONTAINING UPPER('Plantes')

Envoyé par resultat

2 Le jardin pour les nuls Jardin
2 Le jardin pour les nuls Jardiner
2 Le jardin pour les nuls Plantes

**windmastr26** · 19/08/2010, 13h22

Envoyé par makowski

D'abort quelle version de Firebird ?
Quel jeu de caractère et quel ordre de tri pour tes champs ?
quelle idée de faire des UPPER de partout ...

Je suis sous la version 2.1. Jeu de caractère par défaut et aucun ordre de tri pour le moment.

Ensuite pour les UPPER c'est un peu obligé si je ne veux pas que la recherche soit sensible à la case, à moins que tu ai une autre solution : je suis toujours preneur.

Envoyé par SergioMaster

normal , d'après le jeu d'essai
un OR par contre ...

Oui ça me semble normal aussi mais là je sèche un peu... Et si je mets un OR, il ne me retourne pas les enregistrements répondant à tous les critères, mais que ceux répondant à au moins 1 des critères...

**makowski** · 19/08/2010, 14h06

Envoyé par windmastr26

Je suis sous la version 2.1. Jeu de caractère par défaut et aucun ordre de tri pour le moment.

très mauvaise idée
jeu de caractère par défaut = pas de jeux de caractère
utiliser WIN1252, ou ISO8859_1 ou UTF8

Envoyé par windmastr26

Ensuite pour les UPPER c'est un peu obligé si je ne veux pas que la recherche soit sensible à la case, à moins que tu ai une autre solution : je suis toujours preneur.

oui, utiliser les ordres de tri insensibles à la casse

Envoyé par windmastr26

Oui ça me semble normal aussi mais là je sèche un peu... Et si je mets un OR, il ne me retourne pas les enregistrements répondant à tous les critères, mais que ceux répondant à au moins 1 des critères...

là c'est certain, il faut ré écrire ta requête complètement différemment
quelque chose comme ça par exemple :

Code :

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

1
2
3
4
5
6
SELECT DISTINCT D.ID,D.TITRE 
FROM DOCUMENTS D INNER
JOIN MOTSCLES M ON D.ID=M.IDDOCUMENT 
WHERE UPPER(M.MOT) CONTAINING UPPER('Jardin')
AND EXISTS (SELECT 1 FROM MOTSCLES M2
WHERE M2.IDDOCUMENT=M.IDDOCUMENT AND  UPPER(M2.MOT) CONTAINING UPPER('Plantes'))

**makowski** · 19/08/2010, 14h41

Exemple :

Code :

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

1
2
3
4
CREATE TABLE MOTSCLES (
   IDDOCUMENT INTEGER NOT NULL,
   MOT VARCHAR(155) CHARACTER SET ISO8859_1 NOT NULL COLLATE FR_FR_CI_AI,
   PRIMARY KEY (IDDOCUMENT,MOT));

Code :

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

1
2
3
4
5
6
7
INSERT INTO MOTSCLES VALUES (1,'Fée');
INSERT INTO MOTSCLES VALUES (1,'Chevalier');
INSERT INTO MOTSCLES VALUES (1,'Princesse');
INSERT INTO MOTSCLES VALUES (1,'Jardin');
INSERT INTO MOTSCLES VALUES (2,'Jardin');
INSERT INTO MOTSCLES VALUES (2,'Jardiner');
INSERT INTO MOTSCLES VALUES (2,'Plantes');

Code :

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

1
2
3
4
5
6
SELECT DISTINCT D.ID,D.TITRE 
FROM DOCUMENTS D INNER
JOIN MOTSCLES M ON D.ID=M.IDDOCUMENT 
WHERE M.MOT CONTAINING 'JardiN'
AND EXISTS (SELECT 1 FROM MOTSCLES M2
WHERE M2.IDDOCUMENT=M.IDDOCUMENT AND  M2.MOT CONTAINING 'plantes')

Résultat :

Code :

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

2	Le jardin pour les nuls

**windmastr26** · 19/08/2010, 14h43

Merci pour le temps que tu as pris.

Mais cette histoire de COLLATE m'intéresse beaucoup. Donc je suis en train de me renseigner et après je test tout ça.

Merci encore

**windmastr26** · 19/08/2010, 15h32

Bon, bon, bon...

J'ai suivi ton conseil mais j'ai plutôt appliqué le jeu de caractère à la création de la base

Code :

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

DEFAULT CHARACTER SET ISO8859_1

Ensuite pour le COLLATE, si j'ai bien compris ce que j'ai vu, la "collation" va me permettre de définir l'ordre de tri et de comparaison dans les clause "WHERE"...

Seulement, si comme tu me le conseille je l'attribue à la création de la table, cela signifie qu'à chaque recherche il ne prendra pas en compte les majuscules et les accents ?

Si tel est le cas, cela convient très bien pour une recherche "insensible à la casse", mais si l'utilisateur ne coche pas cette option alors je n'aurais pas les résultats escomptés...

Cela signifie-t-il que je dois appliquer la "collation" à la recherche de cette manière ?

Code :

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

WHERE M.MOT COLLATE FR_FR_CI_AI = 'marecherche'

**windmastr26** · 19/08/2010, 16h10

Avec ce que tu m'as donné Makowski, j'ai pu bien avancer. Ainsi la requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT DISTINCT D.ID,D.TITRE, M.MOT
FROM DOCUMENTS D INNER
JOIN MOTSCLES M ON D.ID=M.IDDOCUMENT 
WHERE M.MOT COLLATE FR_FR_CI_AI CONTAINING  'jardin'
AND EXISTS (SELECT 1 FROM MOTSCLES M2
WHERE M2.IDDOCUMENT=M.IDDOCUMENT 
AND  M2.MOT COLLATE FR_FR_CI_AI CONTAINING 'plantes')
AND EXISTS (SELECT 1 FROM MOTSCLES M3
WHERE M3.IDDOCUMENT=M.IDDOCUMENT 
AND  M3.MOT COLLATE FR_FR_CI_AI CONTAINING 'fleurs')

Marche parfaitement et me sort bien les enregistrements qui contiennent tous les mots.

Par contre cela ne signifie-t-il pas, vu l'utilisation de sous-requêtes, que plus l'utilisateur saisira de mots clés, plus lente sera la requête ?

Dans tous les cas, merci à vous 2 pour votre aide inestimable

**makowski** · 19/08/2010, 17h02

Envoyé par windmastr26

Par contre cela ne signifie-t-il pas, vu l'utilisation de sous-requêtes, que plus l'utilisateur saisira de mots clés, plus lente sera la requête ?

oui mais pas tant que ça
parce que EXIST s'arrete dès qu'il trouve une valeur
que tu utilise de toute façon l'index de la clé primaire de MOTSCLES

Code :

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

1
2
PLAN (M2 INDEX (RDB$PRIMARY4))
PLAN SORT (JOIN (D NATURAL, M INDEX (RDB$PRIMARY4)))

ceci dit, tu pourrais peut être éviter CONTAINING et ne faire que LIKE 'monmot%' ou ='monmot'

**windmastr26** · 19/08/2010, 17h27

Envoyé par makowski

ceci dit, tu pourrais peut être éviter CONTAINING et ne faire que LIKE 'monmot%' ou ='monmot'

Tout dépend de la différence de charge du serveur entre l'utilisation de CONTAINING et de LIKE. Cela ne pose pas de problèmes sur mon script de ne pas utiliser CONTAINING, mais cela me permet de ne pas utiliser les %.

**makowski** · 19/08/2010, 17h51

CONTAINING est équivalent en tout point à LIKE %...%
LIKE '...%' et STARTING WITH peuvent utiliser un index
LIKE '%..' et LIKE '%...%' et CONTAINING ne peuvent pas

**windmastr26** · 19/08/2010, 17h53

Ok, et l'utilisation des index étant plus rapide...

Donc vaudrait mieux effectivement remettre les LIKE

[GROUP BY/JOIN] Utilisation mots clés

SQL Firebird

Discussions similaires

Partager

Partager