0

I have been struggling with understanding how to use criteria builders. On the very simple scenarios they are straight forward. But how would the following HQL be expressed?

def pkg_query = "select p from Package as p join p.orgs as orgRole where p.packageScope=(:scope) and orgRole.roleType=(:roleType) and orgRole.org=(:provider)"

The first part of the query is easy, but how I access the list?

  Package master = c.get {
    and {
        eq("packageScope",getMasterScope())
        //imaginary code, 'and' would probably break it as only 1 match in list
        for(orgRole in orgs){
            and{
                eq(orRole.org,provider)
                eq(orgRole.roleType,getCPRole())
            }
        }
    }
  }
Giannis
  • 5,286
  • 15
  • 58
  • 113

2 Answers2

2

You can model criterias against associations (regardless of their cardinalitiy) by invoking them as a method that receives a closure with the criterias inside. For the query you posted it'd be:

Package.createCriteria().get {
  eq('packageScope', scope)
  orgs {
     and {
        eq('roleType', roleType)
        eq('org', provider)
     }
  }
}
Deigote
  • 1,721
  • 14
  • 15
  • if you are working with MySQL I really recommend to activate the query log and check the produced SQL, then you can play with the criterias and HQL a bit more and compare the actual queries for both of them, being sure you are achieving the queries you actually want - see http://stackoverflow.com/questions/6479107/how-to-enable-mysql-query-log . I'm sure other datastores have similar options. – Deigote Aug 04 '15 at 09:40
1

You can use below code to get list of object.

def listOfPackage = Package.createCriteria().list {
      eq('packageScope', scope)
      orgs {
         and {
            eq('roleType', roleType)
            eq('org', provider)
         }
      }
    }
praveen_programmer
  • 1,072
  • 11
  • 25