jpagrailsgrails-orm

Grail/GORM Data Service @Query - Join with Multiple Conditions


I have the following two grails domain classes

class FaceData {
    ... other fields omitted ...
    static hasMany = [landmarks: FaceLandmark]
}

class FaceLandmark {
    FaceData faceData
    String name
    float x
    float y
}

and the following GORM Data Service

import grails.gorm.services.Query
import grails.gorm.services.Service

@Service(FaceData)
abstract class FaceDataDataService {
    ... other methods omitted ...

    @Query("select $fd from ${FaceLandmark fl} inner join ${FaceData fd = fl.faceData} where $fl.name = $name")
    abstract Set<FaceData> findByHasFaceLandmarkWithName(String name)
}

I'm trying to define a finder that will get the opposite of findByHasFaceLandmarkWithName. I want it to return only FaceData objects that do not have any FaceLandmarks with a specific name. The SQL for what I'm looking for follows. How do I specify that using an @Query annotation?

select fd.*
from face_landmark fl
right outer join face_data fd
    on fd.id = fl.face_data_id 
    and fl.name = 'some value'
where fl.name is null;

Solution

  • Figured it out...

        @Query("select $fd from ${FaceLandmark fl} right join ${FaceData fd} on $fd = $fl.faceData and $fl.name = $name where $fl is null")
        abstract List<FaceData> findByFaceLandmarkWithNameIsMissing(String name)