(Quick Reference)

5 Nested Queries - Reference Documentation

Authors: Grails Plugin Consortium

Version: 2.4.4

5 Nested Queries

Nested Queries

Nested queries are supported for children objects and collections. To include these in the filterpane tag lib simply use something similar to the following given that a Robot 1- Part 1- Function or

robot*.parts*.functions

<filterpane:filterPane domain="org.grails.example.nested.Robot"
                         additionalProperties="identifier"
                         associatedProperties="parts.functions.name, parts.name"
                         titleKey="fp.tag.filterPane.titleText"
                         dialog="true"
                         visible="n"
                         showSortPanel="n"
                         showTitle="n"
                         listDistinct="true"
                         distinctColumnName="id"
                         fullAssociationPathFieldNames="false"/>

There were two new columns added to the filterPane taglib to help resolve the createCriteria issue where querying on child objects that match multiple parents. Without using the listUnique() criteria method and providing a projection column name you would return duplicated resultsets.

Take for example the following test:

def "test nested criteria call without distinct"() {
    given:
    Book.findOrSaveWhere(title: 'i like turtles', coAuthor: Author.findOrSaveWhere(firstName: 'Co', lastName: 'Author'))
            .addToAuthors(Author.findOrSaveWhere(firstName: 'Cool', lastName: 'Dude'))
            .addToAuthors(Author.findOrSaveWhere(firstName: 'Another', lastName: 'Dude'))
    Book.findOrSaveWhere(title: 'think about it')    def c = Book.createCriteria()
    when:
    def books = c.list {
        and {
            'authors' {
                eq('lastName', 'Dude')
                order('lastName', 'asc')
            }
        }
    }
    then: 'Incorrect size due to multiple matched children'
    Book.list().size() == 2
    books.size() == 2
}

What happens is that multiple children match the nested query and the parent is added once for each matching child. To correct this you would need to use the `listDistinct` of the criteria. We achieve this by adding two additional parameters to the filterpane tag.

`listDistinct` A boolean to tell the query to use listDistinct
`distinctColumnName` - A string with the column name to group by in the count query (of parent class)

<filterpane:filterPane domain="org.grails.example.nested.Robot"
    …
    listDistinct="true"
    distinctColumnName="id"
    "/>

<< 4Templates

6Reference >>