Show Navigation

Querying the Database using GORM Dynamic Finders

This guide will demostrate how to efficiently query your database using GORM's dynamic finders.

Authors: Matthew Moss

Grails Version: 5.0.1

1 Grails Training

Grails Training - Developed and delivered by the folks who created and actively maintain the Grails framework!.

2 Getting Started

In this guide, you are going to update a Grails service to more efficiently query a database. The service methods currently load all records from a table and search through them in-memory; you will change those methods to use GORM Dynamic Finders, which are more efficient all around.

2.1 What you will need

To complete this guide, you will need the following:

  • Some time on your hands

  • A decent text editor or IDE

  • JDK 1.8 or greater installed with JAVA_HOME configured appropriately

2.2 How to complete the guide

To get started do the following:

or

The Grails guides repositories contain two folders:

  • initial Initial project. Often a simple Grails app with some additional code to give you a head-start.

  • complete A completed example. It is the result of working through the steps presented by the guide and applying those changes to the initial folder.

To complete the guide, go to the initial folder

  • cd into grails-guides/querying-gorm-dynamic-finders/initial

and follow the instructions in the next sections.

You can go right to the completed example if you cd into grails-guides/querying-gorm-dynamic-finders/complete

3 Testing the Service

The initial and complete projects are not full Grails applications. Instead, there is the Grails service QueryService which you will edit. With any changes you make, you should strive to ensure all of the unit tests in QueryServiceSpec continue to pass.

To run the unit tests:

$ ./gradlew :initial:test
Starting a Gradle Daemon (subsequent builds will be faster)
:initial:compileJava NO-SOURCE
:initial:compileGroovy
:initial:buildProperties
:initial:processResources
:initial:classes
:initial:compileTestJava NO-SOURCE
:initial:compileTestGroovy
:initial:processTestResources NO-SOURCE
:initial:testClasses
:initial:test

BUILD SUCCESSFUL

The above is what you should see if all the unit tests pass. A full HTML report can be found at
initial/build/reports/tests/test/index.html.

If you make changes to the service that cause the unit tests to fail, you will see output like this:

$ ./gradlew test
Starting a Gradle Daemon (subsequent builds will be faster)
:initial:compileJava NO-SOURCE
:initial:compileGroovy
:initial:buildProperties
:initial:processResources
:initial:classes
:initial:compileTestJava NO-SOURCE
:initial:compileTestGroovy
:initial:processTestResources NO-SOURCE
:initial:testClasses
:initial:test


demo.QueryServiceSpec > test what players have last name Klein FAILED
    org.spockframework.runtime.ConditionFailedWithExceptionError at QueryServiceSpec.groovy:123
        Caused by: groovy.lang.MissingPropertyException at QueryServiceSpec.groovy:123

demo.QueryServiceSpec > test what players have last name King FAILED
    org.spockframework.runtime.ConditionFailedWithExceptionError at QueryServiceSpec.groovy:123
        Caused by: groovy.lang.MissingPropertyException at QueryServiceSpec.groovy:123

37 tests completed, 2 failed
:initial:test FAILED

FAILURE: Build failed with an exception.

* What went wrong:
Execution failed for task ':initial:test'.
> There were failing tests. See the report at:
  file:///<...path...>/querying-gorm-dynamic-finders/initial/build/reports/tests/test/index.html

* Try:
Run with --stacktrace option to get the stack trace. Run with --info or --debug option to get more log output.

BUILD FAILED

In that case, open the HTML report file to see which tests are failing and why.

Your goal for this guide is to update every QueryService method with an implementation that uses Dynamic Finders and still passes all of the QueryServiceSpec unit tests.

4 Updating the Service

Dynamic finders are methods generated at runtime. Though the methods don’t exist initially, Grails uses the Groovy metaprogramming hooks methodMissing and propertyMissing to recognize when you attempt to call one. Grails will then examine the method name you use and generate an appropriate implementation, including the corresponding database query.

Dynamic finders read like English commands. For example, if I want to find all the board games with an average play duration between 30 and 90 minutes, it might look like this:

def games = Game.findAllByAverageDurationBetween(30, 90)

