CODEWITHSUNDEEP
hibernategrailsgrails-domain-classhibernate-criteria
David B
David B

Reputation: 3581

Grails hasMany: find all master records with a child that matches a criteria

I have this Grails 2.0.3 project wherein there are two models DomainA and DomainB and both are related to each other by a many-to-many relationship in which DomainB is a child of DomainA.

class DomainA {
  // properties
  static hasMany = [domains: DomainB]
}

class DomainB {
  // properties
  static hasMany = [domains: DomainA]
  static belongsTo = [DomainA]
}

Given this kind of design, I want to query all DomainB wherein there is/are an instance(s) of DomainA following the query set to DomainA.

def domainsList = DomainA.createCriteria().list() {
   // other criterions for the other properties
}
DomainB.createCriteria().list() {
   inList("domains", domainsList)
   // other criterions for the other properties
}

When executing the code above, an error is prompt ERROR util.JDBCExceptionReporter - Parameter #1 has not been set. wherein the Parameter #1 is prompt the the domains property name in the inList criterion.

Having this problem, is this possible to solve? How?

Upvotes: 3

Views: 8352

Answers (2)

ubiquibacon
ubiquibacon

Reputation: 10667

You can use HQL:

def domainBsWithDomainAs = DomainB.executeQuery( 'SELECT DISTINCT b FROM DomainB b INNER JOIN b.domains a WHERE a IN(SELECT DISTINCT a FROM DomainA a)' )

Or with createCriteria:

def domainsList = DomainA.createCriteria().list() {
   // other criterions for the other properties
}
DomainB.createCriteria().list() {
   "in"("domains", domainsList) // Use "in" (including quotes) instead of inList.
   // other criterions for the other properties
}

Upvotes: 3

Victor Sergienko
Victor Sergienko

Reputation: 13485

Take a look at GORM guide, look for "Querying associations". Let's rather try to do it all with a single query.

With new "where" query, it's

def query = DomainB.where {
  domains { someAField == 3 } && someBField == 8
}

or with CriteriaBuilder:

DomainB.withCriteria {
  domains { 
    eq 'someAField', 3 
  }
  eq 'someBField', 8
}

Upvotes: 5

Related Questions