prioriser les résultats dans une requête en fonction d'une règle de gestion

**speedev** · 02/02/2009, 11h59

Bonjour,

Je me casse les dents pour faire fonctionner une requête qui récupère des articles en fonction de la date du jour et des opérations commerciales qui lui sont appliquées OU non.

Je ne poste pas ma requête pour le moment.

Je vous explique très concrètement ma requête et vous allez me dire si vous voyez d'entrée de jeu une commande SQL qui s'y porterait bien.

Un article peut avoir N opérations commerciales qui lui sont appliquées sur une même période et en base de données. Sur le site je dois appliquer/afficher une seule opération commerciale et pour cela j'ai une règle de gestion simple visant à "prioriser" les opé, elle est la suivante : ope1>ope2>ope3.

Sommairement :
Ope 1 est prioritaire sur toutes les opé....
Ope 2 est prioritaire sur l'ope 3...

Déjà voyez-vous une syntaxe sql intéressante pour ce faire (genre avec des IF dans un WHERE par exemple...je n'ai rien trouvé là dessus) ?

Sachant que je récupère les opé/articles par un LEFT JOIN car un produit n'est pas forcément en ope, comment et surtout Où puis-je affecter une telle règle de gestion afin de ne récupérer qu'un seul état produit en fonction de l'opé ?

Je me vois mal faire des IN et NOT IN consécutifs avec des sous-requêtes (pfiou y'a peut-être plus performant). Et ça m'embête de récupérer tous les résultats possibles dans un tableau PHP pour ensuite les trier en conséquence de la règle de gestion.

Quelque chose me dit qu'une commande SQL (magique!) pourrait solutionner mon problème de "priorisation".

Merci

**Antoun** · 02/02/2009, 12h59

J'avais déjà fait un truc comme ça. En gros, ça marche comme une requête de type "le plus récent parmi", sauf qu'en l'occurence c'est "le plus prioritaire parmi".

**speedev** · 02/02/2009, 14h09

Oui c'est le concept effectivement "le plus prioritaire parmi", et il s'agirait donc quelque part de traiter une liste de choix.
Si tu retrouves ton cas déjà traité ça m'intéresse beaucoup !

**Antoun** · 02/02/2009, 14h23

Envoyé par speedev

Oui c'est le concept effectivement "le plus prioritaire parmi", et il s'agirait donc quelque part de traiter une liste de choix.
Si tu retrouves ton cas déjà traité ça m'intéresse beaucoup !

Je pourrais le retrouver, mais le fonctionnel était monstrueusement compliqué... tu n'y comprendrais rien et vraisemblablement moi non plus

, donc ça ne ferait pas trop avancer le schmilblick. Peux-tu me donner une maquette (éventuellement simplifiée) de ton cas ?

**speedev** · 02/02/2009, 14h42

J'ai allégé la requête car il y a beaucoup de clauses inutiles pour l'exemple, je pense que tu devrais piger ce que je souhaite avec cette maquette :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT art_id, L.lao_prx_old , L.lao_prx_new
FROM version 
INNER JOIN article AS M ON ([jointures_tables_précédentes])
LEFT JOIN lnk_art_ope AS L ON (
	L.lao_art=A.art_id AND (
		L.lao_ope IN (
			SELECT ope_id 
			FROM operation AS P 
			WHERE 
			(? BETWEEN P.ope_dad AND P.ope_daf) AND 
                        ope_typ EST_PRIORITAIRE_PARMI (ope1,ope2,ope3)

		)
	)
) 
WHERE [conditions diverses et variées]
GROUP BY art_id

Je récupère tous les articles (par un GROUP BY) mais je souhaite uniquement obtenir leurs caractéristiques (leurs prix en l'occurrence) vis-à-vis de l'opération commerciale la plus prioritaire pour chacun d'eux. La priorité entre chaque OC est la même pour tous les articles.

**Maljuna Kris** · 02/02/2009, 15h22

Saluton,
Je pense à un LEFT JOIN sur une sous-requête sur la table `lnk_art_ope`, sous-requête corrélée par l'id de l'article, classée par ORDER BY ope DESC et LIMIT 1.

**Antoun** · 02/02/2009, 15h25

Je réfléchis au fur et à mesure... déjà, je réécrirais ta requête en éliminant la sous-requête inutile :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT art_id, L.lao_prx_old , L.lao_prx_new
FROM version 
  INNER JOIN article AS M ON ([jointures_tables_précédentes])
  LEFT JOIN lnk_art_ope AS L ON L.lao_art=A.art_id 
  LEFT JOIN operation AP ON L.lao_ope = P.ope_id 
    AND ? BETWEEN P.ope_dad AND P.ope_daf
    AND P.ope_typ EST_PRIORITAIRE_PARMI (ope1,ope2,ope3)
WHERE [conditions diverses et variées]
GROUP BY art_id

Ensuite, il faut traduire ta priorité par un indicateur numérique. L'idéal serait d'avoir une colonne Priorite sur la table operation ou sur la table types_operation que tu as peut-être...

A défaut, on peut calculer cette prio avec un CASE WHEN ou, plus joli, avec un FIND_IN_SET. Si je retiens cette dernière hypothèse, la requête deviendrait qqch comme :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT art_id, L.lao_prx_old , L.lao_prx_new
FROM version 
  INNER JOIN article AS M ON ([jointures_tables_précédentes])
  LEFT JOIN lnk_art_ope AS L ON L.lao_art=A.art_id 
  LEFT JOIN operation AP ON L.lao_ope = P.ope_id 
    AND ? BETWEEN P.ope_dad AND P.ope_daf
    AND FIND_IN_SET(P.ope_typ, 'ope1', 'ope2', 'ope3') <= ALL (SELECT FIND_IN_SET(P2.ope_typ, 'ope1', 'ope2', 'ope3') 
  FROM lnk_art_ope AS L2 INNER JOIN operation P2 on L2.lao_ope = P2.ope_id
  WHERE L2.lao_art = A.art_id AND ? BETWEEN P2.ope_dad AND P2.ope_daf)
WHERE [conditions diverses et variées]
GROUP BY art_id

**speedev** · 02/02/2009, 15h26

Bien vu mais ope1 ope2 ope3 sont juste des exemples que je donne, leurs valeurs ne sont pas "ordonnançables".

**Antoun** · 02/02/2009, 15h28

Envoyé par Maljuna Kris

Saluton,
Je pense à un LEFT JOIN sur une sous-requête sur la table `lnk_art_ope`, sous-requête corrélée par l'id de l'article, classée par ORDER BY ope DESC et LIMIT 1.

De mémoire, le LIMIT est à peu près inutilisable dans une sous-requête...

**Maljuna Kris** · 02/02/2009, 15h29

Envoyé par speedev

Bien vu mais ope1 ope2 ope3 sont juste des exemples que je donne, leurs valeurs ne sont pas "ordonnançables".

Ça va être coton de classer ou prioriser selon un critère aussi 'flou'.

**speedev** · 02/02/2009, 15h30

Envoyé par Antoun

Je réfléchis au fur et à mesure... déjà, je réécrirais ta requête en éliminant la sous-requête inutile :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT art_id, L.lao_prx_old , L.lao_prx_new
FROM version 
  INNER JOIN article AS M ON ([jointures_tables_précédentes])
  LEFT JOIN lnk_art_ope AS L ON L.lao_art=A.art_id 
  LEFT JOIN operation AP ON L.lao_ope = P.ope_id 
    AND ? BETWEEN P.ope_dad AND P.ope_daf
    AND P.ope_typ EST_PRIORITAIRE_PARMI (ope1,ope2,ope3)
WHERE [conditions diverses et variées]
GROUP BY art_id

Oui j'avais cette requête au départ mais j'ai du intégré la sous requête dans le premier left join pour obtenir certaines résultat escomptés.Je peux revenir dessus maintenant...

Envoyé par Antoun

Ensuite, il faut traduire ta priorité par un indicateur numérique. L'idéal serait d'avoir une colonne Priorite sur la table operation ou sur la table types_operation que tu as peut-être...

A défaut, on peut calculer cette prio avec un CASE WHEN ou, plus joli, avec un FIND_IN_SET. Si je retiens cette dernière hypothèse, la requête deviendrait qqch comme :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT art_id, L.lao_prx_old , L.lao_prx_new
FROM version 
  INNER JOIN article AS M ON ([jointures_tables_précédentes])
  LEFT JOIN lnk_art_ope AS L ON L.lao_art=A.art_id 
  LEFT JOIN operation AP ON L.lao_ope = P.ope_id 
    AND ? BETWEEN P.ope_dad AND P.ope_daf
    AND P.ope_typ <= ALL (SELECT FIND_IN_SET(P2.ope_typ, 'ope1', 'ope2', 'ope3') 
  FROM lnk_art_ope AS L2 INNER JOIN operation P2 on L2.lao_ope = P2.ope_id
  WHERE L2.lao_art = A.art_id AND ? BETWEEN P2.ope_dad AND P2.ope_daf)
WHERE [conditions diverses et variées]
GROUP BY art_id

Je dis bravo... je viens juste de lire les "case when then else" sur le site de mysql, mais le FIND_IN_SET c'est exactement ce que je veux !

Sinon un champ de priorité serait le bienvenu mais trop tard...le contexte de recette approche.

Merci je teste ça tout de suite.

**Maljuna Kris** · 02/02/2009, 15h35

Envoyé par Antoun

De mémoire, le LIMIT est à peu près inutilisable dans une sous-requête...

Presque vrai, donc pas si sûr

#

ERROR 1235 (ER_NOT_SUPPORTED_YET)
SQLSTATE = 42000
Message = "This version of MySQL doesn't yet support
'LIMIT & IN/ALL/ANY/SOME subquery'"

Cela signifie que

SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1)

ne fonctionnera pas, mais uniquement dans certaines versions d'origines, comme MySQL 4.1.1.

**speedev** · 02/02/2009, 15h58

Je ne peux pas encore dire que le résultat est OK, je dois pousser mes tests un peu plus loin mais ça me parait pas mal du tout.

Par contre je viens de me souvenir pourquoi j'ai utilisé une sous-requête que tu trouvais inutile : Parcequ'une opération peut ne pas exister ! Je dois donc être sûr que l'opération existe c'est pourquoi un second LEFT JOIN n'était pas ok.

**Antoun** · 02/02/2009, 16h03

Envoyé par Maljuna Kris

Presque vrai, donc pas si sûr

Je viens de tester que c'est toujours vrai en 5.0.67.

**speedev** · 02/02/2009, 16h17

Antoun ça fonctionne bien nickel !
Seulement je me retrouve coincé si je veux ajouter une condition par rapport à une OP particulière.

Par exemple si c'est l'ope2 qui est retournée par le FIND_IN_SET, je souhaite contrôler que l'ancien prix du produit est bien différent du nouveau prix (règle de gestion autrefois placée dans le ON du LEFT JOIN) car cette ope2 est une opération promotionnelle par exemple.

Telle qu'est formée la requête je dois faire une clause conditionnelle mais j'ignore comment faire.

**Antoun** · 02/02/2009, 16h31

Dans ce cas, il faut que tu remplaces les deux FIND_IN_SET par deux CASE WHEN :

Code :

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

1
2
3
4
5
6
CASE 
  WHEN ope_typ = 'ope1' THEN 1
  WHEN ope_typ = 'ope2' AND autres conditions THEN 2
  WHEN ope_typ = 'ope3' THEN 3
  ELSE 4
END

**speedev** · 02/02/2009, 17h50

Ça marche maintenant ! Youpi !

(Maintenant je vais me pencher sur les perf car cette requête risque de causer du tort au serveur sql...)

Encore merci !

**Antoun** · 02/02/2009, 23h14

Envoyé par speedev

Et ça marche maintenant ! Youpi !

(Maintenant je vais me pencher sur les perf car cette requête risque de causer du tort au serveur sql...)

Encore merci !

MySQL gère très mal les sous-requêtes corrélées. Si tu as des problèmes de perf, il faudra réécrire la sous-requête sous forme de jointure (cf. http://sqlpro.developpez.com/cours/s...usrequetes/#L3).

**speedev** · 02/02/2009, 23h59

Ok merci.
Je ne pense pas pouvoir transformer les sous-requêtes en jointure, a priori je me trouve dans le mm cas que l'exemple 1 mais je vérifierai tout ça.

**Antoun** · 03/02/2009, 10h52

Au minimum, tu peux remplacer la sous-req corrélée par une sous-req dans le FROM. Comme <= ALL est équivalent à = MIN (aux NULL près), je dirais que ça donne qqch comme ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT art_id, L.lao_prx_old , L.lao_prx_new
FROM version 
  INNER JOIN article AS M ON ([jointures_tables_précédentes])
  LEFT JOIN lnk_art_ope AS L ON L.lao_art=A.art_id 
  LEFT JOIN operation AP ON L.lao_ope = P.ope_id 
    AND ? BETWEEN P.ope_dad AND P.ope_daf
  LEFT JOIN (
    SELECT L2.lao_art, MIN(FIND_IN_SET(P2.ope_typ, 'ope1', 'ope2', 'ope3')) AS Prio
    FROM lnk_art_ope AS L2 INNER JOIN operation P2 ON L2.lao_ope = P2.ope_id
    WHERE ? BETWEEN P2.ope_dad AND P2.ope_daf
    GROUP BY L2.lao_art
  ) AS Pri ON Pri.lao_art = A.art_id 
       AND FIND_IN_SET(P.ope_typ, 'ope1', 'ope2', 'ope3') = Pri.Prio
WHERE [conditions diverses et variées]
GROUP BY art_id

Ça remplace les corrélation par des jointures sur une table temporaire, ce que MySQL gère bcp mieux. Ça ne serait pas forcément une bonne idée sur SQL Server, mais sur MySQL oui !

prioriser les résultats dans une requête en fonction d'une règle de gestion

Requêtes MySQL

Discussions similaires

Partager

Partager