Grails:如何最好地构建一个休眠条件构建器来搜索与域实例的“hasMany"关系 [英] Grails : how to best construct a hibernate criteria builder to search 'hasMany' relationships with domain instance

问题描述

我正在处理一个 grails 项目，并希望利用休眠条件构建器来搜索域对象的实例.我想找到其中一个hasMany"关系包含具有特定 id 的域对象的实例.这是我的意思的一个例子.

I am working on a grails project and would like to leverage hibernate criteria builders to search for instances of a domain object. I would like to find instances where one of the 'hasMany' relationships contains domain object with certain ids. Here is an example of what I mean.

域对象

class Product {
   static hasMany = [ productOptions: ProductOption ]
}

class ProductOption{
   Option option
   static belongsTo = [ product: Product ]
}

class Option{
   String name
}

这是我的域结构的简化示例，不包括所有关系.

This is a simplified example of my domain structure and doesn't include all relationships.

Option 可以是尺寸、颜色、品牌等.

An Option could be size, color, brand, etc.

我想要实现的示例

假设我有 3 个产品.

Lets say I have 3 products.

Product 1 is red, small and by brandx

Product 2 is blue, small and by brandx

Product 3 is yellow, medium and by brandz

我需要涵盖一些场景.

场景一

• 查找蓝色、小号和按品牌 x 的产品.所以在这种情况下，我应该只返回产品 2.

场景 2

• 查找红色或蓝色且尺寸较小的产品.因此，应退回产品 1 和产品 2.

场景 3

• 查找按brandx 或brandz 分类的产品.所以所有产品都应该退回.

我希望这涵盖所有场景.

I hope this covers all scenarios.

这是当前尝试的一个示例.

This is an example of a current attempt.

def c = Product.createCriteria()
def products = c.list{
    and {
        productOptions {
            'option' {
                idEq(1)//1 is the id of the blue option
            }
        }
        productOptions {
            'option' {
                idEq(5)//5 is the id of the small size option
            }
        }
        productOptions {
            'option' {
                idEq(10)//10 is the id of the brandx brand option
            }
        }
    }
}

此示例的 和 部分不包括所有选项并且失败.我如何最好地实现这一目标?我可以使用 Grails 休眠标准构建器来实现这一目标吗?如果其他信息有帮助，请告诉我.

The and portion of this example doesn't include all options and fails. How do I best achieve this? Can I use Grails hibernate criteria builder to achieve this? Please let me know if additional information will help.

预先感谢您提供的任何指导.

Thanks in advance for any guidance provided.

推荐答案

您正在寻找的相当于 Groovy 的 Object.every(Closure).

What you're looking for is the equivalent of Groovy's Object.every(Closure).

assert [1, 2, 3].every { it <4 } == 真断言 [1, 2, 3].every { it <3 } == 假

assert [1, 2, 3].every { it < 4 } == true assert [1, 2, 3].every { it < 3 } == false

every() 方法返回一个布尔值，指示闭包是否对集合中的每一项求值为真.

The every() method returns a Boolean indicating whether the Closure evaluates to true for every item in the collection.

遗憾的是，没有任何查询方法(where、criteria 和 HQL)提供与 every() 等效的功能.但是……你可以使用 HQL 作弊.

Unfortunately, none of the query methods (where, criteria, and HQL) provide an equivalent of every(). But... you can cheat using HQL.

注意:Where 和 Criteria 查询都不行，因为它们不支持等效的 HQL HAVING 子句.

def ids = [4, 5, 6] // List of Option ids.

Product.executeQuery '''
select prd from Product as prd 
    join prd.productOptions as prdopts 
    join prdopts.option as opt 
where opt.id in :ids 
group by prd
having count(prd) = :count''', [ids: ids.collect { it.toLong() }, count: ids.size().toLong()]

工作原理

查询首先选择所有 Product，这些 Product 在ids 列表.只要产品具有至少一个选项，它就会被退回.

How it works

The query begins by selecting all of the Products which have any of the Options in the ids list. As long as a Product has at least one of the options it will be returned.

这会产生为每个匹配选项列出一个 Product 的副作用.例如，如果 Product 具有三个 Option，那么 Product 将返回三次.GROUP BY 子句使查询过滤掉那些重复的列表.

This produces the side-effect of listing a Product for every matching option it has. For instance, if a Product has three of the Options, then the Product is returned three times. The GROUP BY clause makes the query filter out those duplicate listings.

然而，这些重复是这次黑客攻击的关键:如果 ID 列表是一个唯一列表，并且 Product 没有不止一次具有相同的 Option，如果重复的数量等于 ID 的数量，则 Product 具有所有必需的 Option.这就是 HAVING 子句通过计算 Product 的数量所做的.

However, those duplicates are key to this hack: if the list of IDs is a unique list, and Products do not have the same Option more than once, then the Product has all of the required Options if the number of duplicates is equal to the number of IDs. And that's what the HAVING clause does by counting the number of Products.

场景 2 &3 可以由同一个查询处理.我将放弃一致性并选择 Criteria 查询，因为它最适合此目的.

Scenarios 2 & 3 can be handled by the same query. I'm going to forgo consistency and chose a Criteria query because it serves this purpose best.

// Example params for scenario 2
def qparams = [
    or: [1, 2], // These are color Option IDs
    and: 5 // This is a size Option ID
]

// Example params for scenario 3
def qparams = [
    or: [10, 11] // These are brand Option IDs
]

Product.withCriteria {
    productOptions {
        option {
            if(qparams.and) eq('id', qparams.and.toLong())
            inList('id', qparams.or.collect({ it.toLong() }))               
        }
    }
}

or 参数总是需要的，但是 if 块只添加 and 约束，如果 and参数被指定.请注意，ID 都只是选项 ID，因此您具有一定的灵活性.例如，您可以在没有大小限制的情况下搜索任何颜色.

The or parameter is always expected, but the if block only adds the and constraint if the and parameter is specified. Notice that the IDs are all just Option IDs, so you have some flexibility. For instance, you can search for any colors without a size constraint.

您会注意到，在我的示例中，我将 IDS 从整数转换为长整数.如果您的 ID 来自数据库，那么它们已经是多头，因此您可以取出该代码.

You'll notice that in my examples I converted the IDS from Integers to Longs. If you IDs are coming from the database, then they're already Longs so you can take that code out.

这篇关于Grails:如何最好地构建一个休眠条件构建器来搜索与域实例的“hasMany"关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！