Bloc "Rapports personnalisables" - comment définir et utiliser des constantes ?

Bloc "Rapports personnalisables" - comment définir et utiliser des constantes ?

par Séverin Terrier,
Nombre de réponses : 5
Avatar Documentation writers Avatar Moodleurs particulièrement utiles Avatar Testeurs Avatar Traducteurs

Bonjour,

Après avoir posé la question en anglais, je tente ma chance ici.

J'utilise beaucoup les rapports personnalisables SQL, pour différentes choses. Pour des questions de lisibilité, et surtout pour faciliter la maintenance et les modifications, j'aimerais pouvoir définir des constantes en début de requête (sur le principe évoqué ici), et les utiliser ensuite au sein de ma requête.

Malheureusement, ce bloc ne permet de lancer qu'une seule requête (les ; n'y sont pas tolérés), ce qui empêche d'utiliser la commande SET.

Quelqu'un aurait une idée permettant d'effectuer cela ?

Séverin

Moyenne des évaluations  -
En réponse à Séverin Terrier

Re: Bloc "Rapports personnalisables" - comment définir et utiliser des constantes ?

par Séverin Terrier,
Avatar Documentation writers Avatar Moodleurs particulièrement utiles Avatar Testeurs Avatar Traducteurs

Re-bonjour,

Alors, en menant quelques recherches, je suis d'abord tombé sur cette page, qui m'a fait découvrir une possible solution. Quelques tests m'ont permis de vérifier le bon fonctionnement.

Il s'avère qu'il s'agit d'expression de table commune (ou CTE : Common Table Expression), et que cette possibilité existe depuis MySql 8.0 (sorti en 2018) ou depuis MariaDB 10.2 (sorti en 2017).

Vous pouvez consulter la page d'aide dédiée MySQL et la page d'aide dédiée MariaDB.

Du coup, l'application à mon usage est la suivante :

  • En début de requête SQL, j'intègre quelques lignes pour définir mes valeurs
  • En début de liste des tables, j'ajoute ma table de valeurs
  • Dans mes requêtes, j'utilise ma table de valeurs

Exemple concret, en passant de la requête suivante :

SELECT
  cc.path,cc.name,c.fullname,FROM_UNIXTIME(c.startdate)

FROM prefix_course            AS c
JOIN prefix_course_categories AS cc   ON cc.id   = c.category

WHERE c.shortname LIKE '%droit%'       -- Pour cibler les cours
  AND cc.path     LIKE "%/1055/1057%"  -- Pour cibler une catégorie
# AND cc.path     LIKE '%/1055%'       -- Pour cibler une catégorie

A celle-ci :

