////
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements.  See the NOTICE file
distributed with this work for additional information
regarding copyright ownership.  The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License.  You may obtain a copy of the License at

https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied.  See the License for the
specific language governing permissions and limitations
under the License.
////

Criteria is an advanced way to query that uses a Groovy builder to construct potentially complex queries. It is a much better approach than building up query strings using a `StringBuilder`.

Criteria can be used either with the link:../api/org/grails/datastore/gorm/GormEntity.html#createCriteria()[createCriteria()] or link:../api/org/grails/datastore/gorm/GormEntity.html#withCriteria(groovy.lang.Closure)[withCriteria(closure)] methods.

The builder uses Hibernate's Criteria API. The nodes on this builder map the static methods found in the https://docs.jboss.org/hibernate/orm/5.6/javadocs/org/hibernate/criterion/Restrictions.html[Restrictions] class of the Hibernate Criteria API. For example:

[source,groovy]
----
def c = Account.createCriteria()
def results = c {
    between("balance", 500, 1000)
    eq("branch", "London")
    or {
        like("holderFirstName", "Fred%")
        like("holderFirstName", "Barney%")
    }
    maxResults(10)
    order("holderLastName", "desc")
}
----

This criteria will select up to 10 `Account` objects in a List matching the following criteria:

* `balance` is between 500 and 1000
* `branch` is 'London'
* `holderFirstName` starts with 'Fred' or 'Barney'

The results will be sorted in descending order by `holderLastName`.

If no records are found with the above criteria, an empty List is returned.


==== Conjunctions and Disjunctions


As demonstrated in the previous example you can group criteria in a logical OR using an `or { }` block:

[source,java]
----
or {
    between("balance", 500, 1000)
    eq("branch", "London")
}
----

This also works with logical AND:

[source,java]
----
and {
    between("balance", 500, 1000)
    eq("branch", "London")
}
----

And you can also negate using logical NOT:

[source,java]
----
not {
    between("balance", 500, 1000)
    eq("branch", "London")
}
----

All top level conditions are implied to be AND'd together.


==== Querying Associations


Associations can be queried by having a node that matches the property name. For example say the `Account` class had many `Transaction` objects:

[source,groovy]
----
class Account {
    ...
    static hasMany = [transactions: Transaction]
    ...
}
----

We can query this association by using the property name `transactions` as a builder node:

[source,java]
----
def c = Account.createCriteria()
def now = new Date()
def results = c.list {
    transactions {
        between('date', now - 10, now)
    }
}
----

The above code will find all the `Account` instances that have performed `transactions` within the last 10 days.
You can also nest such association queries within logical blocks:

[source,java]
----
def c = Account.createCriteria()
def now = new Date()
def results = c.list {
    or {
        between('created', now - 10, now)
        transactions {
            between('date', now - 10, now)
        }
    }
}
----

Here we find all accounts that have either performed transactions in the last 10 days OR have been recently created in the last 10 days.


==== Querying with Projections


Projections may be used to customise the results. Define a "projections" node within the criteria builder tree to use projections. There are equivalent methods within the projections node to the methods found in the Hibernate https://docs.jboss.org/hibernate/orm/5.6/javadocs/org/hibernate/criterion/Projections.html[Projections] class:

[source,java]
----
def c = Account.createCriteria()

def numberOfBranches = c.get {
    projections {
        countDistinct('branch')
    }
}
----

When multiple fields are specified in the projection, a List of values will be returned. A single value will be returned otherwise.


==== Transforming Projection Results


If the raw value or simple object array returned by the criteria method doesn't suit your needs, the result can be transformed with a https://docs.jboss.org/hibernate/orm/5.6/javadocs/org/hibernate/transform/ResultTransformer.html[ResultTransformer]. Let's say we want to transform the criteria results into a Map so that we can easily reference values by key:

[source,java]
----
def c = Account.createCriteria()

def accountsOverview = c.get {
    resultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP)
    projections {
        sum('balance', 'allBalances')
        countDistinct('holderLastName', 'lastNames')
    }
}

// accountsOverview.allBalances
// accountsOverview.lastNames
----

Note that we've added an alias to each projection as an additional parameter to be used as the key. For this to work, all projections must have aliases defined, otherwise the corresponding map entry will not be built.