This guide will explore ways to build dynamic finders by updating all the methods in QueryService with implementations that use dynamic finders, such that the new implementations continue to pass all unit tests in QueryServiceSpec.

4.1 Find One By Value

The first method to update is QueryService.queryGame which takes a single name parameter. The existing code loads all instances of the Game domain class, and searches for the single instance whose name matches the parameter. (We only expect to find one, since the name property of Game is unique.)

Game queryGame(String name) {
    Game.all.find {
        it.name == name
    }
}

The all property on Game does exactly what it sounds like: it fetches all of the instances from the database into memory. This terrible implementation fetches all of the instances when only one is needed; this is highly inefficient code! Let’s get exactly the one instance we want (i.e. the one with the provided name) using a dynamic finder.

Every dynamic finder starts with a prefix. Since we expect to find only one game (because the name is constrained to be unique), we can use findBy, which will return the matching instance if one is found or a null if no match is found.

findBy can also be used to retrieve only the first of many matching instances. However, you should consider adding a sort column, described in Parameterizing Queries, to make your query robust.

After determining the prefix, next we need the property to match against. The Game domain class has a name property that the inefficient implementation used; by appending (using camel-case) the property name to the prefix, we create the dynamic finder findByName to find the desired game. This method should be called on the domain class of interest: in this case, Game. So our dynamic finder call will be Game.findByName(…​).

At the same time we add a property name to the dynamic finder method name, we should also add an argument to the dynamic finder method call; in this case, we want to use the name parameter provided by the queryGame method. So the final, efficient implementation of queryGame is:

Game queryGame(String name) {
    Game.findByName(name)
}

Change the implementation and run the tests to see that they still pass.

When you append a property name to a dynamic finder method, you also append a corresponding argument to the method call in most cases. The few cases that differ in this respect will be pointed out in later sections of this guide.

4.2 Find Many By Value

Knowing how to find one instance by a property value, we can find multiple instances by a (non-unique) property value by changing the prefix. Instead of findBy, use findAllBy to return a list of matching instances. If none match, the list will be empty.

Let’s update QueryService.queryGamesWithAverageDuration, a method used to collect all of the board games with the provided average duration. Here is the existing, inefficient implementation:

List<Game> queryGamesWithAverageDuration(Integer averageDuration) {
    Game.all.findAll {
        it.averageDuration == averageDuration
    }
}

As before, this slow implementation loads all records into memory, then searches over them. The database engine is much quicker and the memory load is much lower if only the matching records are returned, so let’s change this to a dynamic finder.

To find multiple records, use the findAllBy prefix. Append the property name averageDuration (updating capitalization for consistent camel-case of the dynamic finder method name), and pass the averageDuration parameter from queryGamesWithAverageDuration as an argument to the dynamic finder method call.

List<Game> queryGamesWithAverageDuration(Integer averageDuration) {
    Game.findAllByAverageDuration(averageDuration)
}

4.3 Find By Inequality

The previous methods searched for matching instances by value equality. Dynamic finders can search also by inequality: whether the property is not equal to, is greater than, or is less than the provided value.

Let’s look at QueryService.queryGamesNotConsideredStrategy which attempts to find all games that, according to the database, are not marked as strategy games.

List<Game> queryGamesNotConsideredStrategy() {
    Game.all.findAll {
        it.strategy != true
    }
}
It would be more idiomatic to write the predicate as !it.strategy, but for this demo, the not-equal comparison against true is made explicit to better illustrate the similarity between the inefficient version and the dynamic finder version.

Since we expect there will be more than one result, we still need to use the prefix findAllBy. The property of interest is strategy, a boolean flag. But using the dynamic finder findAllByStrategy(true) finds all of the strategy games, and we want the non-strategy games. In order to find those, we need to add a comparator. A comparator changes the query from an equality test to something else. You’ll see many of these in the sections to follow. Here, we will use the comparator NotEqual which, when appended to the property name in the dynamic finder method name, generates a query that matches on non-equality.

Our improved query to find non-strategy games is then:

