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 ...
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
_datetimefield, 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_fieldin Web config file.)
String fragment to find. Type in the text to find here.
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
This is an experimental feature. Performance is not guaranteed.