Discussion :

[dré kam]

Bonjour,

Je travaille sur un site web et j'aimerais améliorer mon moteur de recherche. Je n'attends pas spécialement de code, mais ce que je veux c'est surtout savoir si ce que je veux faire est faisable et où orienter mes recherches. Bien entendu, je ne dirais pas non à des exemples de code SQL.
Alors j'ai une page "recherche avancée" sur le site qui permet de trouver des utilisateurs selon plusieurs critères. Par exemple, on peut trouver des utilisateurs vivant dans un pays en particulier, professionnels dans un domaine spécifique, etc. Je voudrais ordonner les résultats de manière à avoir les utilisateurs qui remplissent tous les critères, puis ceux qui en remplissent deux sur trois, puis ceux qui en remplissent un sur trois. Je précise que dans ma requête je fais des "OU" (OR) entre les critères. Y a-t-il un moyen d'y arriver depuis MySQL ou faut-il traiter les résultats après dans mon code ?
L'autre chose c'est le stockage des résultats. Disons que j'ai une requête plus complexe, sur plusieurs tables. Je cherche un moyen de stocker les résultats, ou en tout cas d'éviter au serveur d'avoir à refaire tout le travail à chaque fois que l'utilisateur veut passer à la page suivante/précédente. Quelle est la meilleure approche? Idéalement je souhaiterais avoir une solution qui n'est pas trop chronophage.
La dernière chose, et là c'est vraiment un bonus, c'est obtenir des résultats en cas de mauvaise orthographe d'un critère. Si un utilisateur recherche des membres s'appelant "Anna", pouvoir lui proposer des "Ana". J'ai réussi à le faire avec les villes en ayant une base de données d'orthographes différentes, est-ce qu'il y a un moyen de le faire ici?

Merci

[SQLpro]

REMARQUE GÉNÉRALE : votre question aurait été plus adéquate dans le forum SGBD en général...

Alors j'ai une page "recherche avancée" sur le site qui permet de trouver des utilisateurs selon plusieurs critères. Par exemple, on peut trouver des utilisateurs vivant dans un pays en particulier, professionnels dans un domaine spécifique, etc. Je voudrais ordonner les résultats de manière à avoir les utilisateurs qui remplissent tous les critères, puis ceux qui en remplissent deux sur trois, puis ceux qui en remplissent un sur trois. Je précise que dans ma requête je fais des "OU" (OR) entre les critères. Y a-t-il un moyen d'y arriver depuis MySQL ou faut-il traiter les résultats après dans mon code ?

Il n'y a pas d'automatisme possible à ce niveau. Vous pouvez éventuellement faire une procédure stockée avec du SQL dynamique qui est généré en dégradant les critères associés à un score (exemple : SELECT 1 AS SCORE, * FROM ... WHERE <tous les critères> UNION ALL SELECT 2 AS SCORE, * FROM ... WHERE <tous les critères - 1> UNION ALL SELECT 3 AS SCORE, * FROM ... WHERE <tous les critères -2> ), mais c'est complexe à réaliser et fastidieux...
Une autre solution est de tout faire via la recherche FULL TEXT. Hélas le moteur Full Text de MySQL est une vraie merde.
Dans d'autres SGBDR (MS SQL Server pour ne pas le citer) vous pouvez obtenir une réponse avec un "score" de recherche. Le prédicat devient :
FREETEXTTABLE(<liste_colonnes_de_recherche>, 'tous les mots clef')
Lisez le papier que j'ai écrit à ce sujet : https://blog.developpez.com/sqlpro/p...text_search_no

L'autre chose c'est le stockage des résultats. Disons que j'ai une requête plus complexe, sur plusieurs tables. Je cherche un moyen de stocker les résultats, ou en tout cas d'éviter au serveur d'avoir à refaire tout le travail à chaque fois que l'utilisateur veut passer à la page suivante/précédente. Quelle est la meilleure approche? Idéalement je souhaiterais avoir une solution qui n'est pas trop chronophage.

Il suffit de stocker les clefs du résultat dans une table temporaire locale ou globale ou encore une pseudo table temporaire (qui a ma préférence) avec en sus une colonne d'ordonnancement. Puis de réutiliser cette table temporaire pour chaque accès page. Pour mieux faire, il faut taguer les données de cette table avec un identifiant de session. Là aussi MySQL n'est pas à l'aide vue la pauvreté de son niveau de SQL, car il faudrait implémenter les fonction de fenêtrage et d'ordonnancement comme RANK, ROW_NUMBER, etc.
A lire sur le sujet : http://sqlpro.developpez.com/article...clause-window/
Là encore, je ne saurait trop vous recommander l'usage de SQL Server qui est le SGBDR le plus complet et le plus normatif à ce jour.

