Clone this wiki locally
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.
-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.
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")
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:
username = root,
created < 2009-01-01.
|=||: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.
Scoped 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 = 2009-01-01 becomes
(created >= "2009-01-01 00:00:00" AND created < "2009-01-02 00:00:00")
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:
null? error_messages. Note that this is only supported if the field is included in the search definition.
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
description field of the Person model are being searched by calling
scoped_search :on => [:name, :description].
SELECT * FROM people WHERE (name LIKE '%scoped_search%' OR description LIKE '% scoped_search%')
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.
SELECT * FROM people WHERE (name LIKE '%Great plugin%' OR description LIKE '%Great plugin%')