Construire ses requêtes
Théorie : les éléments de construction
Chaque question est un tableau constitutué de différents éléments :
"access" : Regroupe les types de membres autorisés a accéder à une question.
=> array(ADMIN,TUTEUR) // seuls l'admin et les tuteurs auront
accès à cette question
=> "STAGIAIRE" // seul les membres de types "stagiaires"
auront accès à cette question
=> array(0,3) // seuls les stagiaires et les tuteurs auront accès
à cette question (types Ganesha)
"noaccess" : Regroupe les types de membres non-autorisés a accéder à une question.
=> "STAGIAIRE" // seul les membres de types "stagiaires" auront n'auront pas accès à cette question
"cat" : Intitulé de la catégorie (les catégories sont classées par ordre alphabétique)
=> "Ma catégorie"
=> "02- catégorie n°2"
"subcat" : Intitulé de la sous-catégorie (les sous-catégories sont classées par ordre alphabétique au sein de leur catégorie respective)
=> "1- Sous Catégorie1 de la cat2"
"version" : Version de ganesha
avec laquelle la question est compatible.
Utilisé conjointement avec les constantes de config.inc.php, permet d'afficher
ou non une question en fonction d'un n° de version.
Exemple :
* version = "2";
* VERSIONCHECK = "1.3"; // défini dans config.inc.php
* SORTVERSION = "GE"; // défini dans config.inc.php
* Cela peut être interprété comme :
* "Affiche la question si 1.3 est supérieur ou égal à
2"
* Donc la question ne sera PAS affichée.
"q" : Intitulé de la question (qui sera affiché à l'écran) [obligatoire]
=> "Qui (nom/prénom/groupe) a fait un exercice entre telle date et telle date ?"
"r" : Requête (jusqu'à la clause WHERE) [obligatoire]
=> 'SELECT DISTINCT m.nom, m.prenom, l.name as Nom_du_groupe, g.id_groupe FROM membres m, list_groupe l, groupe g, tracking t'
"static" : Partie de la requête qui suit la cause WHERE qui n'est pas amené à être modifiée.
=> "l.id_groupe = g.id_groupe AND m.id=g.id_membres AND m.id=t.user_id"
"date" : Nom du champs permettant de filtrer la question sur la date.
=> "tracking.date"
"orderby" : liste des clauses ORDER
BY.
Le premier élément est le nom de la clause telle qu'il sera affiché.
[obligatoire]
Le deuxième élément est le champ de la clause tel qu'il
existe dans la base. [obligatoire]
Le troisième élément est l'ordre par défaut dans
lequel le tri sera effectué.
Plusieurs clauses peuvent être listées sous forme de tableau.
=> array("Date,t.date", "Groupe,g.id_groupe,DESC")
// exemple avec 2 clauses
=> "Date,t.date" // exemple avec une clause sans tabeau
=> array("Date,t.date") // exemple avec une clause dans un tableau
"groupby" : liste des clauses GROUP
BY.
"groupby" est constitué de la liste des champs de regroupement
(séparés par des virgules s'il y en a plusieurs)
=> "g.id_groupe"
=> "g.id_groupe, t.date"
"options" : liste des options de
la clause WHERE que l'on souhaite pouvoir manipuler.
Le premier élément est le nom de l'option tel qu'il sera affiché.
[obligatoire]
Le deuxième élément est le champ de l'option tel qu'il
existe dans la base. [obligatoire]
Le troisième élément est le critère de filtre pour
évaluer l'expression (au choix : <, <=, =, >=, >, <>,
like, rlike). Par défaut : "<>".
Le quatrième élément est la valeur du critère. Par
défaut : "NULL".
Le cinquième élément est le titre d'un film, mais n'a rien
à faire là.
Plusieurs clauses peuvent être listées sous forme de tableau.
=> array("Id du groupe,g.id_groupe") // = AND g.id_groupe
<> 'NULL'
=> "Id du groupe,g.id_groupe,<>" // idem ci-dessus
=> array("Id du groupe,g.id_groupe,=,3") // AND g.id_groupe='3'
=> array("Id du groupe,g.id_groupe,>,3","Type, description,<>,ADMIN",
"Nom, nom") // AND g.id_groupe>'3' AND description <> 'ADMIN'
AND nom <> 'NULL'
"limit" : indique les clause de limitation à l'affichage.
=> "100" // affiche par lot de 100 enregistrements
=> "80,20" // affiche par lot de 80 enregistrements à partir
du 20ème
Pratique : 3 exemples
Premier exemple
Soit la requête suivante :
SELECT lm.module_titre, COUNT(DISTINCT t.user_id), SEC_TO_TIME(SUM(t.temps)/COUNT(distinct
t.user_id))
FROM formation f, list_module lm, tracking t
WHERE t.module_id=lm.module_id AND t.module_id = f.module_id AND t.user_id=f.user_id
AND f.avancement = 100
GROUP BY t.module_id
Celle-ci permet de connaître les temps moyen de réalisation des modules achevés.
Elle pourra être traduite dans QuickStats 2 sous la forme :
$r[] = array(
"access" => "ADMIN, TUTEUR",
"cat" => "Monitoring",
"subcat" => "Patrick",
"q" => "Temps moyen de réalisation des modules achevés",
"r" => "SELECT lm.module_titre as 'Unité',
COUNT(DISTINCT t.user_id) as 'Nb de stagiaires',
SEC_TO_TIME(SUM(t.temps)/COUNT(distinct t.user_id)) as 'Temps moyen'
FROM formation f, list_module lm, tracking t",
"static" => "t.module_id=lm.module_id AND t.module_id = f.module_id
AND t.user_id=f.user_id",
"options" => "Avancement, f.avancement, =, 100",
"groupby" => "t.module_id"
);
Explications :
- on indique que l'accès est limité à l'admin et aux tuteurs :
"access" => "ADMIN, TUTEUR",
- on indique qu'on ajoute une nouvelle entrée au tableau de questions :
$r[] = array(
- on ajoute ce qui nous permettra de retrouver la question dans la liste :
"cat" => "Monitoring", // catégorie
"subcat" => "Patrick", // sous-catégorie
"q" => "Temps moyen de réalisation des modules achevés",
// intitulé de la question
- on récupère ce qui se trouve avant le WHERE dans la requete originelle et on en profite pour nommer les colonnes ("as 'Temps moyen'", par exemple) :
"r" => "SELECT
lm.module_titre as 'Unité', // première colonne
COUNT(DISTINCT t.user_id) as 'Nb de stagiaires', // deuxieme
colonne
SEC_TO_TIME(SUM(t.temps)/COUNT(distinct t.user_id)) as 'Temps moyen'
// troisième colonne
FROM formation f, list_module lm, tracking t", // tables concernées
- on repère tout ce qui n'est pas amener à varier et qui se trouve après la clause WHERE :
"static" => "t.module_id=lm.module_id AND t.module_id = f.module_id AND t.user_id=f.user_id",
- on indique ce que l'on souhaite faire varier (ici, on veut un option "Avancement" basée sur le champs "formation.avancement", par défaut "égal à 100") :
"options" => "Avancement, f.avancement, =, 100",
- on indique qu'on veut regrouper par identifiant de module :
"groupby" => "t.module_id"
Deuxième exemple
Pour savoir combien de temps se sont connectés les membres :
SELECT nom, prenom, description, SEC_TO_TIME(SUM(time_cnx)) FROM
stat_connexion, type_membres tm
INNER JOIN membres m ON id_membres = id
WHERE tm.type=m.type AND description = 'Stagiaire'
GROUP BY id_membres
ORDER BY nom, tm.type
Cette requête pourra être tratduite dans QuickStats 2 de la façon suivante :
$r[] = array(
"noaccess" => "STAGIAIRE",
"cat" => "Monitoring",
"subcat" => "Patrick",
"q" => "Combien de temps se sont connectés les membres
? ",
"r" => "SELECT
nom,
prenom,
description as 'Type',
SEC_TO_TIME(SUM(time_cnx)) as 'Temps de connexion'
FROM stat_connexion, type_membres tm
INNER JOIN membres m ON id_membres = id",
"static" => "tm.type=m.type",
"options" => array("Type, description", "Nom, nom"),
"groupby" => "id_membres",
"orderby" => array("Nom, nom", "Type, tm.type",
"Temps, 'Temps de connexion'"),
"date" => "date_cnx",
"limit" => "30"
);
- on interdit l'accès aux stagiaires "noaccess"
=> "STAGIAIRE",
- on a ajouté une clause ORDER BY :
"orderby" => array("Nom, nom", "Type, tm.type",
"Temps, 'Temps de connexion'"),
- on a ajouté une clause de fitre de date sur le champs date_cnx
: "date" => "date_cnx",
- on a ajouté une clause de limitation qui affichera les résultats
par lots de 30 : "limit" => "30"
NB : l'option de trie basé sur le type : "options" => array("Type, description", "Nom, nom") est propre à e-carel.org, Ganesha ne stockant pas les temps de connexion des tuteurs/admins.
Troisième exemple
SELECT nom, prenom, SEC_TO_TIME(SUM(temps)) FROM tracking
INNER JOIN membres ON user_id = id
GROUP BY user_id
ORDER BY nom
Cette requête permet de connaître le temps de travail des stagiaires.
Traduit pour QuickStats, cela peut donner :
$r[] = array(
"version" => "2.1",
"cat" => "Monitoring",
"subcat" => "Patrick",
"q" => "Combien de temps les stagiaires ont-ils passé
à travailler ?",
"r" => "SELECT
nom,
prenom,
SEC_TO_TIME(SUM(temps)) as 'Temps de travail'
FROM tracking
INNER JOIN membres ON user_id = id",
"options" => "Nom, nom",
"date" => "date",
"groupby" => "user_id",
"orderby" => array("Nom, nom", "Temps,'Temps de
travail'")
);
- on a ajouté une limitation par numéro de version : "version"
=> "1.3" par exemple pour indiquer à partir de quelle
version de Ganesha cette question peut être utilisée.
Admettons que dans votre /addons/quickstats/config.inc.php,
vous ayez :
define("ADDON_QUICKSTATS_VERSIONCHECK", "2");
define("ADDON_QUICKSTATS_SORTVERSION", "GE");
Alors QuickStats interpretera de la façon suivante : "On affiche
la question si 2 est superieur ou égal à 1.3". La question
sera donc affichée.