List<Game> queryGamesNotConsideredStrategy() {
    // General case: using the NotEqual comparator.
    Game.findAllByStrategyNotEqual(true)    (1)

    // Special case: using exceptional form for Boolean properties.
    Game.findAllNotStrategy()    (2)
}
1 This query demonstrates the general form of a dynamic finder using the NotEqual comparator.
2 This query demonstrates the exceptional form of dynamic finders for boolean properties.
Though the second form shown here is more compact and readable, the first form using the NotEqual comparator will work equally well for any database types; it is used here with a boolean property simply to demonstrate how the comparator works.

What if we want a greater than or less than comparison? Let’s look at QueryService.queryGamesExpectedShorterThan, which will return a list of games with an average duration shorter than the provided value.

List<Game> queryGamesExpectedShorterThan(Integer duration) {
    Game.all.findAll {
        it.averageDuration < duration
    }
}

At this point, we know to start with findAllByAverageDuration. Since we want to match property values that are less than the provided value, we should use the LessThan comparator. Again, this comparator is appended after the property name, so our final, improved implementation is:

List<Game> queryGamesExpectedShorterThan(Integer duration) {
    Game.findAllByAverageDurationLessThan(duration)
}

There is a similar comparator when you need instances with property values greater than the supplied argument: use GreaterThan. For example, QueryService.queryGamesRatedMoreThan will find all games with a rating higher than the provided rating. The original implementation:

List<Game> queryGamesRatedMoreThan(BigDecimal rating) {
    Game.all.findAll {
        it.rating > rating
    }
}

Our dynamic finder for this query will be called findAllByRatingGreaterThan.

List<Game> queryGamesRatedMoreThan(BigDecimal rating) {
    Game.findAllByRatingGreaterThan(rating)
}

The LessThan and GreaterThan comparators are strict inequality comparisons; that is, if the values compared are equal, the instance is not considered a match. If you need to also include equality, use the non-strict LessThanEquals and GreaterThanEquals comparators.

4.4 Counting Matching Instances

Sometimes, you don’t need the actual results of a query, but rather just how many instances did match. As before, this inefficient implementation loads all the records, then counts how many match a predicate.

int queryHowManyGamesRatedAtLeast(BigDecimal rating) {
    Game.all.count {
        it.rating >= rating
    }
}

A more efficient implementation of the above would call the dynamic finder Game.findAllByRatingGreaterThanEquals(rating), then return the size of the resulting list. But if all you need is a single number — the count of matching records — it is inefficient to fetch the results of the database query into memory.

In this case, use the prefix countBy. It works very much like findAllBy, but instead of returning the matching instances, it returns the number of instances matched. So to count how many games rated a certain value or higher:

int queryHowManyGamesRatedAtLeast(BigDecimal rating) {
    Game.countByRatingGreaterThanEquals(rating)
}
By now, you’ve probably noticed that the QueryService methods, when implemented efficiently using dynamic finders, is not really useful as-is. That said, Grails services often do much more business logic that what this guide attempts to teach. The use of the service here helps to separate the work from the tests.

4.5 Find By Value Range

What if you need to find instances where some property falls in between two values? Maybe you want to know all the games played (represented by the domain class Match) between two dates?

List<Game> queryMatchesPlayedBetweenDates(Date startDate, Date finishDate) {
    Match.all.findAll {
        startDate <= it.started && it.started <= finishDate
    }
}

Use the comparator Between. As mentioned earlier, most comparators expect a matching argument in the dynamic finder call. However, Between is one of the exceptions, and it expects two arguments: the lower and upper bounds. In the case of QueryService.queryMatchesPlayedBetweenDates, the lower and upper bounds are the start and finish dates.

We will use the started property of Match as the property to examine. We could have chosen to look at the finished property, but that could be null while started cannot be null according to the constraints of Match.

Now we know the parts to build up our dynamic finder call: findAllBy to look for multiple, matching instances; started as the property to examine; and Between as the operator to find values of the property between provided lower and upper bounds. Here is the efficient implementation.

List<Game> queryMatchesPlayedBetweenDates(Date startDate, Date finishDate) {
    Match.findAllByStartedBetween(startDate, finishDate)
}

A similar comparator is InRange, which works like Between but accepts a Groovy range as the argument. As an example, we want to find out how many high scores there are across all played games. Let’s define high scores to be in the range 90-100.

