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)
• tag
• date_of_birth
• age (which calculates a date of birth search in the background)
• school_year
• is_parent
• is_adult
• current
• created_at
• updated_at

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, joe and 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

Logical operators

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

Comparison operators

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:

Operator	Symbol	Notes
=	: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 = and ^ 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

Keywords

Search will automatically parse keywords for queries. Inputting queries with any of these keywords may lead to unexpected behaviour.

Keyword	Symbol	Example
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 set? and 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).

Custom Queries

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.

Temporal comparisons

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:

• today, yesterday, tomorrow
• 5 minutes ago, 1 hour ago, 7 days ago etc. (hours, minutes, days, weeks, months, years)
• 5 minutes from now, 1 hour from now, 7 days from now etc. (hours, minutes, days, weeks, months, years)
• any format supported by DateTime.parse (docs), e.g. 2017-05-10, 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