[2008] Filtres conditionnels sur requête SQL ?

Filtres conditionnels sur requête SQL ?

Bonjour,

Après plusieurs jours de méli-mélo intellectuel, j'en remets mon problème à la communauté.

J'ai une page web où il est possible de sélectionner des filtres pour une requête. La requête doit alors ressortir toutes les personnes répondant aux critères saisis dans la page. Ces filtres sont enregistrés dans une table sous cette forme

(a noter qu'il y a une autre table, non utilisé dans ce contexte, qui permet de stocker les informations générales de la sélection, comme le nom, un commentaire, à qui appartient la sélection, la date de création, avec pour id unique la clé étrangère ci dessous : idbase)

iddetail : identifiant unique du filtre
idbase : identifiant de la sélection
keyname : nom de la clé
keyvalue : valeur de la clé

Chacun de ces filtres sont multiples, représentés par des listes.

Les listes sont alimentées par diverses requêtes de ce type :

SELECT DISTINCT(un champ, un autre champ) FROM UTILISATEURS

où l'on récupère un identifiant (non unique sur la table) & un texte accompagnant.

L'ensemble des données est dans la table "utilisateurs" (pas de table périphérique pour stocker ces informations avec des clés étrangères)

Ainsi, si je remplis ma sélection avec 2 types de filtres sur 5, j'obtiens dans ma table de filtre :

iddetail idbase keyname keyvalue
1 1 'REGION' 'ile de france'
2 1 'REGION' 'PACA'
3 1 'SOCIETE' 'Societe X'
4 1 'SOCIETE' 'Societe Y'
5 1 'SOCIETE' 'Societe Z'

Il y a alors 5 catégories de filtres possibles :
- Région
- Société
- Responsable
- Magasin
- Responsable régional

Je voudrai alors faire une procédure stockée qui utilise les filtres renseignés dans la table et les appliquer à ma requête.

J'ai bien la solution à mon problème, mais pas assez propre à mon gout :
- Construire la requête en .NET et l'exécuter
- Construire la requête en SQL et l'exécuter via un "execute_sql"

Donc j'ai essayé plein de combinaison, mais la clé de mon problème est que lorsque aucun filtre n'est définit pour une clé donnée (keyname), il faut avoir "tous les utilisateurs".

J'ai essayé de faire l'intersection (INTERSECT) entre tous les résultats des requêtes de ce type :

SELECT
usr.*
FROM UTILISATEURS AS usr INNER JOIN MES_FILTRES AS filtre ON usr.region = filtre.keyvalue AND filtre.keyname = 'REGION' AND filtre.idbase = 1

(etc, pour chaque type de filtre)

Mais étant donné que dans l'exemple, je n'ai pas de critère de filtre pour 3 types sur 5, le result set de chacune de ces requêtes est par conséquent, NULL. L'intersect ne marche pas, et le résultat complet est NULL.

L'UNION des résultats peut être pensé, mais on obtient alors, une combinaison "OU" (OR) des filtres, et ils doivent être de ce type :
(REGION = 'IDF' OR REGION = 'PACA') AND (SOCIETE='SOCIETE_x')

S'en suit tout un tas de test divers & variés à base de UNION / INTERSECT / EXCEPT ...

Au final, mon souci est toujours le même : ne pas pouvoir agir conditionnellement sur ma requête ... Par exemple, faire tel type de jointure si filtre, ou tel type si non.

Des conseils avisés ?

Merci d'avance à celui/celle/ceux pouvant aider à la résolution de mon problème...

Hello,

Je suis pas sûr d'avoir bien compris ce que vous voulez mais voici toujours les réponses à ce que j'ai cru comprendre ^^.

Avoir une requête avec filtre variable - Exemple : table de personne avec sélection au choix sur le nom, le prénom et/ou la date de naissance
Je passe la définition de la table qui se veut triviale.
On aura alors une requête comme ceci
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 25 DECLARE @NOM VARCHAR(50) = NULL; DECLARE @PRENOM VARCHAR(50) = NULL; DECLARE @DATE DATE = NULL --AFFECTATION DES VALEURS QUI VONT BIEN AUX VARIABLES SELECT * FROM T_PERSONNE WHERE 1 = CASE WHEN @NOM IS NULL THEN 1 WHEN @NOM = NOM THEN 1 ELSE 0 END AND 1 = CASE WHEN @PRENOM IS NULL THEN 1 WHEN @PRENOM = PRENOM THEN 1 ELSE 0 END AND 1 = CASE WHEN @DATE IS NULL THEN 1 WHEN @DATE = DATE_NAISSANCE THEN 1 ELSE 0 END
Avec ceci (à intégrer dans une procédure stockée par exemple), vous pourrez donc filtrer de manière automatique sur les filtres données en paramètre par l'utilisateur. Si le fitre n'est pas fourni, le marqueur NULL est affecté par défaut à la variable et donc le résultat du test sera positif (1 = 1 --> donc pas de tri).

Si maintenant, pour chaque filtre, l'utilisateur peut donner une liste de valeur, j'utiliserais personnellement les TVP (Table Value Parameter). Cela fonctionnerait exactement de la même manière. Sauf que, à la place d'avoir l'opérateur "=", on aurait l'opérateur "IN" avec une requête sur le TVP.

Cela répond-il à votre problème?

Bonjour,

Vous pouvez tenter quelque chose dans ce style :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT 
    u.id,
    f.idbase
FROM UTILISATEUR u
INNER JOIN MES_FILTRE f
    ON f.keyvalue = CASE f.keyname
                             WHEN 'REGION' THEN u.region
                             WHEN 'SOCIETE' THEN u.societe
                             ...
                        END
GROUP BY u.id,
    f.idbase
HAVING COUNT(*) = COUNT(DISTINCT f.keyname)

Mais n'attendez pas de bonnes performances !

Vous êtes un chef :) !!

