GORM criteria query filtering where id in list

Question

I'm trying to add a clause to an existing GORM criteria to do some extra filtering.

I have various objects in my domain that are owned by an Organisation. For example, Product. Organisations can also own other organisations. I only want Users who belong to a particular Organisation to be able to see domain objects that belong to that Organisation or any of their Organisation's child Organisations.

There are a number of places in my application where I need to query for domain objects that are owned by Organisations. I want to create a DetachedCriteria instance that I can reuse throughout by application wherever I need to filter out objects that a User should not be allowed to see.

I've denormalised my model a bit to hopefully make things easier. When a new Organisation is inserted, it recursively adds itself to the hierarchyOrganisations collection of each of the Organisations above it.

class Organisation {
    ...
    static hasMany = [hierarchyOrganisations: Organisation]
    ...
}

One of the places I want to apply the organisation filtering DetachedCriteria is in Grails' scaffolding controller's list method, so I need to be able to paginate the final set of results.

Using the Product controller's list method as an example, I'm currently doing something like this -

def hierarchyCriteria = new DetachedCriteria(Organisation).build {
    eq('id', currentUserOrganisation.id)
}

def productInstanceList = Product.createCriteria().list {
    inList('parentOrganisation.id', hierarchyCriteria.list().each { organisation -> 
        organisation.hierarchyOrganisations.collect { it.id }
    })
}

...which works, but I don't think it will scale. I'll be hitting the database to bring back the collection of hierarchyOrganisations, then iterating over them in the application to pull out the id, and then querying again to bring back the final filtered list of organisations.

If I was using SQL, I could use a join or an inner select, but I can't seem to get the syntax right translating this to the GORM criteria dsl.

Answer 1

I just ended up using some SQL to augment the criteria.

By adding a sqlRestriction block to my criteria dsl I was able to add a subselect to apply the filtering I needed

I'd still be interested in finding out if this is possible using just the grails hibernate criteria dsl.