如何在JPQL查询中过滤子集合? [英] How to filter child collection in JPQL query?

问题描述

我有以下数据库模型:

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)

现在，我需要获取所有具有 active 变体的产品的Category记录.我通过以下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

这篇关于如何在JPQL查询中过滤子集合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！