Grails GORM 'or' not working with associat

问题:

In the following example, I'd expect Product.searchAll to match both additives and products, but it seems to ignore 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 SQL generated 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 this a problem with Grails, GORM or H2?

回答1:

My guess, quickly looking at your query, is that Grails / GORM is performing an inner join. An inner join only matches if a relationship exists between the tables. In the example above, that query will never match choc, because choc does not have any associated additives.

So, it's not the or that's failing, it's the actual query. Fire up localhost:8080/{yourapp}/dbConsole and run that same query, but without the where statement. You should see that you only get products with one or more additives.

I believe (not tested) you can force a LEFT JOIN using syntax like this:

import org.hibernate.criterion.CriteriaSpecification
...

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

This should force a left (or outer) join, allowing for products that do not have a matching additive. Note: It's possible to get duplicate results when using outer joins, but this depends on your particular usage scenario.