join query on one-to-one Grails domain using GORM with parameters

Question

I have a two Grails domain class, let say:

class Hero {
    String name
    Float level

    Familiar familiar
}

class Familiar {
    String name
    Integer raceId
}

Now, what I want is to retrieve all Hero having Familiar with the name similar to a given String, for example: "hachiko". To do that using SQL, all I need is to perform a query similar to this:

SELECT h.name, h.level
FROM HERO h JOIN FAMILIAR f ON h.id = f.ownerId
WHERE f.name LIKE '%hachiko%'

But how can I do that on Grails? This is my code:

String filter = "hachiko"
def hero = Hero.executeQuery(
    "SELECT h.name, h.level FROM HERO h JOIN h.familiar WHERE h.id = " +
    "h.familiar.ownerId and h.familiar.name LIKE :filter",
    [filter, "%$filter%"]
)

Would this work? Should I create those static properties (hasOne, belongsTo), and where should I place them? Because so far, without those static properties, I'm receiving an error message:

ORA-00904: "HERO0_"."FAMILIAR_ID": invalid identifier

And having those static properties added, I got this:

org.hibernate.MappingException: hasOne property [Hero.familiar] is not bidirectional. Specify the other side of the relationship!

Their relationship is optional one-to-one. (e.g. A Hero can have at most one Familiar), and the id of the Familiar is the same of it's Hero.

Answer 1

You can use createCriteria for that

String filter = "hachiko"
def hero = Hero.createCriteria().list {
    familiar {
        like('name', filter)
    }    
}