Grails : how to best construct a hibernate criteria builder to search 'hasMany' relationships with domain instance -

i working on grails project , leverage hibernate criteria builders search instances of domain object. find instances 1 of 'hasmany' relationships contains domain object ids. here example of mean.

domain objects

class product {    static hasmany = [ productoptions: productoption ] }  class productoption{    option option    static belongsto = [ product: product ] }  class option{    string name } 

this simplified example of domain structure , doesn't include relationships.

an option size, color, brand, etc.

example of achieve

lets have 3 products.

product 1 red, small , brandx  product 2 blue, small , brandx  product 3 yellow, medium , brandz 

i have few scenarios need cover.

scenario 1

• find products blue, small , brandx. in case should return product 2.

scenario 2

• find products either red or blue , size small. both product 1 , product 2 should returned.

scenario 3

• find products either brandx or brandz. products should returned.

i hope covers scenarios.

this example of current attempt.

def c = product.createcriteria() def products = c.list{     , {         productoptions {             'option' {                 ideq(1)//1 id of blue option             }         }         productoptions {             'option' {                 ideq(5)//5 id of small size option             }         }         productoptions {             'option' {                 ideq(10)//10 id of brandx brand option             }         }     } } 

the and portion of example doesn't include options , fails. how best achieve this? can use grails hibernate criteria builder achieve this? please let me know if additional information help.

thanks in advance guidance provided.

what you're looking equivalent of groovy's object.every(closure).

assert [1, 2, 3].every { < 4 } == true assert [1, 2, 3].every { < 3 } == false

the every() method returns boolean indicating whether closure evaluates true every item in collection.

unfortunately, none of query methods (where, criteria, , hql) provide equivalent of every(). but... can cheat using hql.

note: nor criteria queries because don't support equivalent of hql having clause.

scenario #1 - hack

def ids = [4, 5, 6] // list of option ids.  product.executequery ''' select prd product prd      join prd.productoptions prdopts      join prdopts.option opt  opt.id in :ids  group prd having count(prd) = :count''', [ids: ids.collect { it.tolong() }, count: ids.size().tolong()] 

how works

the query begins selecting of products have any of options in ids list. long product has @ least 1 of options returned.

this produces side-effect of listing product every matching option has. instance, if product has 3 of options, product returned 3 times. group clause makes query filter out duplicate listings.

however, duplicates key hack: if list of ids unique list, , products not have same option more once, product has of required options if number of duplicates equal number of ids. , that's having clause counting number of products.

scenario 2 & 3

scenarios 2 & 3 can handled same query. i'm going forgo consistency , chose criteria query because serves purpose best.

// example params scenario 2 def qparams = [     or: [1, 2], // these color option ids     and: 5 // size option id ]  // example params scenario 3 def qparams = [     or: [10, 11] // these brand option ids ]  product.withcriteria {     productoptions {         option {             if(qparams.and) eq('id', qparams.and.tolong())             inlist('id', qparams.or.collect({ it.tolong() }))                        }     } } 

the or parameter expected, if block adds and constraint if and parameter specified. notice ids option ids, have flexibility. instance, can search colors without size constraint.

about ids...

you'll notice in examples converted ids integers longs. if ids coming database, they're longs can take code out.