Convert SQL Statement in Criteria query

Question

I need some help so that I can convert My sql in grails criteria query:

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 
    """

Edit: I am stuck on the inner seelect i.e for

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

for the inner select I tried this:

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

    }

This generate exception

 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

score 1 · Answer 1 · answered Sep 12 '14 at 13:41

It is difficult to say for sure without seeing your object model but you may be looking for something like this...

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

I hope that helps.

Convert SQL Statement in Criteria query

1 Answers1