[id='creating_ickle_queries-{context}']
= Creating Ickle Queries
{brandname} provides an Ickle query language that lets you create relational and full-text queries.

== Ickle Query Example

To use the API, first obtain a QueryFactory to the cache and then call the `.create()` method, passing in the string to use in the query.
Each `QueryFactory` instance is bound to the same `Cache` instance as the `Search`, but it is otherwise a stateless and thread-safe object that can be used for creating multiple queries in parallel.

For instance:

[source,java,tile="Using Ickle"]
----
// Remote Query, using protobuf
QueryFactory qf = org.infinispan.client.hotrod.Search.getQueryFactory(remoteCache);
Query<Transaction> q = qf.create("from sample_bank_account.Transaction where amount > 20");

// Embedded Query using Java Objects
QueryFactory qf = org.infinispan.query.Search.getQueryFactory(cache);
Query<Transaction> q = qf.create("from org.infinispan.sample.Book where price > 20");

// Execute the query
QueryResult<Book> queryResult = q.execute();
----

[NOTE]
====
A query will always target a single entity type and is evaluated over the contents of a single cache. Running a query over multiple caches or creating queries that target several entity types (joins) is not supported.
====

Executing the query and fetching the results is as simple as invoking the `execute()` method of the `Query` object. Once
executed, calling `execute()` on the same instance will re-execute the query.

[discrete]
== Pagination

You can limit the number of returned results by using  the `Query.maxResults(int maxResults)`. This can be used in
conjunction with `Query.startOffset(long startOffset)` to achieve pagination of the result set.

[source,java]
----
// sorted by year and match all books that have "clustering" in their title
// and return the third page of 10 results
Query<Book> query = queryFactory.create("FROM org.infinispan.sample.Book WHERE title like '%clustering%' ORDER BY year").startOffset(20).maxResults(10)
----

[discrete]
== Number of Hits

The `QueryResult` object has the `.hitCount()` method to return the total number of results of the query, regardless
of any pagination parameter. The hit count is only available for indexed queries for performance reasons.

[discrete]
== Iteration

The `Query` object has the `.iterator()` method to obtain the results lazily. It returns an instance of `CloseableIterator` that must be closed after usage.

[NOTE]
====
The iteration support for Remote Queries is currently limited, as it will first fetch all entries to the client
before iterating.
====

[discrete]
== Named Query Parameters

Instead of building a new Query object for every execution it is possible to include named parameters in the query which
can be substituted with actual values before execution. This allows a query to be defined once and be efficiently
executed many times. Parameters can only be used on the right-hand side of an operator and are defined when the query is
created by supplying an object produced by the `org.infinispan.query.dsl.Expression.param(String paramName)` method to
the operator instead of the usual constant value. Once the parameters have been defined they can be set by invoking either
`Query.setParameter(parameterName, value)` or `Query.setParameters(parameterMap)` as shown in the examples below.
⁠
[source,java,tile="Using Named Parameters"]
----
QueryFactory queryFactory = Search.getQueryFactory(cache);
// Defining a query to search for various authors and publication years
Query<Book> query = queryFactory.create("SELECT title FROM org.infinispan.sample.Book WHERE author = :authorName AND publicationYear = :publicationYear").build();

// Set actual parameter values
query.setParameter("authorName", "Doe");
query.setParameter("publicationYear", 2010);

// Execute the query
List<Book> found = query.execute.list();
----

Alternatively, you can supply a map of actual parameter values to set multiple parameters at once:
⁠
[source,java,title="Setting multiple named parameters at once"]
----
Map<String, Object> parameterMap = new HashMap<>();
parameterMap.put("authorName", "Doe");
parameterMap.put("publicationYear", 2010);

query.setParameters(parameterMap);
----

[NOTE]
====
A significant portion of the query parsing, validation and execution planning effort is performed during the first
execution of a query with parameters. This effort is not repeated during subsequent executions leading to better
performance compared to a similar query using constant values instead of query parameters.
====

[[query_ickle]]
== Ickle Query Language Parser Syntax

The Ickle query language is small subset of the link:https://en.wikipedia.org/wiki/Java_Persistence_Query_Language[JPQL] query language, with some extensions for full-text.

The parser syntax has some notable rules:

* Whitespace is not significant.
* Wildcards are not supported in field names.
* A field name or path must always be specified, as there is no default field.
* `&&` and `||` are accepted instead of `AND` or `OR` in both full-text and JPA predicates.
* `!` may be used instead of `NOT`.
* A missing boolean operator is interpreted as `OR`.
* String terms must be enclosed with either single or double quotes.
* Fuzziness and boosting are not accepted in arbitrary order; fuzziness always comes first.
* `!=` is accepted instead of `<>`.
* Boosting cannot be applied to `>`,`>=`,`<`,`<=` operators. Ranges may be used to achieve the same result.

