9.3. Research queries

Warning

Research queries use the database connection specified as DATABASES['research'] in the Django settings. If users are to see >1 database, this connection must have appropriate privileges, and read-only access, or research users might alter or destroy data.

Users can use raw SQL, or some automatic methods of building queries.

Todo

add screenshots

9.3.1. Query builder

The administrator tells the web site about one or several databases that are accessible via the database connection, including how tables should be linked on patient (via research IDs), within and across databases. The query builder then assists you to build simple SELECT / FROM / JOIN / WHERE queries in SQL, which can be run directly or edited further.

9.3.2. SQL

SQL is the standard language for asking questions of databases. CRATE provides an interactive way to build SQL queries, but you can also take those queries and extend them yourself, or just write your own.

Here, you can edit and save/load SQL queries, run them, and view/save the tabular output.

If you don’t know SQL but would like to learn, there are lots of online SQL tutorials. There are slight variations in syntax depending on the exact database type you are using, but the core language is standardized.

Tip

If you enter bad SQL, don’t worry. An error message will be generated, but you will do no harm (assuming your administrator has configured things correctly! See above).

9.3.3. Highlighting text in results

Here, you can specify text to highlight in different colours in the output. This makes it easy to spot words in long paragraphs.

For example, you can choose to highlight “insulin” in one colour and “glucose” in another. The settings you save here affect the display of results for any research queries that you run.

9.3.4. Results: table view

The “standard” way to view the results of a query is as a table in an HTML page. Each table row is a database row (record). Each table column is a database column (field). The table is paginated.

Highlighting will be applied. Long cells can be collapsed. Cells that are identical to the one above show ditto marks.

There is a Filter button through which you can turn the display of individual columns on or off.

9.3.5. Results: record view

This view may be better than the table view (see above) for detailed inspection of large records. One page (table) represents a single database record. The left-hand column contains database column (field) names, and the right-hand column contains values from the database.

9.3.6. Results: tab-separated values (TSV) file

This option offers the results of the currently selected query to download in tab-separated value (TSV) format.

TSV is like comma-separated value (CSV) format, but (as the name suggests) uses tabs rather than commas to separate columns (which is often helpful because humans use a lot of commas and not as many tabs when writing text). Both CSV and TSV files are readily accepted by standard spreadsheet problems such as LibreOffice Calc and Microsoft Excel.

Only the data is downloaded (compare Excel format, below).

9.3.7. Results: Excel (.XLSX) file

This option offers the results of the currently selected query to download in Office Open XML format, as used by Microsoft Excel.

The first spreadsheet contains the data; the second contains the SQL and the time of execution.