Geek asks and answers

Grails GORM 'or' does not work with associations

In the following example, I expect Product.searchAll to match both additives and products, but it seems to ignore it eq('name', taste).

class Additive {
    String flavor
    static belongsTo = [product:Product]
}

class Product {
    String name
    static hasMany = [additives:Additive]

    static constraints = {
            name nullable:true
    }

    static namedQueries = {
        searchAll { taste ->
            or {
                eq('name', taste)
                additives { eq('flavor', taste) }
            }
        }
        searchAdditives { taste ->
            additives { eq('flavor', taste) }
        }
        searchProducts { taste ->
            eq('name', taste)
        }
    }
}

class SearchSpec extends grails.plugin.spock.IntegrationSpec {
    def choc, muff

    def 'searchAll should return products and additives that match - THIS FAILS'() {
        setup:
            createTestProducts()
        expect:
            Product.searchAll("chocolate").list() == [choc, muff]
    }


    def 'searchProducts should return only products that match - THIS PASSES'() {
        setup:
            createTestProducts()
        expect:
            Product.searchProducts("chocolate").list() == [choc]
    }

    def 'searchAdditives should return only additives that match - THIS PASSES'() {
        setup:
            createTestProducts()
        expect:
            Product.searchAdditives("chocolate").list() == [muff]
    }

    private def createTestProducts() {
        // create chocolate
        choc = new Product(name:'chocolate').save(failOnError:true, flush:true)
        // create a chocoloate-flavored muffin
        muff = new Product(name:'muffin').addToAdditives(flavor:'chocolate').save(failOnError:true, flush:true)
    }
}

The generated SQL is as follows:

select this_.id as id1_1_, this_.version as version1_1_,
this_.name as name1_1_, additives_1_.id as id0_0_,
additives_1_.version as version0_0_, additives_1_.flavor as
flavor0_0_, additives_1_.product_id as product4_0_0_ from product
this_ inner join additive additives_1_ on
this_.id=additives_1_.product_id where (this_.name=? or
(additives_1_.flavor=?))

Is there something wrong with my syntax, or is it a problem with Grails, GORM or H2?

+3
source share
1 answer

My guess, looking quickly at your request, is what Grails / GORM does inner join. The inner join only matches between relationships between tables. In the above example, this query will never match chocbecause it chochas no related additives.

, or, . localhost: 8080/{yourapp}/dbConsole , where. , .

( ) LEFT JOIN :

import org.hibernate.criterion.CriteriaSpecification
...

    searchAll { taste ->
        createAlias("additives", "adds", CriteriaSpecification.LEFT_JOIN)
        or {
            eq('name', taste)
            eq('adds.flavor', taste)
        }
    }

( ) , , . . , .

+5

More articles:


All Articles