Grails Detached Criteria with Composite key

Question

Domain Setup .

Study {
    Long id
    String name
    Site site
    USState state
    ...
}

.

Site {
    Long id
    String name
    ...
}

.

Resource {
    Long id
    String name
    Boolean active
    USState state
    ...
}

.

SiteResource {
    id composite: ['site', 'resource']
    Site site
    Resource resource
}

I have 2 lists that get populated for the Study. One is a list of currently associated Resources to the Site Another is a list of available Resources that can be associated to the Site. These available Resources are determined by being Active = true and within the same USState as the Study

I'm trying to write a criteria to get this to work but cannot seem to get it. I need to show all Resources that are Active, in the same USState as the Study and not currently associated with the Site (the current associations are in the first table)

return Resource.createCriteria().list(sort: params.sort, order: params.order, max: params.max, offset: params.offset) {
        if (params.searchText) {
            ilike("name",  "%${params.searchText}%")
        }

        eq("usState", params.state)
        eq("active", true)

        ne "id" , new DetachedCriteria(SiteResource).build {
            'in' ("resource", params.associatedResources.resource.id)
            'in' ("site", params.associatedResources.site.id)
        }
    }

I've also tried the following:

def siteResources = getSiteResourcesBySite(site).collect {
    it.resource
}

def resources = resource.findAllByRefugeAndActiveNotInList(refuge, true, siteResources, [sort:"name"])

return resources.list(sort: params.sort, order: params.order, max: params.max, offset: params.offset)

I hope this made sense; I'm still trying to figure out how the DetachedQuery works. If there is a better way to accomplish this task please enlighten me!

score 0 · Answer 1 · answered Nov 06 '14 at 19:08

Criteria builder was not needed. Here's my solution:

def site = siteService.getSite(params.siteId)
def stateCode = site.study.state.code
def state= State.findByCode(stateCode )

List<Resource> associatedResources = siteResourceService.getAssociatedResourcesBySite(site).collect { it.id }

params.state = state
params.site = site

...

def getUnassociatedResourcesByState(params, List associatedResources) {
    params.max = Math.min(params.max ? Integer.parseInt(params.max) : 25, 100)
    params.offset =  params.offset ? params.offset : 0
    params.sort = params.sort ? params.sort : 'name'
    params.order = params.order == 'desc' ? params.order : 'asc'

    return Resource.findAllByStateAndActiveAndIdNotInList(params.state, true, associatedResources, [sort: params.sort, order: params.order, max: params.max, offset: params.offset])
}

Grails Detached Criteria with Composite key

1 Answers1