Exists clause in criteria

Question

Consider this query:

select user_id
from user_role a
where a.role_id = -1
      and not exists (select 1 
                      from user_role b 
                      where b.role_id != a.role_id 
                            and b.user_id = a.user_id);

I'm trying to recreate it using gorm criterias. Is it possible? How can I add the exists clause?

UPDATE

I've been trying to resolve this way:

UserRole.createCriteria().list{
  eq('role', roleObj)
  createAlias('user', 'u')
  not{
    inList('u.id', {
      not {
        eq('role', roleObj)
      }
      projections {
       property 'user.id'
      }
    })
  }
}

Still not working. I'm gettins this error when executing it:

DUMMY$_closure1_closure2_closure3_closure4 cannot be cast to java.lang.Long

I don't understand the error message. The inner criteria returns the list of ids and if I replace the inner criteria with a list of longs it works. Any clue?

Thanks in advance

Answer 1

Not tested : Try Sql restriction

 UserRole.createCriteria().list {
  and{
    eq('roleId',-1)
    sqlRestriction(" not exists(select 1 
                        from UserRole  b 
                        where ............ 
                              )")
  }

}

-- Hoping you have named your domain as UserRole and column name named as roleId.