Querying associations with enum collections in Grails

Question

I'm trying a query in Grails 1.2.1, find all products by tenant type.

My solution works but is very inefficient, first I retrieve all products and then find all matching results for the given tenant.

I found a related bug in JIRA: Enum as collection

class Product {
    Set<TenantType> tenants
    static hasMany = [tenants: TenantType]
}

enum TenantType {
    BICYCLE,
    MOTORCYCLE
}

def tenant = TenantType.BICYCLE
Product.list().findAll { product -> tenant in product.tenants }

Is there a more efficient way of querying for this data?

Possible duplicate of [Grails select domain objects based on an enum value in an enum list property](http://stackoverflow.com/questions/4829823/grails-select-domain-objects-based-on-an-enum-value-in-an-enum-list-property) — Vinay Prajapati, Oct 06 '15 at 10:25

score 3 · Accepted Answer · edited May 23 '17 at 11:44

3

A similar question was asked here and as pointed out in the answer, it looks like Hibernate doesn't support criteria queries over collections of value types like enums. One option is to use an hql query instead:

Product.executeQuery('from Product p inner join p.tenants tenants
                      where tenants = :tenant', [tenant: TenantType.BICYCLE])

edited May 23 '17 at 11:44

Community

1
1

answered Mar 26 '12 at 09:22

Geli

56
2

score 1 · Answer 2 · answered Oct 17 '17 at 12:18

1

Can be executed without join:

Product.executeQuery('from Product where :tenant in elements(tenants)', [tenant: TenantType.BICYCLE])

answered Oct 17 '17 at 12:18

Koloritnij

1,167
1
8
15

score 0 · Answer 3 · answered Oct 18 '17 at 12:27

Use named query in Product class something like

findByTenantType { tenantType->
        tenants{ eq 'value', tenantType}

    }

And then access this named query like this -

def product = Product .findByTenantType(TenantType.BICYCLE).get()

see similar blog - http://www.grailsbrains.com/search-parent-through-child-in-aggregation-relationship/

Querying associations with enum collections in Grails

3 Answers3