Effectivement, ça marche bien comme cela :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
// 2 lignes de filtres
DECLARE @filtreRegion int;
SET @filtreRegion = (SELECT count(*) FROM MES_FILTRES WHERE keyName = 'REGION' AND idBase = @idSelection);
 
// pas de ligne
DECLARE @filtreSociete int;
SET @filtreSociete = (SELECT count(*) FROM MES_FILTRES WHERE keyName = 'SOCIETE' AND idBase = @idSelection);
 
 
SELECT * FROM UTILISATEURS 
WHERE
1 = CASE
WHEN @filtreRegion = 0 then 1
WHEN @filtreRegion > 0 AND Region IN (SELECT keyValue FROM MES_FILTRES WHERE keyName = 'REGION' AND idBase = @idSelection) THEN 1
ELSE 0
END
AND
1 = CASE
WHEN @filtreSociete = 0 THEN 1
WHEN @filtreSociete > 0 AND societe IN (SELECT keyValue FROM MES_FILTRES WHERE keyName = 'SOCIETE' AND idBase = @idSelection) THEN 1
ELSE 0
END

Ravi d'avoir pu aider !

Citation:

Envoyé par Luc1an0

Ainsi, si je remplis ma sélection avec 2 types de filtres sur 5, j'obtiens dans ma table de filtre :

iddetail idbase keyname keyvalue
1 1 'REGION' 'ile de france'
2 1 'REGION' 'PACA'
3 1 'SOCIETE' 'Societe X'
4 1 'SOCIETE' 'Societe Y'
5 1 'SOCIETE' 'Societe Z'

Dans votre cas, le plus performant est de créer des tables temporaires et de joindre votre table avec ces tables temporaires.

Exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE #T_REGION (REGION VARCHAR(32) NOT NULL); CREATE TABLE #T_SOCIETE (SOCIETE VARCHAR(32) NOT NULL); INSERT INTO #T_REGION VALUE ('ile de france'), ('PACA'); INSERT INTO #T_SOCIETE VALUE ('societe X'), ('societe Y'), ('Societe Z'); ALTER TABLE #T_REGION ADD CONSTRAINT PRIMARY KEY (REGION); ALTER TABLE #T_SOCIETE ADD CONSTRAINT PRIMARY KEY (SOCIETE); SELECT * FROM MaTable AS T INNER JOIN #T_REGION AS R ON T.REGION = R.REGION INNER JOIN #T_SOCIETE AS S ON T.SOCIETE= S.SOCIETE;
A +

Citation:
Envoyé par SQLpro
Dans votre cas, le plus performant est de créer des tables temporaires et de joindre votre table avec ces tables temporaires.

Exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE #T_REGION (REGION VARCHAR(32) NOT NULL); CREATE TABLE #T_SOCIETE (SOCIETE VARCHAR(32) NOT NULL); INSERT INTO #T_REGION VALUE ('ile de france'), ('PACA'); INSERT INTO #T_SOCIETE VALUE ('societe X'), ('societe Y'), ('Societe Z'); ALTER TABLE #T_REGION ADD CONSTRAINT PRIMARY KEY (REGION); ALTER TABLE #T_SOCIETE ADD CONSTRAINT PRIMARY KEY (SOCIETE); SELECT * FROM MaTable AS T INNER JOIN #T_REGION AS R ON T.REGION = R.REGION INNER JOIN #T_SOCIETE AS S ON T.SOCIETE= S.SOCIETE;
A +
Il y a des problèmes de performances avec les TVP's ? Parce que fondamentalement, c'est la même solution non ?
De plus je ne vois pas, s'il fallait en faire une SP, comment je ferais sans TVP mais si je voulais utiliser des tables temporaires derrière. Il faut bien transmettre les valeurs à mettre dans les tables temporaires à la SP. Et vu que je peux avoir plusieurs valeurs par paramètre, j'ai besoin de TVP's.

Non ?