Range highScore = 90..100
def numHighScores = queryService.queryHowManyScoresWithinRange(highScore)

Here’s the old, inefficient implementation of queryHowManyScoresWithinRange:

int queryHowManyScoresWithinRange(Range range) {
    Score.all.count {
        it.score in range
    }
}

Since we only want to know how many high scores there are (and not the scores themselves), we’ll use the countBy prefix. We need to look at the score property of the Score domain class, and we’ll use InRange to compare against the provided range. Here is the improved implementation.

int queryHowManyScoresWithinRange(Range range) {
    Score.countByScoreInRange(range)
}

4.6 Find by Similar Strings

In addition to inequality comparisons (with types such as numbers and dates), we want to do inexact string comparisons…​ to know that one string is similar or "like" another string. Here follows three examples that we should improve with a dynamic finder.

First, find all players having a certain last name.

List<Game> queryPlayersWithLastName(String lastName) {
    Player.all.findAll {
        it.name.endsWith " ${lastName}"
    }
}

GORM provides the dynamic finder comparator Like for string comparisons, and it uses the character % in the search string as a wildcard. So the above can more efficiently be written as:

List<Player> queryPlayersWithLastName(String lastName) {
    Player.findAllByNameLike("% ${lastName}")
}

The % wildcard can go anywhere in the search string: front, back, middle, even multiple locations. It can match any string, even empty.

The second example finds all game mechanics whose name contains a snippet of text, case-insensitive.

List<Game> queryMechanicsContaining(String text) {
    Mechanic.all.findAll {
        StringUtils.containsIgnoreCase it.name, text
    }
}

The earlier comparator Like was case-sensitive; it matched anything where the % wildcards appeared, but other text was an exact match. If you want to search ignoring case, use the Ilike comparator. You can still use wildcards, as is done here.

List<Mechanic> queryMechanicsContaining(String text) {
    Mechanic.findAllByNameIlike("%${text}%")
}

In our third example, we find all games where the name matches a regular expression pattern.

List<Game> queryGamesMatching(String pattern) {
    Game.all.findAll {
        it.name ==~ pattern
    }
}
Regular expressions are a handy tool for developers to define complex search expressions in a compact form. Groovy provides support for regular expressions built upon the Regular Expression APIs in Java.

To do regular expression matching, use the Rlike comparator in your dynamic finder.

List<Game> queryGamesMatching(String pattern) {
    Game.findAllByNameRlike(pattern)      // Rlike: not universally supported
}
The Rlike comparator is not universally supported! It is only supported if the underlying database supports regular expressions. If the database does not support regular expressions, then Rlike will fall back to Like behavior.

4.7 Find By Null/Non-Null

Since our databases and Grails domain classes can contain properties that might hold null values, we need ways to query those situations. For example, in our original service implementations, we have two methods used to find which games played are complete and which are still ongoing.

int queryHowManyMatchesInProgress() {
    Match.all.count {
        it.finished == null
    }
}

int queryHowManyMatchesCompleted() {
    Match.all.count {
        it.finished != null
    }
}

The comparators for checking null values are IsNull and IsNotNull. These are special in that they require no arguments for the dynamic finder. Here is the improved implementation:

int queryHowManyMatchesInProgress() {
    Match.countByFinishedIsNull()
}

int queryHowManyMatchesCompleted() {
    Match.countByFinishedIsNotNull()
}

4.8 Find Property In List

In the above sections, we have been able to find instances through a property: by equality, by inequality, by values between bounds. Sometimes, though, the matching values for a property do not form a simple range. With dynamic finders, you can find records where a property is tested against items from a provided list: use the InList comparator.

Here are two examples. The first finds all of the Game records that match a list of String game names, while the second example finds all Match records that track the game play of the games listed.

    List<Game> queryGamesForNames(List<String> names) {
        Game.all.findAll {
            it.name in names
        }
    }

    List<Game> queryMatchesForGames(List<Game> games) {
        Match.all.findAll {
            it.game in games
        }
    }

