Last updated 5 May 2015. Created on 22 July 2014.
Edited by donutdan4114, John Carbone. Log in to edit this page.

Documentation for the QUI module.

Sections


Creating a New Report

After installation and permissions configuration, visit admin/reports/qui and then click "Create a report now" (if you've never created one), or use the "New Report" tab if you already have some reports built.

Give the report a name. Other options will appear after a name is given.

Give the report an optional description.

From the Query Builder dialog you can start to build your query. If you've declared multiple databases (such as a legacy database) in your settings.php, QUI will pick up on it and show you the option to build queries against your other database(s). Otherwise, select your base table. Now you can begin adding joins, conditions, expressions, etc.

Example Report & Report Edit Screen Overview

In this example we're going to pull usernames, user ids, the names of any roles a user has and limit the list to only our customer's email domain. Although Views can also accomplish this, it's just an example. ;) This example can be imported into your own QUI install by copying this gist http://tinyurl.com/knu4fht and pasting it into your install at admin/reports/qui/import (Needs updating, export does not use json format) This example can also be seen here: https://www.drupal.org/files/qui_report_screen_0.png

First we would select the "from table" as users. You'll notice that the interface updates via ajax to filter out irrelevant options based on your selection.

You'll notice that the selections are via an autocomplete field. This can be changed back to a normal select list in the settings tab at admin/reports/qui/settings Revision and cache tables are excluded by default but they can be exposed on the settings page as well. Gzip compression can also be enabled and disabled there. Caution: if you leave an unfinished query without saving, your work will be lost!

Let's start with the basics and we'll check out some advanced features at the end of this example. First we'll add a left join on users_roles with the left column as Users (uid) and the right column as Users Roles (uid). We would then add a second (inner) join on the role table with the left column of Role (rid) and the right column on Users Roles (rid).

Now we can add our condition for filtering on the email domain by adding a "Where" column of Users (mail) with the like operator of %customer-domain.com. Here you could also put in a Where snippet or use Having or a having snippet.

To get the roles grouped together per user we can add an "Expression" with an alias of roles and the expression of GROUP_CONCAT(role.name).

To limit the columns let's open the columns fieldset. Only tables that you've selected will be listed in the columns area. For our query we're going to select only mail and name under Users, and nothing under Users Roles or Role.

Finally, we'll set some of the "Extras". Here we could limit the results by using the "Range" settings, but we don't need that one for this example. We do need to use the "Group By" though so we'll set that to group on Users (uid). Lastly we'll set a "Sort" of Users (mail) with a direction of Ascending.

When we hit "Preview" we'll see the query executed as well as the result set. By default the preview is limited to 5 results, but you can change that setting at admin/reports/qui/settings you can use 0 for unlimited results. Again, make sure you save your work before leaving the screen! Click "Save" to save your work.

QUI also comes with some great advanced features. Under the Advanced tab you'll see cache lifetime options, the ability to access a report via a special url with tokens, report tagging for organizing groups of reports, allowed roles to limit access to individual reports, and allowed formats the report can be viewed in. QUI currently ships with the following formats: HTML Table, CSV, TSV, pipe, JSON, and serialized PHP; but developers can extend the module using HOOK_qui_formatters_info().

Formatters

QUI Formatters are a way to format report data. QUI ships with several pre-defined formats that people commonly use to show report data: HTML Table, CSV, TSV, pipe, JSON, and serialized PHP. Each formatter is essentially a theme function that can be hooked into and input/output can be altered.

You can create your own formatters by implementing hook_qui_formatters_info.

Formatters can be returned in plain text, HTML, or as a downloadable file. It is important to note that any data you return raw could contain dangerous JS code (XSS). You must take care when displaying your query results on the site, to sanitize the row output using check_plain. The HTML Table format is currently run through check_plain before it is output, so this only applies to formats like JSON or CSV.

Displaying a Report in a Block or in a Content Type

Reports are entities which can be referenced. If you want a report in a Bean or anything else fieldable, just create an entity reference for Reports. Once you do, you can select any Report you've built in this field and it will be rendered. This creates interesting possibilities where a Report could display data in a block or on the dashboard with data from a legacy database or a non-drupal database. All Report data for tables is currently run through check_plain though, until filter formats are supported in QUI. This means that all HTML is escaped before presentation (for safety).

UI Tips

Subselects are supported. You can subselect another QUI Report by creating a new condition with an IN operator, and setting the value as report: MACHINE_NAME.

Using QUI with Migrate

QUI is a nice pairing with the Migrate module and can be used to build the queries Migrate needs. Since alternate databases are are supported as well, QUI can take some of the pain out of writing migrations. Just build a query in the UI then pass it to migrate in your constructor. By declaring your source database in your settings.php, QUI will display the option to select it in the Query Builder.

    // in your migrate class constructor
    $report = qui_report_load_by_machine_name('MACHINE_NAME');
    $query = $report->buildQuery();
    $this->source = new MigrateSourceSQL($query);

Hooks

There are two main ways to alter a report query.

You can implement hook_qui_report_query_alter(SelectQuery &$query, QuiReport $report) which will allow you to alter the $query object, and you can check which report you're running which the $report object.

For modifying a specific query, you can use the more specific hook_qui_report_machine_name_query_alter(SelectQuery &$query, QuiReport $report) where "machine_name" is the machine_name of the report you want to target.

In these hooks, you can modify conditions, joins, selects, expressions, etc.. By implementing the hook, a warning will be shown on the reports dashboard that the query is overridden in code.

Custom Coding

QUI Reports are entities which can be easily loaded and run from within your custom module.

<?php
  $report_id = 4; // ID of the report to load. You can also load by machine_name if that's what you're into.
  $report = qui_report_load($report_id); // You now have a QuiReport object.
  $report = qui_report_load_by_machine_name('some_name'); // Alternate method of loading a report.
  // You can make changes to the report object here, for example...
  // Changing a condition to be dynamic.
  $report->conditions[0]['value'] = check_plain($_GET['uid']); // A uid parameter is passed in the URL to make this query dynamic.
  $results = $report->runQuery(); // Running the query is very easy.
  // Now you can display the results however you want!
  // You can also get the SelectQuery object from the report to alter that.
  $query = $report->buildQuery();
  $query->condition('table', 'value');
  $results = $query->execute()->fetchAll();
?>

Here are a couple more complete examples of using QUI reports in your code.

<?php
// Example: Get a report query and immediately use the results.
$report = qui_report_load_by_machine_name('roles_example');
/* Alternatively you could also use the id of the report to load it, 
 * using qui_report_load(ID) or even qui_report_load_multiple(array(ID1, ID2))
 * qui_report_load and qui_report_load_multiple also support entity conditions
 * @ see entity_get_controller()
 */

$result = $report->runQuery();
foreach ($result as $row) {
  // Do stuff with the results of the query
}
?>
<?php
// Example: Get the report, build the query, change some settings 
// or conditions then execute it.
$report =  qui_report_load_by_machine_name('roles_example');
$query = $report->buildQuery();
$query->condition('YOURCOLUMN', 'YOURVALUE');
$result = $query->execute()->fetchAll();
foreach ($result as $row) {
  // Do stuff with the results of the query
}
?>

Drush

QUI ships with Drush integration for listing and viewing QUI Reports. Reports can be loaded by name and shown in whatever formatters are available. Some options are also available for limits, ordering and grouping the output from within Drush. See screenshot or use drush help qui-list or drush help qui-view to learn more.

Maintaining Reports as Exportables

@todo explain hook_default_qui_reports and how to maintain reports in code. For now, the module's readme should help with this.

AttachmentSize
List Page118.63 KB
Query Builder Screen601.46 KB
Settings Page209.58 KB
JSON Output32.7 KB
Drush224.89 KB

Looking for support? Visit the Drupal.org forums, or join #drupal-support in IRC.