如何在 JPQL 查询中过滤子集合?

2022-01-18 00:00:00 spring java jpql jpa spring-data

我有以下数据库模型:

Category -< ProductCategory >- Product -< Variant

(Category 和 Product 是多对多关系，Product 和 Variant<是一对多关系/代码>)


(Category has many-to-many relationship with Product and Product has one-to-many relationship with Variant)
现在我需要获取所有 Category 记录，这些记录的产品具有 active 变体.我通过以下 JPQL 查询获取这些对象:

Now I need to get all Category records that have product with active variants. I'm getting these objects via the following JPQL query:
@Query("select distinct c from Category c join c.products as p join p.variants as pv where pv.active = true")

效果很好 - 准确返回类别 - 但是每个 Category 都包含 所有 产品 - 不仅 这些具有 active 变体.

It works well - returns categories accurately - however every single Category contains all the products - not only these with active variants. 
如何过滤掉在单个查询中处于非活动状态的产品(或变体)?

How can I filter out the products (or variants) that are inactive in a single query?
这里是一个包含数据库结构和示例数据的 postgres 脚本.对于给定的数据，两个类别(CAT 1、CAT 2)、两个产品(PROD 1、PROD 2)并且应该返回三个变体(VAR 1、VAR 2、VAR 3).

Here's a postgres script that with database struct and sample data. For given data two categories (CAT 1, CAT 2), two products (PROD 1, PROD 2) and three variants (VAR 1, VAR 2, VAR 3) should be returned.
推荐答案
我遇到了完全相同的问题，我花了一段时间才弄清楚它是如何工作的.当您在 JOIN 之后添加 FETCH 时，应过滤子列表，如下所示:

I had exactly the same problem and it took me a while to find out how this works. The child list should be filtered when you add FETCH after your JOIN like this:
SELECT DISTINCT c FROM Category c JOIN FETCH c.products as p join p.variants as pv where pv.active = true


	
		相关文章