Projections and aggregations with Grails Criteria Builder

The Grails Criteria Builder is often introduced through simple filters such as status, title, or publication date. It becomes more useful when a report needs selected fields, grouped totals, or figures calculated by the database. This guide to projections and aggregations with Grails Criteria Builder shows how to turn those requirements into queries that remain readable and efficient.

A projection chooses which values a query returns, while an aggregation calculates a value such as a count, total, average, minimum, or maximum. Together, they can avoid loading complete domain objects when a dashboard needs only a title and price, or when a sales summary requires thousands of rows to be reduced to a handful of totals.

Australian development teams often work across distributed offices in Melbourne, Sydney, and other cities. A pragmatic “have a go” attitude is useful, but reporting queries still need predictable results, stable output, and sensible handling of dates, money, and regional business rules.

Selecting fields with projections

Start with the smallest useful result set. Suppose a Book domain class contains title, category, price, publishedAt, and status. Instead of returning every Book instance, a projection can retrieve only the values needed by a catalogue screen:

def activeBooks = Book.list {
    projections {
        property('title', 'bookTitle')
        property('category')
        property('price')
    }

    eq('status', 'ACTIVE')
    max(20)
}

The query returns maps rather than domain objects. The alias bookTitle gives the application a predictable key, while category and price use their property names. The top-level max(20) limits the number of results; it is separate from the max() aggregate used inside a projection list.

Aliases are valuable when the field name displayed to users differs from the database property. They also reduce accidental coupling between a controller and an unaliased result key. Where possible, keep a small mapping between projection aliases and response objects instead of passing map keys directly into JSON responses.

Projections should represent the shape of the report, not merely optimise an existing entity query. A list screen might need an author’s name, but returning the entire author object can introduce lazy-loading work or expose fields the screen does not require. Use distinct() when duplicate projection values must be removed, and remember that it reduces rows without changing the underlying source records.

Calculating aggregate values

Aggregate projections instruct the database to calculate values across the selected rows. They are preferable to retrieving every entity and reducing it in Groovy because the database can perform the operation without transferring the full data set:

def summary = Book.list {
    projections {
        count('id')
        sum('price')
        avg('price')
        min('price')
        max('price')
    }

    eq('status', 'ACTIVE')
}

This produces one summary row containing the number of active books and their price statistics. A count is normally returned as a numeric total, while currency aggregates should use a decimal type rather than a floating-point value. The exact numeric class can depend on the database and mapped property, so controllers should not rely on unsafe assumptions about its precision.

Aggregation also determines how missing values behave. Counting a non-null identifier excludes rows with a null identifier, averaging ignores null prices, and summing a collection containing only null values can return null. Tests should cover empty reports as well as populated ones, because a dashboard that displays “$0.00” when the database returns null may conceal a data problem.

The Criteria Builder covers common aggregate functions, but calculated expressions can require HQL or a database-specific query. For example, a weighted total might be clearer in a named query:

def total = Book.executeQuery(
    'select sum(b.price) from Book b where b.status = :status',
    [status: 'ACTIVE']
)

Named parameters should remain parameters. Concatenating a request value into HQL would bypass the protection that parameter binding provides and could create a serious injection risk.

Grouping results for reports

A grouped projection changes the grain of a report. Instead of one total for every active book, it produces one row for each category:

def categoryTotals = Book.list {
    projections {
        groupProperty('category')
        count('id')
        sum('price')
        avg('price')
    }

    eq('status', 'ACTIVE')
    order('category')
}

Using groupProperty() also generates the required GROUP BY clause. The category becomes the grouping dimension, while the count and sums describe each category independently. Results are commonly returned as maps with keys corresponding to the projected properties and aggregate functions.

Several grouping properties can be projected when a report needs a more detailed grain, such as category and status. Each combination then receives its own aggregate values. This distinction is important: changing the grouping level changes the meaning of every number in the result, so the grain should be stated clearly in the service method or report definition.

Australian reporting periods add another consideration. Financial years usually run from 1 July to 30 June, and business dates may need to follow that boundary rather than the calendar year. Store timestamps in UTC, convert them for display, and account for AEST and AEDT in eastern states and the half-hour offset used in ACST. For broader database design context, a database research summary can help teams compare data-oriented choices with the workload they actually have.

Joining data without inflated totals

Associations can be included in a Criteria projection, but each join affects the rows being aggregated. This example totals books for each author:

def authorTotals = Author.list {
    createAlias('books', 'writtenBook')
    join 'writtenBook'

    projections {
        groupProperty('name', 'authorName')
        count('writtenBook.id')
        avg('writtenBook.price')
    }

    order('authorName')
}

The inner join includes only authors who have at least one book. A left join can preserve authors without books, although their book count will be zero and their average price will be null. That difference must be reflected in the report rather than hidden by an unconditional fallback value.

Multiple collection joins can multiply rows. For example, joining both a book’s authors and its categories can make one book appear several times before aggregation. The count then measures joined rows rather than distinct books. A distinct identifier count may help when counting unique children, but sums can still be duplicated. In that situation, separate queries, a subquery, or a carefully designed database view will usually produce a more trustworthy total.

Indexes deserve attention once grouped reports become slow. Filters on status, category, and foreign-key columns are common candidates for indexing, although the generated SQL and database execution plan should guide the decision. Application logging should record the query and parameters during development, while production logs can capture elapsed time and result size without recording sensitive values.

An older Grails project may also contain packaging instructions from its original hosting provider. The AppFog deployment walkthrough is useful for understanding that deployment flow, but it should be treated as historical material rather than a current hosting recommendation. New Australian deployments can consider the Sydney cloud region, while checking data-residency requirements and cloud costs quoted in Australian dollars. Whether 10% GST applies depends on the supplier and its Australian tax registration.

Protecting queries and handling failures

Projection names, sorting fields, and filter properties should come from an allowlist when they originate from a request. A controller should never pass an arbitrary client-provided field directly into a projection or sort clause. Even when a Criteria DSL reduces the risk compared with raw SQL, allowing arbitrary identifiers can expose unmapped data and weaken authorisation.

Authorisation must also be applied consistently to the rows being counted. If ordinary book listings exclude archived records, an aggregate used by the same dashboard should use the same restriction. Otherwise, a count can reveal information about protected records. Service-layer tests should verify both the returned values and the generated SQL for users with different permissions.

Error messages should be translated rather than assembled directly inside a controller. The site’s exception translation guide shows how custom exceptions can be mapped into localised messages. This keeps query errors separate from user-facing content and makes it easier to test Australian English copy alongside other supported languages.

Australian deployments must also consider the Australian Privacy Act 1988 and the privacy obligations that apply to the organisation. Limit access to reporting data, encrypt sensitive fields, and avoid placing personal information into logs or unrestricted exports. These measures are particularly important when a reporting service is hosted in Australia but accessed by administrators working overseas.

Testing should cover projection keys, aggregate types, empty result sets, grouped ordering, joins, and authorisation boundaries. A small fixture that creates authors with no books, books with multiple categories, and null prices will expose problems that uniform sample data often misses. It is also worth asserting that aliases remain stable, because renaming one can break a controller or frontend contract without causing a compilation failure.

Start with one real report from your application and express its required output as explicit projections. Build the query incrementally, confirm the grouping grain, inspect the generated SQL, and compare its totals with a known dataset. Then use the matching Grails Example lessons and code examples to extend the approach into practical application workflows.

Run the finished Criteria query against production-shaped data, verify its performance and permissions, and release the report with confidence.