By using the InList comparator and passing in the list of items to match against, we can reduce these methods to be more efficient.

    List<Game> queryGamesForNames(List<String> names) {
        Game.findAllByNameInList(names)
    }

    List<Match> queryMatchesForGames(List<Game> games) {
        Match.findAllByGameInList(games)
    }

In the second example, QueryService.queryMatchesForGames, note that the list does not contain simple types (like Number or String), but actually contains a list of Game domain objects. This is reasonable, since the property game of Match is an instance of Game.

Anytime you have a relationship like this, you can use domain class instances as part of your query. As another example, the Score domain references the Player domain, and so, for example, to find all of the scores by a particular player:

    def jeffBrown = Player.findByName('Jeff Brown')
    def scores = Score.findByPlayer(jeffBrown)

Related to the InList comparator is the NotInList comparator which, as it sounds, finds matching instances where the property under test is not found in the provided list. So, for the inefficient implementation of QueryService.queryGamesOtherThan:

    List<Game> queryGamesOtherThan(List<Game> games) {
        Game.all.findAll {
            !(it in games)
        }
    }

This can be replaced using the NotInList comparator:

    List<Game> queryGamesOtherThan(List<Game> games) {
        Game.findAllByNameNotInList(games*.name)
    }

4.9 Combining Query Clauses

There will be times when you want to find records by more than one property. Let’s take an example: we want to find all the board games that support a certain number of players. Our inefficient implementation looks like this:

    int queryHowManyGamesSupportPlayerCount(Integer playerCount) {
        Game.all.count {
            it.minPlayers <= playerCount && playerCount <= it.maxPlayers
        }
    }

These implementation use equality and less-than-or-equals operators; we already know how to search for these things independently. For example, to find where the minimum number of players is no higher than the requested player count, we would do this:

    List<Game> games = Game.findAllByMinPlayersLessThanEqual(playerCount)

Or to find those games where the maximum number of players is no less than the requested player count, we would do this:

    List<Game> games = Game.findAllByMaxPlayersGreaterThanEqual(playerCount)

Because minPlayers and maxPlayers are different properties, we can’t use the Between or InRange comparators, as they operate on a single property.

Instead, we introduce here combinators. These are the boolean operators And and Or. So if we want to combine two requirements (i.e. a property and its comparator), we join them using And if we require both requirements to pass, or Or if only one is required to pass.

For our current example, we want to combine findAllByMinPlayersLessThanEqual and findAllByMaxPlayersGreaterThanEqual, requiring both statements to pass (which matches the boolean-and operator && of the inefficient implementation). When joining these, the prefix (e.g. findAllBy) only needs to be specified once; then, our And combined query becomes findAllByMinPlayersLessThanEqualAndMaxPlayersGreaterThanEqual, and the final dynamic finder method call will take two arguments: one for minPlayers and one for maxPlayers.

    int queryHowManyGamesSupportPlayerCount(Integer playerCount) {
        Game.countByMinPlayersLessThanEqualsAndMaxPlayersGreaterThanEquals(playerCount, playerCount)
    }

Another similar example: find all board games supporting an exact number of players (i.e. where minPlayers and maxPlayers are equal to the same number). The original implementation:

    List<Game> queryGamesSupportExactPlayerCount(Integer playerCount) {
        Game.all.findAll {
            it.minPlayers == playerCount && it.maxPlayers == playerCount
        }
    }

and the improved implementation:

    List<Game> queryGamesSupportExactPlayerCount(Integer playerCount) {
        Game.findAllByMinPlayersAndMaxPlayers(playerCount, playerCount)
    }
You are not limited to two predicates in your dynamic finder. You can use the And multiple times to join as many predicates (i.e. query requirements) as you need. However, past two or three predicates, you may find that your dynamic finder becomes difficult to read. Complex queries may be implemented more legibly by using other query techniques; these will be the topics of future guides.

If we don’t require both parts of the combination to pass, we can use the Or combiner (which compares to the boolean-or operator ||). In QueryService.queryGamesConsideredFamilyOrParty, the original implementation:

    List<Game> queryGamesConsideredFamilyOrParty() {
        Game.all.findAll {
            it.family || it.party
        }
    }

