GORM Queries and Finders

Choosing the Right Tool

Real applications need filtering, sorting, pagination, and joins. GORM offers four query styles so you can pick the right one:

• Dynamic finders - quick reads, simple queries only.
• Where queries - the recommended default.
• Criteria queries - dynamic conditions built at runtime.
• HQL - complex joins and performance tuning.

Dynamic Finders

User.findByEmail("test@example.com")
User.findAllByActive(true)
User.findAllByAgeGreaterThan(18)
User.findAllByUsernameLike("%admin%")
User.findByUsernameAndActive("admin", true)

Easy to read, but the method names get unwieldy for complex conditions and are resolved at runtime.

Where Queries (Recommended)

Where queries read like plain English and are type-safe:

def users = User.where {
    active == true && age >= 18
}.list()

def paged = User.where {
    active == true
}.list(max: 10, offset: 0, sort: "username")

Joins come naturally:

def books = Book.where {
    author.name == "John"
}.list()

Criteria Queries

Use criteria when conditions are optional or built at runtime:

def users = User.createCriteria().list {
    if (params.active) eq("active", true)
    if (params.minAge) ge("age", params.minAge as Integer)
    order("username", "asc")
}

HQL

def users = User.executeQuery(
    "from User u where u.active = true"
)

Powerful for complex joins, but avoid it for simple lookups.

Counting and Projections

def total = User.count()
def activeCount = User.countByActive(true)

def names = User.createCriteria().list {
    projections { property("username") }
}

def stats = User.createCriteria().get {
    projections {
        avg("age")
        max("age")
    }
}

Performance Tips

• Avoid N+1 queries by using joins and deliberate fetching.
• Paginate every list.
• Index frequently queried columns:

static mapping = {
    email index: true
}

Common Mistakes

• Overusing dynamic finders for complex logic.
• Fetching entire tables without pagination.
• Using HQL where a where query would do.
• Ignoring the SQL logs during development.

Key Points

• Where queries are the modern default.
• Criteria handles dynamic filters.
• HQL is for complex joins and tuning.
• Pagination and indexing protect performance.