Regex, sélection d'un champ contenant x mots parmi une liste définie [Résolu]

[Xunil]

Bonsoir,

J'ai une liste de mots définie, avec REGEXP, je voudrais sélectionner les lignes de ma table qui contiennent au moins x mots parmi cette liste et ceci peu importe le nombre d'occurences de chaque mot pourvu qu'il y soit au moins une fois, et ceci dans une seule regex(pas autant de regex qu'il n'y a de combinaisons de mots)

Par exemple, j'ai une liste "a, b, c, d, e", je voudrais que si mon champ contient:

(a et b et c et d) ou (a et b et c et e) ou (a et b et d et e), etc...
Donc 4 termes distinct parmi cette liste, que la ligne soit sélectionnée.

Par contre si mon champ contenait "a b a c", celle-ci ne devrait pas être sélectionnée, puisque il n'y a que 3 valeurs différentes.

Je sais pas si je suis bien clair

J'ai tenté plein de chose, mais pas de résultats corrects à chaque fois.

J'ai également un problème avec les caractères qui ne sont pas de mots que je n'arrive pas à intégrer dans une classe de caractères, mais ce sera pour plus tard

[Antoun]

pourquoi utiliser des REGEXP ? est-ce que, LIKE ou FIND_IN_SET ne te suffiraient pas ?

Je te propose deux solutions que j'illustre avec REGEXP, mais qui sont adaptables à LIKE ou FIND_IN_SET.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
-- solution "bricolage booléen"
SELECT champ
FROM table_textes
WHERE (champ REGEXP 'a') 
+ (champ REGEXP 'b') 
+ (champ REGEXP 'c') 
+ (champ REGEXP 'd') 
+ (champ REGEXP 'e') >= 4
 
-- solution avec table Thesaurus contenant tes mots-clés possibles
SELECT champ
FROM table_textes Tx
  INNER JOIN Thesarus Th ON Tx.champ REGEXP Th.motcle
WHERE  Th.motcle IN ('a', 'b', 'c', 'd', 'e')
GROUP BY champ 
HAVING COUNT(*) >= 4

EDIT : j'ai rajouté des parenthèses dans mon "bricolage boolén"

[Xunil]

Merci, mais le problème avec LIKE, c'est qu'un mot peut-être contenu dans un autre.

Je pourrais mettre des espaces, du genre LIKE '% clef %'
Mais pour un mot en début/fin de chaine, ça poserait problème, et également avec les caractères de ponctuations.

En fait, le but est de pouvoir approfondir une recherche en cas de résultat nul après une recherche en FULLTEXT, et également sur des champs en CHAR ou VARCHAR.

Et donc je souhaite utiliser ceci afin d'essayer de faire un classement par pertinence, donc si l'utilisateur indique 4 mots, que les résultats retournés soit classés.

1- champs contenant les 4 mots (pas dur)
2- champs contenant 3 mots sur les 4
3- champs contenant 2 mots sur les 4
4- champs contenant 1 des 4 mots

C'est sans doute plus clair

[Antoun]

Envoyé par Xunil

En fait, le but est de pouvoir approfondir une recherche en cas de résultat nul après une recherche en FULLTEXT, et également sur des champs en CHAR ou VARCHAR.

Si tu cherches des mots, MATCH... (...IN BOOLEAN MODE) me semblerait pourtant suffisant et le + simple. Le BOOLEAN MODE indique un calcul de pertinence sur le mode "un point par mot-clé trouvé".

Envoyé par Xunil

Et donc je souhaite utiliser ceci afin d'essayer de faire un classement par pertinence, donc si l'utilisateur indique 4 mots, que les résultats retournés soit classés.

1- champs contenant les 4 mots (pas dur)
2- champs contenant 3 mots sur les 4
3- champs contenant 2 mots sur les 4
4- champs contenant 1 des 4 mots

C'est sans doute plus clair

Pour calculer cette pertinence, tu peux utiliser ou mon "bricolage booléen", ou le COUNT(*) avec une table Thesaurus.

[Xunil]

Question sans doute con, mais une table thesaurus, c'est quoi ?