-- On défini ici les valeurs (variables) à utiliser dans la requête
-- Fonctionne depuis MySql 8.0 et MariaDB 10.2
-- Si besoin, on peut faire les modifications ici (uniquement)
-- Si plus d'une ligne, les suivantes commencent par une virgule
-- Attention : pas 2 fois la même variable (commenter l'autre) !
WITH my AS ( SELECT
  '%droit%'      AS ChaineCours  -- Pour cibler les cours
#,'%/1055/1057%' AS CheminCat    -- Pour cibler la catégorie
 ,'%/1055%'      AS CheminCat    -- Pour cibler la catégorie (remplacer ligne dessus)
) -- Fin des valeurs personnalisées (on les appelle avec my.NomVariable)

-- On écrit la requête principale (pas besoin d'en modifier les valeurs)
SELECT
  cc.path,cc.name,c.fullname,FROM_UNIXTIME(c.startdate)

FROM my                       -- Pour intégrer les valeurs (variables) définies en haut
JOIN prefix_course            AS c
JOIN prefix_course_categories AS cc   ON cc.id   = c.category

WHERE c.shortname LIKE my.ChaineCours -- Pour cibler les cours
  AND cc.path     LIKE my.CheminCat   -- Pour cibler une catégorie

Alors effectivement, vu ainsi, on ne saisi pas trop l'intérêt (d'autant que c'est très long avec mes différents commentaires), mais lorsqu'on utilise des requêtes qui sont très longues, voire certains éléments utilisés plusieurs fois, on gagnera en lisibilité et facilité d'évolution (travailler sur des valeurs différentes) en modifiant les éléments en tête de requête plutôt qu'au sein de la requête.

Séverin

Moyenne des évaluations Utile (2)
En réponse à Séverin Terrier

Re: Bloc "Rapports personnalisables" - comment définir et utiliser des constantes ?

par Séverin Terrier,
Avatar Documentation writers Avatar Moodleurs particulièrement utiles Avatar Testeurs Avatar Traducteurs
Par contre, si quelqu'un trouve une solution permettant facilement d'avoir une liste de valeurs, au sein de laquelle faire un IN, ça m'intéresse.
Moyenne des évaluations  -
En réponse à Séverin Terrier

Re: Bloc "Rapports personnalisables" - comment définir et utiliser des constantes ?

par Christian Bocquet,
Avatar Moodleurs particulièrement utiles

Bonjour Séverin,

Pour des petites listes sur un petit exemple:

WITH identifiants AS (
  SELECT 4 AS identifiant
  UNION SELECT 5 AS identifiant
  UNION SELECT 7 AS identifiant
),
     langues AS (
  SELECT 'fr' AS lang
  UNION SELECT 'en' AS lang
)
SELECT u.lastname,u.firstname, u.lang
FROM prefix_user AS u
WHERE u.id IN (SELECT identifiant FROM identifiants) 
    AND u.lang IN (SELECT lang FROM langues)

Christian

Moyenne des évaluations Utile (1)
En réponse à Christian Bocquet

Re: Bloc "Rapports personnalisables" - comment définir et utiliser des constantes ?

par Séverin Terrier,
Avatar Documentation writers Avatar Moodleurs particulièrement utiles Avatar Testeurs Avatar Traducteurs
Merci beaucoup Christian pour cette piste utile sourire

J'ai testé et vu qu'on peut simplifier l'écriture. Dans ton exemple, la clause WITH peut devenir :

WITH
  identifiants(ID) AS (
  SELECT 4 UNION SELECT 5 UNION SELECT 7
)
, langues(lang) AS (
  SELECT 'fr' UNION SELECT 'en' UNION SELECT 'ar'
)
Et si on a des listes avec un nombre similaire d'enregistrements, et qu'on veut une seule table, on peut faire :
WITH My(ID,lang) AS (
      SELECT 4, 'fr'
UNION SELECT 5, 'en'
UNION SELECT 7, 'ar'
)
et renommer les appels des deux tables par "My".

Tout cela ouvre des perspectives intéressantes, qui devraient permettre de simplifier des requêtes très longues. Si on pouvait éviter les UNION SELECT et directement mettre des listes de valeurs, ça serait encore mieux.

Séverin
Moyenne des évaluations Utile (1)
En réponse à Séverin Terrier

Re: Bloc "Rapports personnalisables" - comment définir et utiliser des constantes ?

par didier cadiou,

Bonjour Séverin.

J'ai regardé le script SQL que tu cites. Il y a beaucoup de requêtes imbriquées, je comprends que tu ais besoin de définir des constantes.

Je ne connais pas non plus de solution pour faire un script SQL avec le plugin rapport personnalisé. Effectivement on ne peut faire qu'une seule requête.

En conséquence, le script devrait être transformé en requête unique pour que le plugin l'accepte. Je n'ai pas le temps de le reprendre (je rentre de congé), mais parmi les pistes de solutions que j'explorerais si je devais le faire, il y a le fait 

  • de faire plusieurs jointures avec une même table (mdl_course_module et mdl_logstore_standard_log par exemple), mais avec des conditions de jointure spécifiques à chaque besoin,
  • de remplacer certaines sous-requêtes par des comptages conditionnels. Concrètement, au lieu d'une sous-requête avec un 'count()', mettre dans la clause SELECT quelque chose comme '... SUM( IF( m.name NOT IN ('book', 'folder', 'label', 'page', 'resource', 'url', 'recordingsbn'), 1, 0))'. Quand la condition est satisfaite, c'est 1, sinon c'est 0. Après il n'y a plus qu'à faire la somme des 1 pour compter. Ca permet de faire des comptages avec des conditions différentes au sein d'une même requête. C'est quelque chose que j'utilise souvent. Bien sur la condition peut être exprimée autrement qu'avec des 'IF' car tous les SGBD ne l'accepte pas, mais on peut utiliser des 'CASE WHEN, THEN...'.

Moyenne des évaluations Utile (1)