[GROUP BY/JOIN] Utilisation mots clés

Version imprimable

19/08/2010, 00h07
windmastr26

[GROUP BY/JOIN] Utilisation mots clés
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:

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 :

Citation:

-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:

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 :ccool:
19/08/2010, 08h54
makowski

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

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

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:

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

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:

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:

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:

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 8O

19/08/2010, 11h37
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 ...
19/08/2010, 12h22
SergioMaster
Citation:

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:

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')
Citation:

Envoyé par resultat

2 Le jardin pour les nuls Jardin
2 Le jardin pour les nuls Jardiner
2 Le jardin pour les nuls Plantes
19/08/2010, 13h22
windmastr26

Citation:

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.

Citation:

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... :calim2:

Citation:

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

Citation:

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

Citation:

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... :calim2:

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

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'))

Exemple :

Code:

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:

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:

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:

2 Le jardin pour les nuls

19/08/2010, 14h43
windmastr26

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 :ccool:
19/08/2010, 15h32
windmastr26

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:

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:

WHERE M.MOT COLLATE FR_FR_CI_AI = 'marecherche'

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

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 :ccool:

19/08/2010, 17h02
makowski
Citation:

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:

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'
19/08/2010, 17h27
windmastr26

Citation:

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 %.
19/08/2010, 17h51
makowski

CONTAINING est équivalent en tout point à LIKE %...%
LIKE '...%' et STARTING WITH peuvent utiliser un index
LIKE '%..' et LIKE '%...%' et CONTAINING ne peuvent pas
19/08/2010, 17h53
windmastr26

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

Donc vaudrait mieux effectivement remettre les LIKE :mrgreen: