Query language

The query language is simple, but supports several constructs to make more complex and fine-grained queries. <h3>Keywords and phrases</h3> The query language will separate keywords by whitespace, so <code>full moon</code> will be parsed as a list of two keywords. To make it a single phrase, add double quotes: <code>"full moon"</code>. 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. <code>33</code> or <code>-4.31</code>), 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. <h3>Logical operators</h3> 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 <code>full moon</code> equals <code>full, moon</code> and <code>full && moon</code>. Moreover, parenthesis are supported to structure you logic: <code>police (car || uniform), NOT(costume OR "village people")</code> <h3>Comparison operators</h3> 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: <code>> 44</code>, <code>username = root</code>, <code>created < 2009-01-01</code>. Some notes: <table> <tr> <th>Operator </th> <th>Symbol </th> <th>Notes </th> </tr> <tr> <td> = </td> <td> :eq </td> <td> A case sensitive equality comparison for text fields, a normal equality comparison for numerical and temporal fields </td> </tr> <tr> <td> !=, <> </td> <td> :ne </td> <td> The inverse/negation of the = operator </td> </tr> <tr> <td> ~ </td> <td> :like </td> <td> Case insensitive occurrence/contains search for textual fields (i.e. LIKE/ILIKE) </td> </tr> <tr> <td> !~ </td> <td> :unlike </td> <td> The inverse/negation of the ~ operator (NOT LIKE/ILIKE) </td> </tr> <tr> <td> > </td> <td> :gt </td> <td> Greater than. Only supported for numerical and temporal fields </td> </tr> <tr> <td> < </td> <td> :lt </td> <td> Less than. Only supported for numerical and temporal fields </td> </tr> <tr> <td> >= </td> <td> :gte </td> <td> Greater than or equals. Only supported for numerical and temporal fields </td> </tr> <tr> <td> <= </td> <td> :lte </td> <td> Less than or equals. Only supported for numerical and temporal fields </td> </tr> </table> If no operator is given, the query builder will determine a default operator based on the field type, i.e. <code>~</code> for textual fields, <code>=</code> for other field types. The default operator for a field can be overridden in the [[search definition]]. When using a date to search a datetime field, the query is converted to return more logical results, e.g. <code>created = 2009-01-01</code> becomes <code>(created >= "2009-01-01 00:00:00" AND created < "2009-01-02 00:00:00")</code> <h3>NULL operators</h3> It is possible to check if a field has any value or is set to the NULL value, using the <code>set?</code> and <code>null?</code> operators, following the field name: <code>set? deleted_at</code>, <code>null? error_messages</code>. Note that this is only supported if the field is included in the [[search definition]]. <h2>SQL query generation</h2> 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 <code>name</code> and <code>description</code> field of the Person model are being searched by calling <code>scoped_search :on => [:name, :description]</code>. Single keyword. <code> scoped_search</code>: <pre><code>SELECT * FROM people WHERE (name LIKE '%scoped_search%' OR description LIKE '% scoped_search%') </code></pre> Multiple keywords. <code>Willem Wes scoped_search</code>: <pre><code>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%') </code></pre> Phrases, using quotes, are dealt with like single keywords, e.g. <code>"Great plugin"</code>: <pre><code>SELECT * FROM people WHERE (name LIKE '%Great plugin%' OR description LIKE '%Great plugin%') </code></pre> 2010-02-09T18:49:54-08:00 125161 query-language

37427

Query language

2009-08-31T03:41:16-07:00

15870