Query language

The query language is simple, but supports several constructs to make more complex and fine-grained queries.

Keywords and phrases

The query language will separate keywords by whitespace, so full moon will be parsed as a list of two keywords. To make it a single phrase, add double quotes: "full moon". Scoped 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. 33 or -4.31), numerical fields will be searched as well. A string that can be parsed as a date will also cause a search in date and datetime fields.

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 full moon equals full, moon and full && moon. Moreover, parenthesis are supported to structure you logic:
police (car || uniform), NOT(costume OR "village people")

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: > 44, username = root, created < 2009-01-01.

Some notes:

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. The default operator for a field can be overridden in the search definition.

Keywords

Scoped 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 = 2009-01-01 becomes (created >= "2009-01-01 00:00:00" AND created < "2009-01-02 00:00:00")

NULL operators

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? deleted_at, null? error_messages. Note that this is only supported if the field is included in the search definition.

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.

SQL query generation

Note: scoped_search includes adapters for every DBMS to make sure that the generated SQL will return the same records for every DBMS type. Because of this, the generated SQL might differ among DBMSs.

In these example, the name and description field of the Person model are being searched by calling scoped_search :on => [:name, :description].

Single keyword. scoped_search:

SELECT * FROM people 
 WHERE (name LIKE '%scoped_search%' OR description LIKE '% scoped_search%')

Multiple keywords. Willem Wes scoped_search:

SELECT * FROM people
 WHERE (name LIKE '%Willem%' OR description LIKE '%Willem%')
   AND (name LIKE '%Wes%' OR description LIKE '%Wes%')
   AND (name LIKE '%scoped_search%' OR description LIKE '%scoped_search%')

Phrases, using quotes, are dealt with like single keywords, e.g. "Great plugin":

SELECT * FROM people 
 WHERE (name LIKE '%Great plugin%' OR description LIKE '%Great plugin%')