can be replaced with an improved implementation:

    List<Game> queryGamesConsideredFamilyOrParty() {
        Game.findAllByFamilyOrParty(true, true)
    }
While a dynamic finder may use any number of And combiners or any number of Or combiners, you cannot use And and Or together in the same dynamic finder. So this query and those like it are invalid: Game.findByMinPlayersAndMaxPlayersOrParty(2, 5, true). To specify a condition such as this, you need to use other query techniques.

4.10 Parameterizing Queries

Any database query is made more useful when the results are sorted and paginated. With any dynamic finder, beyond the arguments required by the properties/comparators used, an additional Map argument can be added to manage sorting and pagination. (This argument map is the same used for the list() method.)

To sort results as part of your query, use sort, order, and ignoreCase.

  • sort specifies the property to use to sort all instances.

  • order is a string that specifies whether the sort should be ascending/increasing (asc) or descending/decreasing (desc).

  • ignoreCase specifies whether case should be ignored during sorting.

To paginate results as part of your query, use offset and max.

  • offset specifies the index of the first result to return.

  • max specifies the maximum number of results to return.

Pagination parameters are applied after sorting parameters.

As an example, let’s say I want to find all strategy games supporting at least two players.

    List<Game> games = Game.findAllByStrategyAndMinPlayers(true, 2)

Now let’s modify that to order the results, showing first the games supporting the highest maximum number of players. Since we’re changing the ordering (and not the conditions to be included in the results), we add sort/order query parameters.

    List<Game> games = Game.findAllByStrategyAndMinPlayers(true, 2, [sort: 'maxPlayers', order: 'desc'])

Next, let’s just show the first group of ten results. Add offset/max parameters.

    List<Game> games = Game.findAllByStrategyAndMinPlayers(true, 2, [sort: 'maxPlayers', order: 'desc', offset: 0, max: 10])

Assuming a user of the application clicked the Next button, we can modify the last call to show the next group of ten results by updating the offset value.

    List<Game> games = Game.findAllByStrategyAndMinPlayers(true, 2, [sort: 'maxPlayers', order: 'desc', offset: 10, max: 10])

Finally, as was noted earlier, the prefix findBy will return the first matching result of a query. For robust code, you should consider adding a sort parameter, or even replacing findBy with findAllBy and using max: 1 in your parameters. For example:

    // This:
    Game bestTwoPlayerGame = Game.findByMinPlayerAndMaxPlayer(2, 2, [sort: 'rating', order: 'desc'])

    // is equivalent to this:
    List<Game> bestTwoPlayerGame = Game.findAllByMinPlayerAndMaxPlayer(2, 2, [sort: 'rating', order: 'desc', max: 1])
There are a few more parameters available than are listed here. Please refer to the documentation for list to see if they apply to your situation.

5 Convenient Exceptional Forms

Dynamic finders are a great tool for building simple database queries: readable, full efficiency, with little hassle. In addition to all the possibilities described in earlier sections, there are a couple of special, convenient forms to add to your toolbox.

5.1 Query, Then Create/Save

Often, a developer will need to query the database to see if a particular instance exists and, if it does not, wants it created or saved before additional work is done. How many times have you seen or written this sort of code?

    def category = Category.findByName('Zombies')
    if (!category) {
        category = new Category(name: 'Zombies')
        category.save(flush: true)
    }
    // Now do something useful with `category`.

    def game = Game.findByNameAndMinPlayersAndMaxPlayers('SET', 2, 20)
    if (!game) {
        game = new Game(name: 'SET', minPlayers: 2, maxPlayers: 20)
    }
    // Now do something useful with `game`.

GORM provides two prefixes for dynamic finders that help to eliminate this sort of boilerplate: findOrCreateBy and findOrSaveBy. Both of these attempt to use the query specified to find a matching instance. If found, that instance is returned. If not found, a new instance is created and its properties set with the query-named property values. If findOrSaveBy is used, the instance is also saved. The following is equivalent to the above:

    Category category = Category.findOrSaveByName('Zombies')

    Game game = Game.findOrCreateByNameAndMinPlayersAndMaxPlayers('SET', 2, 20)

