GORM notIn subquery with alias

Question

I am trying to implement the following SQL query in GORM:

select au.* from App_user au
inner join SEC_USER su on su.id=au.SEC_USER_ID
where 
not su.id in (
    select susr.SEC_USER_ID from SEC_USER_SEC_ROLE susr
        inner join SEC_ROLE sr on susr.SEC_ROLE_ID=sr.id
    where sr.authority like 'ROLE_INTERNAL%'
    and susr.SEC_USER_ID=su.id
)

(get all the users who don't have a role matching the ROLE_INTERNAL.* pattern)

I have a working query, using GORM:

        AppUser.createCriteria().list(args, {
            secUser {
//                def su = SecUser
                notIn "id", SecUserSecRole.where {
                    secRole {
                        authority ==~ 'ROLE_INTERNAL%'
                    }
/*
                    secUser {
                        id == su.id
                    }
*/
                }.property("secUser.id")
            }
        })

But this query is inefficient, due to the fact that I don't know how to add the SQL where clause and susr.SEC_USER_ID=su.id in the criteria DSL.

I have seen here and there mentions of using the commented code, to create an alias of the SecUser (su) at the root level, and then using it in the subquery, but I ger the following exception when trying to uncomment my additional statements:

No such property: id for class: SecUser

I feel that i am really close, but I can't figure it out by looking at the documentation. It is rather sparse in this aspect.

Answer 1

As the documentation shows under "7.4.8. More Advanced Subqueries in GORM",

DetachedCriteria<AppUser> query = AppUser.where {
    secUser {
        def u = SecUser
        notIn "id", SecUserSecRole.where {
            def u2 = secUser
            secRole.authority ==~ 'ROLE_INTERNAL%' && u2.id == u.id
        }.property("secUser.id")
    }
}
query.list(args)