sql - 在条件查询中转换SQL语句

Question

我需要一些帮助，以便可以在grails条件查询中转换My sql:

String query = """select * from personal_detail as pd
      where pd.id in (
      select u.personal_detail_id from user u where u.id in
      (
        SELECT ur.user_id FROM user_role ur where ur.role_id = '${role.id}'
     )
      and u.enabled = true and u.is_deleted = false
     )
      and
     pd.status = true 
    """

编辑:
我被内在的见解卡住了

SELECT ur.user_id FROM user_role ur where ur.role_id = '${role.id}'

对于内部选择，我尝试了此操作:

def results = UserRole.createCriteria().list {
        createAlias('user','user')
            eq('role', role)
            eq('user.enabled',true)
            eq('user.isDeleted',false)

    }

这产生异常

 Unknown column 'user1_.enabled' in 'where clause'. Stacktrace follows:
Message: Unknown column 'user1_.enabled' in 'where clause'
Line | Method
->>  411 | handleNewInstance  in com.mysql.jdbc.Util
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|    386 | getInstance        in     ''
|   1052 | createSQLException in com.mysql.jdbc.SQLError
|   4096 | checkErrorPacket   in com.mysql.jdbc.MysqlIO

Answer 1

没有看到对象模型很难确定地说，但您可能正在寻找类似的东西...

def results = UserRole.withCriteria {
    eq 'role', role
    user {
        eq 'enabled', true
        eq 'isDeleted', false
    }
}

希望对您有所帮助。