Grails的GORM'或'不与协会合作 [英] Grails GORM 'or' not working with associations

问题描述

在下面的例子中，我期望Product.searchAll同时匹配
添加剂和产品，但似乎忽略 EQ（'名'，味道）。

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)
    }
}

生成的SQL是如下：

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=?))

是不是有什么毛病我的语法，或者这是使用Grails，GORM或H2？

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

推荐答案

我的猜测，很快在看您的查询，是Grails的/格姆正在执行内加入。内部联接仅当表之间存在的关系相匹配。在上面的例子中，该查询会的从不的匹配巧克力，因为巧克力没有任何相关的添加剂。

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.

所以，这不是或就是失败，它的实际查询。火起来的本地主机：8080 / {} yourapp / DBCONSOLE 并运行相同的查询，但没有其中，语句。你应该看到，你只能得到产品的一种或多种添加剂。

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.

我相信（未测试），你可以强制 LEFT JOIN 使用的语法如下：

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.

这篇关于Grails的GORM'或'不与协会合作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！