Query language

The query language is simple, but supports several constructs. The query language parser will split the search string into keywords. It will build a query that matches any record that has at least one occurrence of all of these keywords (using the SQL AND operator) in any of the search fields (using the SQL OR operator. The query language supports some operators to alter this behavior.

The searching is performed with the SQL LIKE operator, or ILIKE in PostgreSQL. Searching therefore is case insensitive, but this can be different in some DBMS’s configurations. For example, the MySQL collation settings can have an impact on this.

Supported constructs and keywords:

• words: some search keywords
• phrases: "a single search phrase"
• negation: "look for this" -"but do not look for this phrase and this" -word
• OR words/phrases: "Hello World" OR "Hello Moon"
• dates: mm/dd/yyyy, dd/mm/yyyy, yyyy/mm/dd, yyyy-mm-dd
• date ranges: > date, >= date, < date, <= date, date TO date. Examples: > 09/27/1980, < 980-09-27

Below, the usage of these constructs is explained, including the SQL that will be generated when using them.

Simple keyword search

In these example, the name and description field of the Person model are being searched by calling searchable_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’)

You can create phrases including spaces by using quotes. "Great plugin":

SELECT * FROM people 

 WHERE (name LIKE ‘Great plugin’ OR description LIKE ‘Great plugin’)

Operators

The query language supports the OR and NOT operator.

Date queries