How to query a GORM Domain with a hasMany enum relationship to find entries containing only specific Enum values?

I'm working with a Grails application where I have a domain class that has a hasMany relationship with an enum. Here’s a simplified version of the code:

class Domain {
    static hasMany = [
        flags: Flags
    ]
}

enum Flags {
    VALUE, OTHER_VALUE
}

I need to write a GORM query that finds all Domain instances where the flags collection contains only the OTHER_VALUE enum value.

1. How can I correctly formulate a GORM query that returns Domain instances where the flags collection only contains the OTHER_VALUE enum?

2. Is there a specific way to handle hasMany relationships with enums in GORM that I’m missing?

Looking for something like

Domain.where {
   flags.contains(Flag.VALUE)
}.list()

however, the above isn't okay.

At first I thought HQL would be the simple answer on this but after giving it a shot, based on your current domain setup you will get unmapped table errors since DOMAIN_FLAGS isn't actually defined.

Instead I dropped down to create a native SQL statement. This is the method I ended up with. You'll need the sessionFactory injected.

List<Domain> sqlQuery() {
    def session = sessionFactory.currentSession
    def sql = """select d.* from domain d
        where d.id in (
            select domain_id from DOMAIN_FLAGS
            group by domain_id
            having count(*) = 1 and max(flags) = 'OTHER_VALUE'
        )"""

    session.createSQLQuery(sql)
            .setParameter('flag', Flags.OTHER_VALUE.toString())
            .addEntity(Domain)
            .list()
}