Grails Criteria Projections Examples

Mar 09, 2015 Snippet comments

We could use Grails criteria projections when we want to select which properties or column to include in the query result. This post will show examples on how to use projections when working with Grails criteria to search for records in the database.

Introduction

If you are new to using Criteria, you may read these previous post on createCriteria method and Criteria API.

Simple Criteria With No Projections

This is a sample code that uses criteria api to retrieve all records but without using projections:

def criteria = Person.createCriteria()
def result = criteria.list{}

This is a sample code that uses criteria api to retrieve records with filtering (WHERE Clause) but still without using projections:

def result = Person.createCriteria().list{ 
    eq ('firstName', 'John')
}

Criteria With Projections

We could select which columns or properties to include by using projections in your criteria code:

Here is the counterpart of the sample codes above but this time using projections:

def result = Person.createCriteria().list {
    projections {
        property('firstName')
        property('lastName')
    }
}

def result = Person.createCriteria().list {
    projections {
        property('firstName')
        property('lastName')
    }
    eq ('firstName', 'John')
}

Projections With Aggregate Functions

We can also use aggregate functions inside Grails Criteria Projections.

• distinct - here is an example on how to retrieve all unique last names in the database
  

  def criteria = Person.createCriteria()
  def result = criteria.list {
      projections {
          distinct('lastName')
      }
  }
  println "Here are the list of unique last names"
  result.each { lastName ->
      println "${lastName}"
  }
  

• avg - here is an example on how to get the average age of all people
  

  def criteria = Person.createCriteria()
  def result = criteria.list {
      projections {
          avg('age')
      }
  }
  println "The average age is ${result[0]}"
  

• count - here is an example on how to get the number of records in the person table
  

  def criteria = Person.createCriteria()
  def result = criteria.list {
      projections {
          count()
      }
  }
  println "The number of rows is ${result[0]}"
  

• sum - here is an example on how to use sum
  

  def criteria = Purchase.createCriteria()
  def result = criteria.list {
      projections {
          sum('price')
      }
  }
  println "The sum of all price ${result[0]}"
  

• max and min - here is an example of how to use max and min
  

  def criteria = Person.createCriteria()
  def result = criteria.list {
      projections {
          max('age')
          min('age')
      }
  }
  println "The maximum age is ${result[0][0]}"
  println "The minimum age is ${result[0][1]}"