[discrete]
=== Filtering operators

Ickle support many filtering operators that can be used for both indexed and non-indexed fields.

[options="header"]
|==============================================================================
| Operator | Description | Example
| in | Checks that the left operand is equal to one of the elements from the Collection of values given as argument.
|FROM Book WHERE isbn IN ('ZZ', 'X1234')
| like | Checks that the left argument (which is expected to be a String) matches a wildcard pattern that follows the JPA rules.| FROM Book WHERE title LIKE '%Java%'
|=| Checks that the left argument is an exact match of the given value         | FROM Book WHERE name = 'Programming Java'
|!=| Checks that the left argument is different from the given value            | FROM Book WHERE language != 'English'
|>| Checks that the left argument is greater than the given value.             | FROM Book WHERE price > 20
|>=| Checks that the left argument is greater than or equal to the given value. | FROM Book WHERE price >= 20
|<| Checks that the left argument is less than the given value.                | FROM Book WHERE year < 2012
|<=| Checks that the left argument is less than or equal to the given value.   | FROM Book WHERE price  <= 50
|between| Checks that the left argument is between the given range limits.  | FROM Book WHERE price BETWEEN 50 AND 100
|==============================================================================

[discrete]
=== Boolean conditions

Combining multiple attribute conditions with logical conjunction (`and`) and disjunction (`or`) operators in order to
create more complex conditions is demonstrated in the following example. The well known operator precedence rule for
boolean operators applies here, so the order of the operators is irrelevant. Here `and`
operator still has higher priority than `or` even though `or` was invoked first.

[source,sql]
----
# match all books that have "Data Grid" in their title
# or have an author named "Manik" and their description contains "clustering"

FROM org.infinispan.sample.Book WHERE title LIKE '%Data Grid%' OR author.name = 'Manik' AND description like '%clustering%'
----

Boolean negation has highest precedence among logical operators and applies only to the next simple attribute condition.

[source,sql]
----
# match all books that do not have "Data Grid" in their title and are authored by "Manik"
FROM org.infinispan.sample.Book WHERE title != 'Data Grid' AND author.name = 'Manik'
----

[discrete]
=== Nested conditions
Changing the precedence of logical operators is achieved with parenthesis:

[source,sql]
----
# match all books that have an author named "Manik" and their title contains
# "Data Grid" or their description contains "clustering"
FROM org.infinispan.sample.Book WHERE author.name = 'Manik' AND ( title like '%Data Grid%' OR description like '% clustering%')
----

[discrete]
=== Selecting attributes
In some use cases returning the whole domain object is overkill if only a small subset of the attributes are actually
used by the application, especially if the domain entity has embedded entities. The query language allows you to specify
a subset of attributes (or attribute paths) to return - the projection. If projections are used then the `QueryResult.list()`
will not return the whole domain entity but will return a `List` of `Object[]`, each slot in the array corresponding to
a projected attribute.

[source,sql]
----
# match all books that have "Data Grid" in their title or description
# and return only their title and publication year
SELECT title, publicationYear FROM org.infinispan.sample.Book WHERE title like '%Data Grid%' OR description like '%Data Grid%'
----

[discrete]
=== Sorting
Ordering the results based on one or more attributes or attribute paths is done with the `ORDER BY` clause. If multiple sorting criteria
are specified, then the order will dictate their precedence.

[source,sql]
----
# match all books that have "Data Grid" in their title or description
# and return them sorted by the publication year and title
FROM org.infinispan.sample.Book WHERE title like '%Data Grid%' ORDER BY publicationYear DESC, title ASC
----

[discrete]
=== Grouping and Aggregation

{brandname} has the ability to group query results according to a set of grouping fields and construct aggregations of
the results from each group by applying an aggregation function to the set of values that fall into each group.
Grouping and aggregation can only be applied to projection queries (queries with one or more field in the SELECT clause).

The supported aggregations are: `avg`, `sum`, `count`, `max`, and `min`.

The set of grouping fields is specified with the `GROUP BY` clause and the order used for defining grouping fields is
not relevant. All fields selected in the projection must either be grouping fields
or else they must be aggregated using one of the grouping functions described below. A projection field can be
aggregated and used for grouping at the same time. A query that selects only grouping fields but no aggregation fields
is legal.
⁠
Example: Grouping Books by author and counting them.
[source,sql]
----
SELECT author, COUNT(title) FROM org.infinispan.sample.Book WHERE title LIKE '%engine%' GROUP BY author
----