We can also transform the result into an object of our choosing via the https://docs.jboss.org/hibernate/orm/5.6/javadocs/org/hibernate/transform/Transformers.html#aliasToBean-java.lang.Class-[Transformers.aliasToBean()] method. In this case, we'll transform it into an `AccountsOverview`:

[source,java]
----
class AccountsOverview {
    Number allBalances
    Number lastNames
}
----

[source,java]
----
def c = Account.createCriteria()

def accountsOverview = c.get {
    resultTransformer(Transformers.aliasToBean(AccountsOverview))
    projections {
        sum('balance', 'allBalances')
        countDistinct('holderLastName', 'lastNames')
    }
}

// accountsOverview instanceof AccountsOverview
----

Each alias must have a corresponding property or explicit setter on the bean otherwise an exception will be thrown.


==== SQL Projections


The criteria DSL provides access to Hibernate's SQL projection API.

[source,java]
----
// Box is a domain class...
class Box {
    int width
    int height
}
----

[source,java]
----
// Use SQL projections to retrieve the perimeter and area of all of the Box instances...
def c = Box.createCriteria()

def results = c.list {
    projections {
      sqlProjection '(2 * (width + height)) as perimeter, (width * height) as area', ['perimeter', 'area'], [INTEGER, INTEGER]
    }
}
----

The first argument to the `sqlProjection` method is the SQL which defines the projections.  The second argument is a list of
Strings which represent column aliases corresponding to the projected values expressed in the SQL.  The third argument
is a list of `org.hibernate.type.Type` instances which correspond to the projected values expressed in the SQL.  The API
supports all `org.hibernate.type.Type` objects but constants like INTEGER, LONG, FLOAT etc. are provided by the DSL which
correspond to all of the types defined in `org.hibernate.type.StandardBasicTypes`.

Consider that the following table represents the data in the
`BOX` table.
[format="csv", options="header"]
|===

width,height
2,7
2,8
2,9
4,9
|===

The query above would return results like this:

[source,groovy]
----
[[18, 14], [20, 16], [22, 18], [26, 36]]
----

Each of the inner lists contains the 2 projected values for each `Box`, perimeter and area.

NOTE: Note that if there are other references in scope wherever your criteria query is expressed that have names that conflict
with any of the type constants described above, the code in your criteria will refer to those references, not the type
constants provided by the DSL.  In the unlikely event of that happening you can disambiguate the conflict by referring
to the fully qualified Hibernate type.  For example `StandardBasicTypes.INTEGER` instead of `INTEGER`.

If only 1 value is being projected, the alias and the type do not need to be included in a list.

[source,java]
----
def results = c.list {
    projections {
      sqlProjection 'sum(width * height) as totalArea', 'totalArea', INTEGER
    }
}
----

That query would return a single result with the value of 84 as the total area of all of the `Box` instances.

The DSL supports grouped projections with the `sqlGroupProjection` method.

[source,java]
----
def results = c.list {
    projections {
        sqlGroupProjection 'width, sum(height) as combinedHeightsForThisWidth', 'width', ['width', 'combinedHeightsForThisWidth'], [INTEGER, INTEGER]
    }
}
----

The first argument to the `sqlGroupProjection` method is the SQL which defines the projections.  The second argument represents the
group by clause that should be part of the query.  That string may be single column name or a comma separated list of column
names.  The third argument is a list of
Strings which represent column aliases corresponding to the projected values expressed in the SQL.  The fourth argument
is a list of `org.hibernate.type.Type` instances which correspond to the projected values expressed in the SQL.

The query above is projecting the combined heights of boxes grouped by width and would return results that look like this:

[source,groovy]
----
[[2, 24], [4, 9]]
----

Each of the inner lists contains 2 values.  The first value is a box width and the second value is the sum of the heights
of all of the boxes which have that width.


==== Using SQL Restrictions


You can access Hibernate's SQL Restrictions capabilities.

[source,java]
----
def c = Person.createCriteria()

def peopleWithShortFirstNames = c.list {
    sqlRestriction "char_length(first_name) <= 4"
}
----

SQL Restrictions may be parameterized to deal with SQL injection vulnerabilities related to dynamic restrictions.


[source,java]
----
def c = Person.createCriteria()

def peopleWithShortFirstNames = c.list {
    sqlRestriction "char_length(first_name) < ? AND char_length(first_name) > ?", [maxValue, minValue]
}
----


NOTE: Note that the parameter there is SQL. The `first_name` attribute referenced in the example refers to the persistence model, not the object model like in HQL queries. The `Person` property named `firstName` is mapped to the `first_name` column in the database and you must refer to that in the `sqlRestriction` string.