C'est une table temporaire contenant les mots à rechercher, c'est ça ?

[Antoun]

Envoyé par Xunil

une table thesaurus, c'est quoi ?

C'est une table temporaire contenant les mots à rechercher, c'est ça ?

Ça pourrait être ça. Dans mon idée, c'était plutôt une table normale contenant l'ensemble des mots-clés potentiellement cherchables (ça ne s'applique pas à tous les cas !).

[Xunil]

Ok, merci, je vais voir pour essayer de faire de cette façon

Par contre une liste définie de mots suceptibles d'être rechercher, c'est pas trop évident à faire.

[Antoun]

Envoyé par Xunil

Par contre une liste définie de mots suceptibles d'être rechercher, c'est pas trop évident à faire.

Si c'est une recherche ouverte, c'est effectivement impossible !

[Xunil]

Effectivement, c'est pour une recherche par mots clefs renseignés par l'utilisateur

[Xunil]

Salut,

J'ai donc opté pour une table TEMP, ça m'a l'air pour l'instant pas trop mal, mais il y a un truc que je n'arrive pas à faire, c'est de compter le nombre de mots clefs totals présent dans un champ.
Pour l'instant, je n'ai que le nombre de mots clefs distinct.

Ma requête actuelle:

Code SQL :

1
2
3
4
5
6
SELECT id, titre, clefs, COUNT(id) AS oc_tt, COUNT(clefs) AS oc_k
FROM sujet 
JOIN TEMP_CLEF 
ON titre REGEXP CONCAT("[[:<:]]", clefs, "[[:>:]]")   
GROUP BY id
ORDER BY oc_tt DESC, oc_k DESC

Ce qui me ressort pour un test effectué avec pour mots recherchés: test et message

CHAINE - TOTAL DES MOTS - TOTAL DES MOTS DISTINCT

message message test => oc_tt : 2 => oc_k: 2
message test test => oc_tt : 2 => oc_k: 2
test test test => oc_tt : 1 => oc_k: 1
message => oc_tt : 1 => oc_k: 1
test => oc_tt : 1 => oc_k: 1
test test => oc_tt : 1 => oc_k: 1

Alors que le résultat que je souhaiterais avoir serait :

message message test => oc_tt : 3 => oc_k: 2
message test test => oc_tt : 3 => oc_k: 2
test test test => oc_tt : 3 => oc_k: 1
test test => oc_tt : 2 => oc_k: 1
message => oc_tt : 1 => oc_k: 1
test => oc_tt : 1 => oc_k: 1

Avec le GROUP BY, y'a pas de solution.

Une petite idée de comment je pourrais obtenir ceci ?

[Antoun]

Envoyé par Xunil

il y a un truc que je n'arrive pas à faire, c'est de compter le nombre de mots clefs totals présent dans un champ.

Ce n'est pas facile, parce que le moteur de regex de MySQL ne peut que te dire si la regex est présente dans ton texte ou non, mais pas combien de fois ni à quelle position.

Tu pourais créer une fonction perso, avec un algo de ce genre-là :

Code :

1
2
3
4
5
6
boucle avec compteur i partant de 1 
extrait = SUBSTRING(texte FROM i FOR taille mot-clé+ 2) ;
Si extrait REGEXP [[:<:]]mot-clé[[:>:]]
Alors on compte une occurence et i = i + taille mot-clé
Sinon i = i + 1
Si i dépasse (taille du texte - taille du mot-clé), sortie de boucle

Une autre solution serait d'utiliser un produit cartésien avec une table d'entiers pour émuler la boucle de manière ensembliste... mais je crains que le résultat ne soit assez coton à traiter !

[Xunil]

Merci, j'ai tenté de faire une fonction, et vu que c'est ma 1ère tentative de création de fonction en SQL, j'ai droit à des erreurs.

J'en suis bloqué à une que je ne comprend pas trop.

Voici mon oeuvre

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
delimiter | 
CREATE FUNCTION nombreClefChamp(champ TEXT, clef VARCHAR(255))
  RETURNS INTEGER
BEGIN
  SET @longChamp = length(champ);
  SET @longClef = length(clef);
  SET @nombreClef = 0;
  WHILE @longChamp > 1 
  DO
    SET @pos = LOCATE(clef, champ);
    IF @pos = 0 
      THEN @longChamp = 1;
    ELSE
      BEGIN
        IF SUBSTRING(champ, @pos - 1, @longChamp + 1) REGEXP CONCAT("[[:<:]]", clef, "[[:>:]]")
          THEN @nombreClef=@nombreClef + 1;
        champ = SUBSTRING(champ, @pos + @longClef);
        SET @longChamp = length(champ);
    END IF;
  END WHILE
  RETURN @nombreClef;
END;
|

Et l'erreur qui m'est indiquée :

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@longChamp = 1;
ELSE
BEGIN
IF SUBSTRING(champ, @pos - 1, @long' at line 9

Merci d'avance.

[Antoun]

- les variables utilisateur (du genre @toto) sont interdites dans les fonctions
- à la place il faut utiliser des variables locales, qui s'écrivent sans @ et doivent être déclarées avec DECLARE
- il te manque un END IF et quelques SET

ça devrait donner qqch comme ça (je n'ai pas testé ni cherché à comprendre, juste corrigé la syntaxe) :

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
CREATE FUNCTION nombreClefChamp(champ TEXT, clef VARCHAR(255))
  RETURNS INTEGER
BEGIN
  DECLARE longChamp INT ;
  DECLARE longClef INT ;
  DECLARE nombreClef INT DEFAULT 0 ;  
  DECLARE pos INT ;
  SET longChamp = length(champ);
  SET longClef = length(clef);
  WHILE longChamp > 1 
  DO
    SET pos = LOCATE(clef, champ);
    IF pos = 0 
    THEN SET longChamp = 1;
    ELSE
        IF SUBSTRING(champ, pos - 1, longChamp + 1) REGEXP CONCAT('[[:<:]]', clef, '[[:>:]]')
        THEN SET nombreClef = nombreClef + 1 ;
          SET champ = SUBSTRING(champ, pos + longClef);
          SET longChamp = length(champ);
		END IF ;
    END IF;
  END WHILE ;
  RETURN nombreClef;
END;

[Xunil]

Merci

J'y suis presque, mais il me reste un tout petit problème, c'est que si mon mot à rechercher est situé en fin de chaine, il n'est pas pris en compte.

select nombreClefChamp('test ceci est un test avec le mot test représenté 4 fois, test', 'test');
Résultat: 3
select nombreClefChamp('test ceci est un test avec le mot test représenté 3 fois', 'test');
Résultat: 3
select nombreClefChamp('ceci est un test avec le mot test représenté 3 fois, test', 'test');
Résultat: 2

La fonction actuelle :

Code SQL :

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
CREATE FUNCTION nombreClefChamp(champ TEXT, clef VARCHAR(255))
  RETURNS INTEGER
BEGIN
  DECLARE longChamp INT ;
  DECLARE longClef INT ;
  DECLARE nombreClef INT DEFAULT 0 ;  
  DECLARE pos INT ;
  DECLARE posStart INT;
  DECLARE posEnd INT;
  SET longChamp = LENGTH(champ);
  SET longClef = LENGTH(clef);
  WHILE longChamp > 1 
  DO
    SET pos = LOCATE(clef, champ);
    IF pos = 0 
    THEN SET longChamp = 1;
    ELSE
    	IF pos = 1
    	THEN SET posStart=pos;
    	ELSE SET posStart=pos-1;
    	END IF;
    	IF (pos+longClef) >= longChamp
    	THEN SET posEnd=longClef;
    	ELSE SET posEnd=longClef+1;
    	END IF;
      IF SUBSTRING(champ, posStart, posEnd) REGEXP CONCAT('[[:<:]]', clef, '[[:>:]]')
      THEN SET nombreClef = nombreClef + 1 ;
      END IF;
      SET champ = SUBSTRING(champ, pos + longClef);
      SET longChamp = LENGTH(champ);
		END IF;
  END WHILE;
  RETURN nombreClef;
END;

Donc c'est la condition

Code SQL :

1
2
3
4
IF (pos+longClef) >= longChamp
    	THEN SET posEnd=longClef;
    	ELSE SET posEnd=longClef+1;
    	END IF;

Pourtant, d'après ma logique, ça devrait être bon.

Je pourrais certes faire un CONCAT(ESPACE + champ + ESPACE) dans mon SELECT, mais je préfèrerais que ça soit fait dans la fonction.

[Xunil]

Bon, comme j'ai pas trouvé pourquoi ça déconnait, j'ai ajouté 2 espaces en fin de ma chaine.

Code SQL :

SET champ=CONCAT(champ, '  ');

En faisant ça, c'est ok.

Merci pour tout Antoun

[Antoun]

Attention, en utilisant LOCATE tu fais l'équivalent d'un LIKE '%motcle%'. Autrement dit, tu ne détecte plus les débuts et fins de mot comme tu le faisais avec REGEXP.

C'est pour cela que dans mon algo je prévoyais un REGEXP sur longClef + 2, afin d'avoir un caractère avant et un caractère après.

[Xunil]

Pour l'instant ça fonctionne bien, j'ai testé avec testx et xtest et ils ne sont pas compter dans le résultat.

J'essaierais d'optimiser ça, quand je me débrouillerais ujn peu mieux avec les fonctions MySQL.

[Antoun]

Code :

select nombreClefChamp('toto test xtest truc', 'test')

me donne bien 1, mais

Code :

select nombreClefChamp('toto test testx truc', 'test')

me donne 2

[Xunil]

Ah oui, j'avais également modifié d'autres critères dans la fonction, et son nom aussi, COUNT_WORD ça fait plus classe

La fonction finale que j'ai :

Code SQL :

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
CREATE FUNCTION COUNT_WORD(champ TEXT, clef VARCHAR(255))
  RETURNS INTEGER
BEGIN
  DECLARE longChamp INT ;
  DECLARE longClef INT ;
  DECLARE nombreClef INT DEFAULT 0 ;  
  DECLARE pos INT ;
  DECLARE posStart INT;
  DECLARE posEnd INT;
  SET champ=CONCAT(champ, '  ');
  SET longChamp = LENGTH(champ);
  SET longClef = LENGTH(clef);
  WHILE longChamp > longClef 
  DO
    SET pos = LOCATE(clef, champ);
    IF pos = 0 
    THEN SET longChamp = 1;
    ELSE
    	IF pos = 1
    	THEN SET posStart=pos;
    	ELSE SET posStart=pos-1;
    	END IF;
    	IF (pos+longClef) >= longChamp
    	THEN SET posEnd=longClef;
    	ELSE SET posEnd=longClef+2;
    	END IF;
      IF SUBSTRING(champ, posStart, posEnd) REGEXP CONCAT('[[:<:]]', clef, '[[:>:]]')
      THEN SET nombreClef = nombreClef + 1 ;
      END IF;
      SET champ = SUBSTRING(champ, pos + longClef);
      SET longChamp = LENGTH(champ);
		END IF;
  END WHILE;
  RETURN nombreClef;
END;

Avec celle-là, seul les mots stricts sont bien inclus dans le comptage.

Et sur mon tout 1er exemple

Code SQL :

1
2
3
4
5
6
7
SELECT id, titre, clefs, COUNT(clefs) AS nbDistinctClef, 
SUM(COUNT_WORD(titre, clefs)) AS nbTotalclef
FROM sujet
JOIN temp_clef 
ON titre REGEXP CONCAT("[[:<:]]", clefs, "[[:>:]]")   
GROUP BY id
ORDER BY nbDistinctClef DESC, nbTotalclef DESC

J'obtiens

message message test => nombre mot distinct : 2 => nombre de mots totaux: 3
message test test => nombre mot distinct : 2 => nombre de mots totaux: 3
test test test => nombre mot distinct : 1 => nombre de mots totaux: 3
test test => nombre mot distinct : 1 => nombre de mots totaux: 2
message => nombre mot distinct : 1 => nombre de mots totaux: 1
test => nombre mot distinct : 1 => nombre de mots totaux: 1

Ce qui est correct.

[Antoun]

impec, bravo pour ta première fonction dans MySQL !