Dashboard Configuration: Writing Query

Back to index

The query editor will let you edit and tune the SQL query to get data used in output.

It is just a simple text area where you can type or paste SQL.

As a default, the SQL will be applied to Moodle self database (1) or can be adressed to an accessory database (PostGreSQL) configured in global settings for this block.

Writing an appropriate will require some lighter or deeper knowledge of Moodle database structure (or of the accessory DB). This is why this blockk should be usually configured by a skilled Moodle administrator.

Some Important Rules To Comply For Writing Moodle Queries

• Only SELECT queries can be processed.
• Queries can perform any kind of JOIN.
• Some complex UNION or INTERSECT may cause perturbations in filter subquery processing. Result may be not guaranteed.
• All output field MUST be "aliased" if they are being used in renderers:

  SELECT
  firstname as fn,
  lastname as ln,
  userame as login,
  ...

Actually will on aloas names be used for referencing data in renderer configuration.

• Table names must be written including the Moodle table prefix (ex : mdl_user for users).

• If query uses aggregation functions (GROUP BY) over a set of columns, a composite primary key must be set as first field of the result, aggregating each column part of the grouping (or many result lines will be lost when converting to result arrays).

Example :

Let's use a query for showing accesses by month and by city:

  SELECT
  city AS city,
  MONTH(FROM_UNIXTIME(time)) AS month,
  count(*) AS access
  FROM
  mdl_log
  WHERE
  action = 'login'
  GROUP BY
  city,month

This query has two major issues :

• Month part of the date do not include year reference, Result will summarize all accesses of every January for all years. This first issue needs to be solved as follows:

   DATE_FORMAT(FROM_UNIXTIME(time), "%Y-%m")) as month

• The apparent primary key for output is city, thus only the last month data in result will be kept. This second issue will be resolved by the composite key:

   CONCAT(city, ' ', DATE_FORMAT(FROM_UNIXTIME(time, "%Y-%m"))) AS pk

added as first query column.