La dernière chose, et là c'est vraiment un bonus, c'est obtenir des résultats en cas de mauvaise orthographe d'un critère. Si un utilisateur recherche des membres s'appelant "Anna", pouvoir lui proposer des "Ana". J'ai réussi à le faire avec les villes en ayant une base de données d'orthographes différentes, est-ce qu'il y a un moyen de le faire ici?

Il existe des solutions pour faire de la correction automatique de saisie, tel que l'algorithme du dictionnaire de KNUTH, ou encore en passant par des fonctions de rapprochement tel que le LEVENSHTEIN, le HAMMING ou l'inférence directe dont je suis l'auteur et qui est actuellement utilisée par la Police pour la traque des terroristes...
Mais encore faudrait-il avoir accès à la liste des mots indexés afin de préparer les codes correcteurs (par exemple pour l'algo du dico de KNUTH) afin d'être efficace. Malheureusement je ne pense pas qu'il soit possible de le faire avec MySQL...
Là encore, MS SQL Server aurait été d'un grand secours, car on a toujours accès à toutes les tables internes afin d'étendre les capacités du moteur de recherche, ce que j'ai fait pour plusieurs de mes clients...
Au sujet de la recherche de motif (nécessaire pour la comparaisons des dysorthographie) Lisez l'article que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/s...aisons-motifs/

Bref, orientez vous pour sur un vrai SGBDR et non pour un ersatz !!!
https://blog.developpez.com/sqlpro/p...oudre_aux_yeux

A +

[skuatamad]

Je voudrais ordonner les résultats de manière à avoir les utilisateurs qui remplissent tous les critères, puis ceux qui en remplissent deux sur trois, puis ceux qui en remplissent un sur trois. Je précise que dans ma requête je fais des "OU" (OR) entre les critères.

Code :

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

1
2
3
case when colonne1 = '$critere1' then 1 else 0 end +
case when colonne2 = '$critere2' then 1 else 0 end +
case when colonne3 = '$critere3' then 1 else 0 end as nb_critere

devrait permettre de trié sur le nb_critere desc pour avoir ceux qui en remplissent le plus en 1er.
Ce CASE sera à générer dynamiquement en fonction des critères sélectionnés.

Disons que j'ai une requête plus complexe, sur plusieurs tables. Je cherche un moyen de stocker les résultats, ou en tout cas d'éviter au serveur d'avoir à refaire tout le travail à chaque fois que l'utilisateur veut passer à la page suivante/précédente.

Plutôt que de stocker le résultat de la requête, ce qui peut être coûteux si la requête renvoie un très grand nombre de ligne (nécessite de stocker l'intégralité du résultat même si l'utilisateur ne consulte que 10 lignes), vous pourriez stocker le résultat du select complexe avec jointure.
Sur oracle, j'utiliserai une vue matérialisée rafraichie périodiquement avec des index bitmap sur les colonnes de filtre utilisée dans les conditions OR.
Mais mysql ne propose ni vue matérialisée ni index bitmap, cependant vous pouvez toujours avoir une table qui stocke le résultat de la requête et un cron qui rafraichie périodiquement cette table, et utiliser des index classique.
Le SELECT de recherche avec les critères dynamiques s'appuiera sur cette table.

Après tout dépend de la volumétrie de base, le SELECT complexe peut être très efficace sans avoir besoin de stocker son résultat.

[dré kam]

Code :

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

1
2
3
case when colonne1 = '$critere1' then 1 else 0 end +
case when colonne2 = '$critere2' then 1 else 0 end +
case when colonne3 = '$critere3' then 1 else 0 end as nb_critere

devrait permettre de trié sur le nb_critere desc pour avoir ceux qui en remplissent le plus en 1er.
Ce CASE sera à générer dynamiquement en fonction des critères sélectionnés.


Plutôt que de stocker le résultat de la requête, ce qui peut être coûteux si la requête renvoie un très grand nombre de ligne (nécessite de stocker l'intégralité du résultat même si l'utilisateur ne consulte que 10 lignes), vous pourriez stocker le résultat du select complexe avec jointure.
Sur oracle, j'utiliserai une vue matérialisée rafraichie périodiquement avec des index bitmap sur les colonnes de filtre utilisée dans les conditions OR.
Mais mysql ne propose ni vue matérialisée ni index bitmap, cependant vous pouvez toujours avoir une table qui stocke le résultat de la requête et un cron qui rafraichie périodiquement cette table, et utiliser des index classique.
Le SELECT de recherche avec les critères dynamiques s'appuiera sur cette table.

Après tout dépend de la volumétrie de base, le SELECT complexe peut être très efficace sans avoir besoin de stocker son résultat.

Je ne vous suis pas. Quelle est la différence entre stocker un SELECT et stocker le résultat?

[skuatamad]

Je ne vous suis pas. Quelle est la différence entre stocker un SELECT et stocker le résultat?

Stocker le résultat du SELECT sans les critères de recherche afin d'avoir une table dédiée à la recherche à requêter avec les critères des utilisateurs versus stocker le résultat de chaque recherche effectuée par les utilisateurs comme vous sembliez le proposer.

[dré kam]

Merci @SQLPro pour votre réponse complète. Alors j'ignorais l'existence des tables temporaires. Je viens de lire la documentation ( https://dev.mysql.com/doc/refman/5.7...ary-table.html ) et j'ai quelques questions à ce sujet. Il est indiqué que la table est supprimée à la fin de la session MySQL. Dans le cas d'un site web, la connexion avec la BDD est souvent fermée et ouverte, ça ne veut pas dire que la table serait supprimée dès l'affichage de la première page de résultats? Je pense à utiliser un table classique et à stocker les résultats de recherche ainsi que l'identifiant de la sélection PHP en cours, puis supprimer les lignes liées quand l'utilisateur se déconnecte. Je pourrais aussi faire une procédure qui supprime les entrées datant de plus d"une semaine par exemple.

Est-ce que je devrais créer une autre discussion dans le forum SGBD?

[SQLpro]

Merci @SQLPro pour votre réponse complète. Alors j'ignorais l'existence des tables temporaires. Je viens de lire la documentation ( https://dev.mysql.com/doc/refman/5.7...ary-table.html ) et j'ai quelques questions à ce sujet. Il est indiqué que la table est supprimée à la fin de la session MySQL. Dans le cas d'un site web, la connexion avec la BDD est souvent fermée et ouverte, ça ne veut pas dire que la table serait supprimée dès l'affichage de la première page de résultats? Je pense à utiliser un table classique et à stocker les résultats de recherche ainsi que l'identifiant de la sélection PHP en cours, puis supprimer les lignes liées quand l'utilisateur se déconnecte. Je pourrais aussi faire une procédure qui supprime les entrées datant de plus d"une semaine par exemple.

Est-ce que je devrais créer une autre discussion dans le forum SGBD?

Effectivement c'est pourquoi je proposait une table pseudo temporaire, c'est à dire une table en dur dans votre application qui contient différentes colonnes tel que :

• un identifiant de session (par exemple un GUID)
• une dateHeure de la session
• un INT pour les clefs à stocker (par exemple n° d'article si c'est un site de vente)
• une INT avec l'ordre de 1 à N

Et il suffit de conserver dans votre session PHP ou dans un cookie du navigateur la valeur du GUID de session de l'internaute, voire même le passer directement dans les paramètres des pages HTML.

Et vider non pas toutes les semaine, mais toutes les 12 heures au maximum (sur de gros sites web marchand je met 30 minutes, avec rafraichissement)... Les internautes mangent ! et une session authentifié à une limite der durée !!!

A +

[dré kam]

Effectivement c'est pourquoi je proposait une table pseudo temporaire, c'est à dire une table en dur dans votre application qui contient différentes colonnes tel que :

• un identifiant de session (par exemple un GUID)
• une dateHeure de la session
• un INT pour les clefs à stocker (par exemple n° d'article si c'est un site de vente)
• une INT avec l'ordre de 1 à N

Et il suffit de conserver dans votre session PHP ou dans un cookie du navigateur la valeur du GUID de session de l'internaute, voire même le passer directement dans les paramètres des pages HTML.

Et vider non pas toutes les semaine, mais toutes les 12 heures au maximum (sur de gros sites web marchand je met 30 minutes, avec rafraichissement)... Les internautes mangent ! et une session authentifié à une limite der durée !!!

A +

Oui, je pense que cette approche résout parfaitement le problème. Merci