querydsl 3.3.0 - 在带有 N 个布尔参数的查询中使用 collection.any() 和 elementcollections 生成 N 个“存在"；子查询 [英] querydsl 3.3.0 - using collection.any() with elementcollections in a query with N boolean arguments generates N "exists" sub-queries

问题描述

我正在尝试在 ElementCollection 中查询符合 2 个条件的特定项目.当我在 QueryDSL 中编写查询时，Hibernate 生成的查询包含 2 个带有子查询的存在语句，每个子查询包含我指定的条件之一.下面是一个例子:

I am attempting to query for a specific item in an ElementCollection that matches 2 criteria. When I write the query in QueryDSL, the query generated by Hibernate includes 2 exists statements with subqueries, each subquery contains 1 of the criterion I specified. Here is an example:

@Entity
public class Person {
   @Id
   private Integer id;
}

@Entity
public class Project {

   @Id
   private Integer id;
   private boolean canned;

   @ElementCollection(fetch=FetchType.EAGER, targetClass=ProjectMember.class)
   @CollectionTable(name="Project_Person", joinColumns={@JoinColumn(name="projectId", nullable=false)})
   private Set<ProjectMember> members;
}

@Embeddable
public class ProjectMember {

   private Integer personId;
   private Integer projectId;
   private boolean overworked;
   private boolean underpaid;
}

现在，如果我尝试执行以下操作，我将获得多个存在子查询，每个子查询都有我指定的一个条件:

Now if I try to do the following, I will get multiple exists subqueries, each having one criterion I specify:

  QProject project = QProject.project;
  QProjectMember members = project.members.any();
  new HibernateQuery(sessionFactory.getCurrentSession())
  .from(project).where(project.canned.eq(true).or(
  members.overworked.eq(true).and(members.underpaid.eq(true)))).list(project);

这是输出查询:

select project
from Project project
where project.canned = ?1 or exists (select 1
from Project projectd92b4
  inner join projectd92b4.members as project_membersaeb8a
where projectd92b4 = project and project_membersaeb8a.overworked = ?1) and exists (select 1
from Project project4b8ff
  inner join project4b8ff.members as project_membersb6de1
where project4b8ff = project and project_membersb6de1.underpaid = ?1)

如您所见，有 2 个存在子查询的语句，每个语句都具有 2 个条件之一.这个查询不会产生正确的结果，因为我不想要一个项目，其中至少有 1 名成员工作过度"，至少有 1 名成员工资不足"；我想要一个项目，其中至少有 1 个项目成员工作过度"和薪酬过低".我是误解了any()"的正确用法还是这是一个错误?

As you can see, there are 2 exists statements with subqueries each having one of the 2 criteria. This query will not produce the correct results as I don't want a project where at least 1 of the members is "overworked" and at least 1 member is "underpaid"; I want a project where at least 1 project member is both "overworked" AND "underpaid." Am I misunderstanding the proper use of "any()" or is this a bug?

推荐答案

每个 any() 用法都被转换成它自己的子查询.如果需要组合多个条件，则需要自己编写子查询.

Each any() usage is converted into it's own subquery. If you need to combine multiple conditions you will need to write the subquery yourself.

any() 不是连接替代，但子查询存在快捷方式.也许这会有所帮助.

any() isn't a join alternative, but a subquery exists shortcut. Maybe that helps.

这篇关于querydsl 3.3.0 - 在带有 N 个布尔参数的查询中使用 collection.any() 和 elementcollections 生成 N 个“存在"；子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！