Also note that the SQL used here is not necessarily portable across databases.


==== Using Scrollable Results


You can use Hibernate's https://docs.jboss.org/hibernate/orm/5.6/javadocs/org/hibernate/ScrollableResults.html[ScrollableResults] feature by calling the scroll method:

[source,java]
----
def results = crit.scroll {
    maxResults(10)
}
def f = results.first()
def l = results.last()
def n = results.next()
def p = results.previous()

def future = results.scroll(10)
def accountNumber = results.getLong('number')
----

To quote the documentation of Hibernate ScrollableResults:

____
A result iterator that allows moving around within the results by arbitrary increments. The Query / ScrollableResults pattern is very similar to the JDBC PreparedStatement / ResultSet pattern and the semantics of methods of this interface are similar to the similarly named methods on ResultSet.
____

Contrary to JDBC, columns of results are numbered from zero.


==== Setting properties in the Criteria instance


If a node within the builder tree doesn't match a particular criterion it will attempt to set a property on the Criteria object itself. This allows full access to all the properties in this class. This example calls `setMaxResults` and `setFirstResult` on the https://docs.jboss.org/hibernate/orm/5.6/javadocs/org/hibernate/Criteria.html[Criteria] instance:

[source,java]
----
import org.hibernate.FetchMode as FM
...
def results = c.list {
    maxResults(10)
    firstResult(50)
    fetchMode("aRelationship", FM.JOIN)
}
----


==== Querying with Eager Fetching


In the section on <<fetching,Eager and Lazy Fetching>> we discussed how to declaratively specify fetching to avoid the N+1 SELECT problem. However, this can also be achieved using a criteria query:

[source,java]
----
def criteria = Task.createCriteria()
def tasks = criteria.list{
    eq "assignee.id", task.assignee.id
    join 'assignee'
    join 'project'
    order 'priority', 'asc'
}
----

Notice the usage of the `join` method: it tells the criteria API to use a `JOIN` to fetch the named associations with the `Task` instances. It's probably best not to use this for one-to-many associations though, because you will most likely end up with duplicate results. Instead, use the 'select' fetch mode:
[source,groovy]
----
import org.hibernate.FetchMode as FM
...
def results = Airport.withCriteria {
    eq "region", "EMEA"
    fetchMode "flights", FM.SELECT
}
----
Although this approach triggers a second query to get the `flights` association, you will get reliable results  - even with the `maxResults` option.

NOTE: `fetchMode` and `join` are general settings of the query and can only be specified at the top-level, i.e. you cannot use them inside projections or association constraints.

An important point to bear in mind is that if you include associations in the query constraints, those associations will automatically be eagerly loaded. For example, in this query:
[source,groovy]
----
def results = Airport.withCriteria {
    eq "region", "EMEA"
    flights {
        like "number", "BA%"
    }
}
----
the `flights` collection would be loaded eagerly via a join even though the fetch mode has not been explicitly set.


==== Method Reference


If you invoke the builder with no method name such as:

[source,java]
----
c { ... }
----

The build defaults to listing all the results and hence the above is equivalent to:

[source,java]
----
c.list { ... }
----

[format="csv", options="header"]
|===

Method,Description
*list*,This is the default method. It returns all matching rows.
*get*,Returns a unique result set i.e. just one row. The criteria has to be formed in a way that it only queries one row. This method is not to be confused with a limit to just the first row.
*scroll*,Returns a scrollable result set.
*listDistinct*,If subqueries or associations are used one may end up with the same row multiple times in the result set. This allows listing only distinct entities and is equivalent to `DISTINCT_ROOT_ENTITY` of the https://docs.jboss.org/hibernate/orm/5.6/javadocs/org/hibernate/criterion/CriteriaSpecification.html[CriteriaSpecification] class.
*count*,Returns the number of matching rows.
|===


==== Combining Criteria


You can combine multiple criteria closures in the following way:

[source,java]
----
def emeaCriteria = {
    eq "region", "EMEA"
}

def results = Airport.withCriteria {
    emeaCriteria.delegate = delegate
    emeaCriteria()
    flights {
        like "number", "BA%"
    }
}
----

This technique requires that each criteria must refer to the same domain class (i.e. `Airport`).
A more flexible approach is to use Detached Criteria, as described in the following section.
