9.5. SQL helpers

This section helps you generate slightly more laborious queries.

9.5.1. Find text anywhere

This looks for text within all text fields. For example, if you elect to search for “paracetamol”, it may generate a query like:

SELECT rid
FROM anonymous_output.table1
WHERE (
    anonymous_output.table1.textfield1 LIKE '%paracetamol%'
    OR anonymous_output.table1.textfield2 LIKE '%paracetamol%'
    OR anonymous_output.table1.textfield3 LIKE '%paracetamol%'
    OR anonymous_output.table1.textfield4 LIKE '%paracetamol%'
)
UNION
SELECT rid
FROM anonymous_output.table2
WHERE (
    anonymous_output.table2.textfield1 LIKE '%paracetamol%'
    OR anonymous_output.table2.textfield2 LIKE '%paracetamol%'
    OR anonymous_output.table2.textfield3 LIKE '%paracetamol%'
    OR anonymous_output.table2.textfield4 LIKE '%paracetamol%'
    OR anonymous_output.table2.textfield5 LIKE '%paracetamol%'
)
-- UNION ...

Options include:

  • The field name containing the patient research ID (RID), which must be consistent across all the tables in the databases that your administrator has told.

  • An ID (RID) value; this is optional but allows you to restrict the query to a single patient (“find me all text containing this word for this patient”).

  • Use full-text indexing where available. This uses SQL such as MATCH (MySQL) or equivalent, rather than LIKE. This restricts you to whole words but makes the query much faster for those fields.

  • Minimum “width” of textual fields to include. You might not want to search all 25-character text fields; these are unlikely to be fields designed to contain comments by humans. This option allows you to restrict to long fields.

  • Include content from fields where found. This includes the text in the output, and makes the query look like:

    SELECT
    rid,
    'anonymous_output.table1' AS _table_name,
    'anonymous_output.table1.column1' AS _column_name,
    anonymous_output.table1.column1 AS _content
FROM anonymous_output.table1
WHERE anonymous_output.table1.column1 LIKE '%paracetamol%'
SELECT
    rid,
    'anonymous_output.table2' AS _table_name,
    'anonymous_output.table2.column1' AS _column_name,
    anonymous_output.table2.column1 AS _content
FROM anonymous_output.table2
WHERE anonymous_output.table2.column1 LIKE '%paracetamol%'
-- UNION ...

  • Include date/time where known. This adds a _datetime field, which may be informative for any tables for which CRATE knows an important date/time field.

    (This must be set up by the administrator; see default_date_field in Web config file.)

  • String fragment to find. Type in the text to find here.

Warning

These queries may be very slow to run.

Your administrator may choose to create a view combining the most important text fields in your database, or a site query, to make this process faster and easier.

9.5.2. Find drugs of a given type anywhere

This is an extension of Find text anywhere and most of the options are the same. The difference is that instead of looking for text like “citalopram”, you can search for drug classes such as “antidepressant”, and the resulting query will look for anything that matches its concept of an “antidepressant”. That includes matches for generic names and brand names.

The code used for “drug-finding” is from

specifically:

Warning

This is an experimental feature. Performance is not guaranteed.