[NOTE]
====
A projection query in which all selected fields have an aggregation function applied and no fields are used for
grouping is allowed. In this case the aggregations will be computed globally as if there was a single global group.
====

[discrete]
=== Aggregations
You can apply the following aggregation functions to a field:

.Index merge attributes
[%header,cols=2*]
|===
|Aggregation function
|Description

|`avg()`
| Computes the average of a set of numbers. Accepted values are primitive numbers and instances of `java.lang.Number`. The result is represented as `java.lang.Double`. If there are no non-null values the result is `null` instead.

|`count()`
|Counts the number of non-null rows and returns a `java.lang.Long`. If there are no non-null values the result is `0` instead.

|`max()`
|Returns the greatest value found. Accepted values must be instances of `java.lang.Comparable`. If there are no non-null values the result is `null` instead.

|`min()`
|Returns the smallest value found. Accepted values must be instances of `java.lang.Comparable`. If there are no non-null values the result is `null` instead.

|`sum()`
|Computes the sum of a set of Numbers. If there are no non-null values the result is `null` instead. The following table indicates the return type based on the specified field.

|===

.Table sum return type
|===
|Field Type |Return Type

|Integral (other than BigInteger)
|Long

|Float or Double
|Double

|BigInteger
|BigInteger

|BigDecimal
|BigDecimal
|===

[discrete]
=== Evaluation of queries with grouping and aggregation

Aggregation queries can include filtering conditions, like usual queries. Filtering can be performed in two stages: before
and after the grouping operation. All filter conditions defined before invoking the `groupBy()` method will be applied
before the grouping operation is performed, directly to the cache entries (not to the final projection). These filter
conditions can reference any fields of the queried entity type, and are meant to restrict the data set that is going to
be the input for the grouping stage. All filter conditions defined after invoking the `groupBy()` method will be applied to
the projection that results from the projection and grouping operation. These filter conditions can either reference any
of the `groupBy()` fields or aggregated fields. Referencing aggregated fields that are not specified in the select clause
is allowed; however, referencing non-aggregated and non-grouping fields is forbidden. Filtering in this phase will
reduce the amount of groups based on their properties. Sorting can also be specified similar to usual queries. The
ordering operation is performed after the grouping operation and can reference any of the `groupBy()` fields or aggregated
fields.

== Using Full-Text Search

[discrete]
=== Fuzzy Queries

To execute a fuzzy query add `~` along with an integer, representing the distance from the term used, after the term.
For instance

[source,sql,tile="Fuzzy Queries in Ickle"]
----
FROM sample_bank_account.Transaction WHERE description : 'cofee'~2
----

[discrete]
=== Range Queries

To execute a range query define the given boundaries within a pair of braces, as seen in the following example:

[source,sql,tile="Range queries with Ickle"]
----
FROM sample_bank_account.Transaction WHERE amount : [20 to 50]
----

[discrete]
=== Phrase Queries

A group of words can be searched by surrounding them in quotation marks, as seen in the following example:

[source,sql,tile="Phrase queries with Ickle"]
----
FROM sample_bank_account.Transaction WHERE description : 'bus fare'
----

[discrete]
=== Proximity Queries

To execute a proximity query, finding two terms within a specific distance, add a `~` along with the distance after the phrase.
For instance, the following example will find the words canceling and fee provided they are not more than 3 words apart:

[source,sql,tile="Proximity queries with Ickle"]
----
FROM sample_bank_account.Transaction WHERE description : 'canceling fee'~3
----

[discrete]
=== Wildcard Queries

To search for "text" or "test", use the `?` single-character wildcard search:

[source,sql,tile="Single-character wildcard queries with Ickle"]
----
FROM sample_bank_account.Transaction where description : 'te?t'
----

To search for "test", "tests", or "tester", use the `*` multi-character wildcard search:

[source,sql,tile="Multi-character wildcard queries with Ickle"]
----
FROM sample_bank_account.Transaction where description : 'test*'
----

[discrete]
=== Regular Expression Queries

Regular expression queries can be performed by specifying a pattern between `/`. Ickle uses Lucene’s regular expression syntax, so to search for the words `moat` or `boat` the following could be used:

[source,sql,tile="Regular Expression queries with Ickle"]
----
FROM sample_library.Book  where title : /[mb]oat/
----

[discrete]
=== Boosting Queries

Terms can be boosted by adding a `^` after the term to increase their relevance in a given query, the higher the boost factor the more relevant the term will be. For instance to search for titles containing beer and wine with a higher relevance on beer, by a factor of 3, the following could be used:

[source,sql,tile="Boosting queries with Ickle"]
----
FROM sample_library.Book WHERE title : beer^3 OR wine
----
