grails/hibernate criteria how to get all rows that have at least one child meeting condition

My understanding is that nested sub queries filter the children. not the parent. In other words

  Parent.withCriteria{
           children{
               gt('age', 5)
           }

  }

would return all Parents, but only the children of those parents that are over 5.

Using the example as reference, what I want is to get ONLY the parents that have at least one child over the age of 5, and get all the children for those parents.

The query that I need this equivalent for is...

  SeasonDetails.withCriteria{
            not{
                clubHistory{
                    club{
                        eq('division',division);
                    }
                }
            }
    }

where I am only getting the season details that are not linked to a certain club.

Does HQL work differently? Maybe I need to go that route instead

Solution

string query="""select new map(p as parent, c as children) from Parent p left join p.children c where c.age > :ageLimit"""
def inputParams=[:]
inputParams.ageLimit = 5
def results = Parent.executeCriteria(query,inputParams)
result?.each { r ->
   println "${r?.parent?.id} is parent id "
   r?.children?.eachWithIndex { c, i ->
    println "${c.id} ${c.age} is iteration ${i}"
   }
}

above should be a query in HQL to do what you need, at the moment it is returning entire objects within map. This can be expensive and cause unnecessary look up, (when iterating above it is having to connect through and get it again)

What you could do instead it capture all you need and use groupBy to get exactly what you need so ..

     string query="""select new map(p.name as parentName, p.id as parentId
 , c.id as childId, c.age as childAge, c.name as childName) from Parent p 
left join p.children c where c.age > :ageLimit"""

This should now return a flat list of all you need if the ids name and age is what you wanted. now you have per parent the amount of children so if parent 1 has 6 children it is repeated twice (as you can see in the iteration above)

you can use groupBy

def finalList = results.groupBy{it.parentId}

this will now change the above list to a per parent group containing each child as a list iteration..

hql also has group by you may need to play around to see what works best for you.

Major difference between query 1 and query 2 is that query 1 when iterated will still interact with database to return the actual objects. In query2 you are returning all results as flat iteration and is no longer connected to actual real database objects. If you enable SQL debugging you will see how much less work goes on through 2nd method.