Groovy createCriteria issue with joined table

Question

I have a domain class Coach which has a has many relationship to another domain class CoachProperty.

Hibernate/Grails is creating a third joined table in the database.

In the example below I am trying to fetch the coaches which both have foo AND bar for their text value. I have tried different solutions with 'or' and 'and' in Grails which either returns an empty list or a list with BOTH foo and bar.

Coach:

class Coach {

static hasMany = [ coachProperties : CoachProperty ]

CoachProperty:

class CoachProperty {

String text
boolean active = true

static constraints = {
    
    text(unique: true, nullable: false, blank: false)
}
}

Joined table which is being auto-created and I populated with some data, in this example I am trying to fetch coach 372 since that coach has both 1 and 2 i.e foo and bar:

+---------------------------+-------------------+
| coach_coach_properties_id | coach_property_id |
+---------------------------+-------------------+
|                       150 |                 2 |
|                       372 |                 1 |
|                       372 |                 2 |
|                        40 |                 3 |
+---------------------------+-------------------+

Inside Coach.createCriteria().list() among with other filters. This should return coach 372 but return empty:

def tempList = ["foo", "bar"]           
                    coachProperties{
                        for(String temp: tempList){
                            and {
                                log.info "temp = " + temp
                                ilike("text",temp)
                            }
                        }                       
                    }

Answer 1

I had to create a workaround with executeQuery where ids is the list containing the id's of the coachproperties i was trying to fetch.

    def coaches = Coach.executeQuery '''
                    select coach from Coach as coach 
                    join coach.coachProperties as props 
                    where props.id in :ids 
                    group by coach
                    having count(coach) = :count''', [ids: ids.collect { it.toLong() 
                    }, count: ids.size().toLong()]
                        
                    
                    or{
                        coaches.each{ 
                            eq("id", it.id)
                        }
                    }