Often, findOrCreateBy may be more appropriate, if you need to fill in additional properties, build relationships, or ensure certain constraints are met before saving. Remember, though, that findOrCreateBy does not save, so call save on the instance to persist your changes.

When using the prefixes findOrCreateBy and findOrSaveBy, only exact matches are allowed, since if not found, your dynamic finder will attempt to create a new instance using the values provided in the query. So, for example, Game.findOrCreateByRating(5.5) is a valid, but the creating dynamic finder Game.findOrCreateByRatingGreaterThan(5.5) is not.

5.2 Simplifying Boolean Matching

Previously, we saw that you could match instances by their boolean properties like these examples:

    // Finds all strategy games.
    Game.findAllByStrategy(true)

    // Finds all non-party games.
    Game.findAllByPartyNotEqual(true)

    // Finds all family games the support at least four players.
    Game.findAllByFamilyAndMinPlayersGreaterThanEquals(true, 4)

Dynamic finders with one boolean property as part of the query can be shorted by incorporating the property name into the prefix and dropping the corresponding argument. So the above examples can be shortened to:

    // Finds all strategy games.
    Game.findAllStrategy()

    // Finds all non-party games.
    Game.findAllNotParty()

    // Finds all family games the support at least four players.
    Game.findAllFamilyByMinPlayersGreaterThanEquals(4)

If you are only examining the boolean property, you can drop the By portion of the prefix (so findAllByBooleanProperty becomes findAllBooleanProperty).

Checking against a true values uses the property name appended to findAll, while checking against a false value uses the property name appended to findAllNot.

If you have other conditions (like the third example above), then append those conditions after By in the prefix as earlier.

Only one boolean property can be incorporated into the prefix in this manner. If you have other boolean properties to match against in the same query, add them as before, such as Game.findAllStrategyByFamily(true) to find all games considered both strategy and family.

5.3 Chaining with Detatched Criteria and Named Queries

While dynamic finders are very convenient for simple queries, more complex queries will require other techniques. While these advanced query techniques are not discussed here, it can be useful to further restrict the results of these advanced techniques by chaining (i.e. appending) dynamic finders to them.

For example, let us find all the games that use involve the "Hand Management" mechanic and have an average duration of less than 120 minutes. Finding all games using a specific mechanic can be done with the Game domain class Named Query, gamesWithMechanic.

    static namedQueries = {
        gamesWithMechanic { aMechanic ->
            mechanics {
                eq 'id', aMechanic.id
            }
        }
    }

    // Find all games using the "Hand Management" game mechanic.
    Mechanic m = Mechanic.findByName("Hand Management")
    def games = Game.gamesWithMechanic(m)

To find games with an average duration of less than 120 minutes, we know to use the call Game.findAllByAverageDurationLessThan(120). But that searches for all games under 120 minutes, regardless of the game’s mechanics. To find games that match both conditions, append the dynamic finder to the named query.

    List<Game> queryGamesWithMechanicNoLongerThanDuration(Mechanic mechanic, int duration) {
        // Games provides a named query, 'gamesWithMechanic', to find all games that employ the provided game mechanic.
        // Dynamic finders can be chained onto named queries to narrow the results.
        Game.gamesWithMechanic(mechanic).findAllByAverageDurationLessThan(duration)
    }

Similarly, dynamic finders can be appended to Detached Criteria and Where Queries to further restrict the results of the criteria query.

    List<Game> queryGamesInCategoryWithAverageDuration(Category category, int duration) {
        // Here is a detached criteria to find all games within the specific category.
        DetachedCriteria<Game> detachedCriteria = new DetachedCriteria(Game).build {
            categories {
                eq 'id', category.id
            }
        }

        // Dynamic finders can be chained onto detached criteria to narrow the results.
        detachedCriteria.findAllByAverageDuration(duration)
    }

    // Find all "Economic" games of approximately 120 minutes.
    def category = Category.findByName("Economic")
    def games = queryGamesInCategoryWithAverageDuration(category, 120)
These various, more advanced techniques — Named Queries, Where Queries, Criteria and Detached Criteria Queries, and HQL — will be explored in depth in future Grails Guides.

