Query

Dashboard block

this panel allows you to enter the query body and make some global choices on the dashbaord behaviour.

Data source

Usually the data source is the moodle database itself. By the way, there is given provision to dashboard some external data coming from an extra PostGreSQL remote database.

Display configuration

You may display:

Raw table form of the query output

Graphic representation of the query output

some summators calculated on output data.

In some circonstances you may choose to display additionnaly:

The final form of the query

The filters queries that will extract the filter modalities

Query input

Here you can enter the full body of the SQL query, according to some writing rules:

Mandatory rules

Query MUST be a select. Any other SQL keywords will be blocked.

All output fields must be aliased (e.g.: “username as un”) to allow correct output mapping on the displays and filters.

UNION or INTERSECT will have big chances not to work.

Additional rules

Moodle tables can be written according to the moodle development standards as {tablename} to allow moodle automatically add the prefix. If no braces are used, then write the complete name of the table as it is stored into the database schema.

If you use filters, add the <FILTERS> tag:

as WHERE clause replacement if the original query has no WHERE

at the end of the WHERE statement in other cases.

If you use dynamic user parameters, add the <PARAMS> tag in the WHERE clause of the query.

Output result rotation (Pro option only)

A SQL result cannot have a variable set of output columns, by construction of the SQL engine. Thus SQL is not a straight method to query matricial data. Data result rotation is a way to transform a flat SELECT output into a data matrix with two dynamic dimensions. this feature is usefull when getting query output that provides multiple data series in one result, and you'll need to separate each serie in a graph. Rotating results will use a defined “pivot” column for defining the dynamically generated output columns. The data will be rearranged to keep the other dimensions consistant, while distributing the actual data in the final matrix.

Example

Say you are willing to extract the numer of users by authentication method AND by country:

SELECT
CONCAT(country, '-', auth) as pkey,
country as cn,
auth as auth,
COUNT(*) as num
FROM
{user}
GROUP BY
CONCAT(country, '-', auth)

In standard SQL this query would provide you a flat output result with one primarykey, two dimensional attributes and one data column:

pkey	cn	auth	num
FR-manual	FR	manual	45
FR-mnet	FR	mnet	4
FR-cas	FR	cas	1754
EN-manual	EN	manual	22
EN-mnet	EN	mnet	23
EN-cas	EN	cas	98
CH-manual	CH	manual	12
CH-mnet	CH	mnet	5
CH-cas	CH	cas	302

Afin de pouvoir afficher un graphe en barres sur une série de données par pays, il est nécessaire pour alimenter l'entrée du graphe, de produire une table transformée de la sortie directe de requête. La table à obtenir est :

pkey	auth	FR	EN	CH
manual	manual	45	22	12
mnet	mnet	4	23	5
cas	cas	1754	98	302

qui nous permette de fournir des séries “par méthode d'authentification” sur les entrées pays.

Nous pouvons exprimer alors la méthode du pivot comme :

“ pivoter la colonne cn (country) autour du pivot “auth” pour re répartir les données de la colonne num ”.

Le pivot nécessite de retraiter les clef primaires pour qu'elles correspondent à la nouvelle organisation de la table de sortie.

Crédits

Valéry Frémaux (valery@activeprolearn.com)- Développements et documentation

Florence Labord (florence@activeprolearn.com) - documentation

Revenir à l'index du guide de configuration - Retour à l'index du composant - Aller au catalogue de requêtes génériques - Revenir à l'index des plugins - Revenir au catalogue