Search code examples
databasegrailsgrails-orm

Grails hasmany search for exact items of hasmany

I have two GORM domain classes. One is a card and the card has many purchases on it. is there any way i can search for cards that have greater than or equal to of certain purchases. Here might be an example

We have 4 cards:

Card1: orange, orange, apple Card2: apple, orange Card3: orange, orange Card4: apple, apple, orange

Lets say i wanted to search for all cards that have 2 or more oranges. so that would only return cards 1,3. Having the search based off of the purchases name.

Solution

  • Assuming your domain classes are something like this...

    class Card {
    static hasMany = [purchases: Purchase]
}

class Purchase {
    String name
}

    Here's how you can do it:

    def cards = Card.withCriteria {
    resultTransformer = org.hibernate.Criteria.DISTINCT_ROOT_ENTITY 

    sizeGe('purchases', 2)

    purchases {
        eq('name', 'orange')
    }
}

    Instead of using a subquery, you can use sizeGe() which counts the number of items in a collection and looks for a count greater than or equal to the number provided.

    However, adding eq() to the query causes the result to contain duplicates due to grouping. A side-effect a sub-query would not have. So the DISTICT_ROOT_ENTITY result transformer filters out the duplicates after the fact.

    You can learn more about criteria queries from my articles here.