Klemi’s query language for searching members is simple, but supports several constructs to make more complex and fine-grained queries. The following fields are searched:
- full name
- first name
- last name
- role (name)
- group (name)
- group_id (integer)
- age (which calculates a date of birth search in the background)
The following guide is an adaptation of the scoped_search instructions
Keywords and phrases
The query language will separate keywords by whitespace, so
joe bloggs will be parsed as a list of two keywords,
bloggs. To make it a single phrase, add double quotes:
"joe bloggs". Search will look in all textual search fields for occurrences of every keyword; only records in which all keywords occur at least once will be returned. If a keyword is numeric (e.g
2010), numerical fields such as school_year will be searched as well. An expression that can be parsed as a date (e.g.
1999/01/01) will also cause a search in the date_of_birth field
The query language supports the logical AND OR and NOT operators, including some alternatives using symbols: & and && for the AND operator, | and || for the OR operator, and – and ! for the NOT operator. Note that by default, the AND operator is used to combine search keywords, so
joe bloggs equals
joe, bloggs and
joe && bloggs. and will therefore find everyone with the name “joe” and everyone with the name “bloggs”. Moreover, parentheses are supported to structure your logic:
Joe (2010 || 2011), NOT(Smith OR "Joe Bloggs") (find me a record with a school year that is either 2010 or 2011 matching the keyword “Joe” but not “Smith” and exclude any matching the keywords “Joe Bloggs”.
For true of false values, e.g. is_parent and is_adult use
is_parent = true
Searching a Range
Searching a range of values can be done wherever a field is numerical. It is achieved by combining two search queries in a normal
and statement. For example:
- to search for all those between 5 and 10 years, input
age > 5 and age < 10
- to search for all those born between 1st June 1986 and 1 January 1987, input
date_of_birth > 1986-06-01 and date_of_birth < 1987-01-01
The query language support some common operators to make a more fine-grained query. The operators semantics depend on the type of the field. Moreover, by providing a field, only the specified field will be searched for the given value. Some examples:
> 1999/09/01(will only search date fields),
name = "Joe Bloggs",
date_of_birth < 2009-01-01.
The full range of terms are given below:
|=||:eq||A case sensitive equality comparison for text fields, a normal equality comparison for numerical and temporal fields|
|!=, <>||:ne||The inverse/negation of the = operator|
|~||:like||Case insensitive occurrence/contains search for textual fields (i.e. LIKE/ILIKE)|
|!~||:unlike||The inverse/negation of the ~ operator (NOT LIKE/ILIKE)|
|>||:gt||Greater than. Only supported for numerical and temporal fields|
|<||:lt||Less than. Only supported for numerical and temporal fields|
|>=||:gte||Greater than or equals. Only supported for numerical and temporal fields|
|<=||:lte||Less than or equals. Only supported for numerical and temporal fields|
|^||:in||In operator. Allows comparing against a list of accepted values.|
|!^||:notin||Not in operator. Allows comparing against a list of rejected values.|
If no operator is given, the query builder will determine a default operator based on the field type, i.e.
~ for textual fields,
= for other field types.
Note on the “in” operator (^)
Where the attribute may have multiple values, e.g. ‘group’ or ‘tag’ note the difference between the
^ comparison operators.
tag = contact will return only those records which have only one tag named ‘contact’.
tag ^ contact will return all records tagged ‘contact’ whatever other tags they may (or may not) have. The same behaviour will obtain for groups and roles. So, for example, if you want everyone in the group called ‘Pathfinders’ make sure to input
group ^ pathfinders. If you want to let everyone who isn’t the minister know where you’re holding his birthday party 😉 try
role !^ minister
Search will automatically parse keywords for queries. Inputting queries with any of these keywords may lead to unexpected behaviour.
|and||:and||‘fish and chips’|
|or||:or||‘fish or chips’|
|not||:not||‘fish not chips’|
|before||:lt||‘fish before chips’|
|after||:gt||‘fish after chips’|
|at||:eq||‘fish at chips’|
When using a date to search a datetime field, the query is converted to return more logical results, e.g.
created_at = 2009-01-01 becomes
(created_at >= "2009-01-01 00:00:00" AND created_at < "2009-01-02 00:00:00")
Negative and NULL operators
Beyond the standard negative operators (!=, !~) you may want to find out when a field is empty. It is possible to check if a field has any value or is set to the NULL value, using the
null? operators, following the field name:
set? school_year(returns all records with school year set),
null? date_of_birth(returns all records with no date of birth set).
Two more methods may be useful:
null? role(returns all records with no currently assigned role).
We are working on developing custom queries to access information that is often required but cannot be expressed easily using the query language. Currently they are:
groups = none This returns all member records which do not currently belong to any group.
Date/datetime/timestamp fields can be compared with temporal or date values. The following syntaxes are supported to specify dates on the right hand side of an expression:
5 minutes ago,
1 hour ago,
7 days agoetc. (hours, minutes, days, weeks, months, years)
5 minutes from now,
1 hour from now,
7 days from nowetc. (hours, minutes, days, weeks, months, years)
- any format supported by
10th May 2017
The relative, right hand side of the expression is first converted into an absolute date, e.g. on the 10th May 2017, entering
created_at < "3 days ago" will first be converted to
created_at < 2017-05-07. Once relative dates are converted, it is compared numerically. All records with a lower
created_at date will be returned, older than 2017-05-07. Similarly to get all members born more than five years ago you would search
date_of_birth < "5 years ago" (although in that instance it would make more sense to use the
age field – i.e.
age > 5