6 Summary of Dynamic Finders

Prefixes

In these prefixes, Flag represents some Boolean property of a domain class; replace it with the name of the Boolean property to be tested.

Prefix Purpose

findBy

Find a single instance that matches the query.

findAllBy

Find multiple instances that match the query.

countBy

Count how many instances match the query.

findOrCreateBy

Special form: creates a new instance if an existing match is not found.

findOrSaveBy

Special form: creates and saves a new instance if an existing match is not found.

findAllFlag[By]

Special form for boolean properties: finds all instances where named property is true. Append 'By' to extend the query.

findAllNotFlag[By]

Special form for boolean properties: finds all instances where named property is false. Append 'By' to extend the query.

Comparators

In the following table, prop represents some property of a domain class; replace it with the name of the actual property to be tested. Replace val (or similar) with the actual value to compare against.

Comparator Example Equivalent Groovy Code Purpose

<none>

findAllByProp(val)

prop == val

Tests property is equal to value.

NotEqual

findAllByPropNotEqual(val)

prop != val

Tests property is not equal to value.

LessThan

findAllByPropLessThan(val)

prop < val

Tests property is less than value.

LessThanEquals

findAllByPropLessThanEquals(val)

prop <= val

Tests property is less than or equal to value.

GreaterThan

findAllByPropGreaterThan(val)

prop > val

Tests property is greater than value.

GreaterThanEquals

findAllByPropGreaterThanEquals(val)

prop >= val

Tests property is greater than or equal to value.

Between

findAllByPropBetween(lowerVal, upperVal)

(lowerVal <= prop) && (prop <= upperVal)

Tests property is between lowerVal and upperVal. Requires two arguments.

InRange

findAllByPropInRange(val)

prop in val

Tests property is in range specified by val (of Range type).

Like

findAllByPropLike(val)

No exact equivalent. Similar to prop.endsWith(val), prop.startsWith(val), prop.contains(val), depending on search string.

Tests property matches using wildcard string comparison. Use % for wildcard. Case-sensitive.

Ilike

findAllByPropIlike(val)

Same as Like.

Same as Like, but case-insensitive.

Rlike

findAllByPropRlike(val)

prop ==~ val

Tests property matches using regular expression.

IsNull

findAllByPropIsNull()

prop == null

Tests property is null. Requires no arguments.

IsNotNull

findAllByPropIsNull()

prop != null

Tests property is not null. Requires no arguments.

Combiners

Within a single dynamic finder method name, you may use either And or Or multiple times to combine multiple criteria, but you cannot use both And and Or in the same dynamic finder.

Combiner Example Equivalent Groovy Code Purpose

And

findByNameAndYearLessThan('Foo', 2017)

(name == 'Foo') && (year < 2017)

Both expressions must match for the instance to match.

Or

findAllByAgeLessThanOrAgeGreaterThan(8,88)

(age < 8) || (age > 88)

At least one expression must match for the instance to match.

Parameters

Pagination and sorting are done by adding a final Map argument to any dynamic finder. The map may contain the following parameters:

Parameter Purpose Default

sort

All matching instances are sorted according to the property specified. Sorting is done before the offset and max parameters are applied.

No default; if not specified, results are unsorted.

order

Sets the sorting order to either ascending/increasing ('asc') or descending/decreasing ('desc').

asc

ignoreCase

Sorting is case-insensitive if true, case-sensitive if false.

true

offset

From all matching, sorted instances, offset specifies the index of the first instance to be returned. Used for paginating results.

0

max

From all matching, sorted instances, max specifies the upper limit of how many instances to return. Used for paginating results.

No default; if not specified, returns all results.

For example, the following finds all games with an average duration between 30 and 90 minutes, sorted by their average duration (sort: 'averageDuration'), starting with the longest (order: 'desc'), and returning only the first five (offset: 0, max: 5) results.

    Game.findAllByAverageDurationInRange(30..90, [sort: 'averageDuration', order: 'desc', offset: 0, max: 5])

7 Do you need help with Grails?

Object Computing, Inc. (OCI) sponsored the creation of this Guide. A variety of consulting and support services are available.

OCI is Home to Grails

Meet the Team