如何选择在ManyToOne关系中由n个父实体引用的子实体？

huangapple
go评论95阅读模式
英文:

How to select a child entity that is referenced by n parent entities in a ManyToOne relationship?

问题

我有两个实体：

  1. @Entity
  2. public class Task {
  3.   @Id
  4.   private long id;
  6.   @ManyToOne(fetch = FetchType.EAGER)
  7.   private Employee employee;
  8.   //... 省略部分内容
  9. }
  11. @Entity
  12. public class Employee {
  13.   @Id
  14.   private long id;
  15.   private String name;
  16.   //... 省略部分内容
  17. }

现在我想选择所有至少分配给 minTasks 个任务的员工。
我尝试了以下代码：

  1. CriteriaBuilder builder = session.getCriteriaBuilder();
  2. CriteriaQuery<Employee> criteria = builder.createQuery(Employee.class);
  3. Root<Task> root = criteria.from(Task.class);
  5. //Join<Task, Employee> join = root.join(Task_.EMPLOYEE);
  6. //criteria.select(join);
  7. //criteria.groupBy(join);
  8. /*criteria.having(
  9.     builder.greaterThanOrEqualTo(
  10.       builder.count(join),
  11.       minTasks
  12.     )
  13.   );*/
  15. criteria.select(root.get(Task_.EMPLOYEE));
  16. criteria.groupBy(root.get(Task_.EMPLOYEE));
  17. criteria.having(
  18.   builder.greaterThanOrEqualTo(
  19.     builder.count(root.get(Task_.EMPLOYEE)),
  20.     minTasks
  21.   )
  22. );
  24. Query<Employee> query = session.createQuery(criteria);
  25. return query.getResultList();

但是这会出现错误消息：

  1. Column reference 'Employee_.ID' is invalid, or is part of an invalid expression.  For a SELECT list with a GROUP BY, the columns and expressions being selected may only contain valid grouping expressions and valid aggregate expressions.

但我实际上不太明白这个消息试图告诉我什么。有谁可以指点我正确的方向吗？

英文:

I have two entities:

  1. @Entity
  2. public class Task {
  3.   @Id
  4.   private long id;
  6.   @ManyToOne(fetch = FetchType.EAGER)
  7.   private Employee employee;
  8.   //... omitted for brevity
  9. }
  11. @Entity
  12. public class Employee {
  13.   @Id
  14.   private long id;
  15.   private String name;
  16.   //... omitted for brevity
  17. }

Now I want to select all the employees that are assigned to at least minTasks tasks.
I tried it like this:

  1. CriteriaBuilder builder = session.getCriteriaBuilder();
  2. CriteriaQuery&lt;Employee&gt; criteria = builder.createQuery(Employee.class);
  3. Root&lt;Task&gt; root = criteria.from(Task.class);
  5. //Join&lt;Task, Employee&gt; join = root.join(Task_.EMPLOYEE);
  6. //criteria.select(join);
  7. //criteria.groupBy(join);
  8. /*criteria.having(
  9.     builder.greaterThanOrEqualTo(
  10.       builder.count(join),
  11.       minTasks
  12.     )
  13.   );*/
  15. criteria.select(root.get(Task_.EMPLOYEE));
  16. criteria.groupBy(root.get(Task_.EMPLOYEE));
  17. criteria.having(
  18.   builder.greaterThanOrEqualTo(
  19.     builder.count(root.get(Task_.EMPLOYEE)),
  20.     minTasks
  21.   )
  22. );
  24. Query&lt;Employee&gt; query = session.createQuery(criteria);
  25. return query.getResultList();

This fails with the message:

  1. Column reference &#39;Employee_.ID&#39; is invalid, or is part of an invalid expression.  For a SELECT list with a GROUP BY, the columns and expressions being selected may only contain valid grouping expressions and valid aggregate expressions.

But I do not really get what this message is trying to tell me. Can anyone point me in the right direction?

答案1

得分: 1

我将查询重构如下，因为在GROUP BY子句中必须有与Employee实体的属性数量相同的参数：

  1. CriteriaBuilder builder = session.getCriteriaBuilder();
  2. CriteriaQuery<Employee> criteria = builder.createQuery(Employee.class);
  3. Root<Employee> root = criteria.from(Employee.class);
  5. // 子查询用于存在性判断
  6. Subquery<Long> numOfTasksSQ = criteria.subquery(Long.class);
  7. Root<Task> rootSQ = numOfTasksSQ.from(Task.class);
  8. Join<Task, Employee> joinSQ = rootSQ.join(Task_.employee, JoinType.INNER);
  9. numOfTasksSQ.select(builder.count(joinSQ.get(Employee_.id)));
  10. numOfTasksSQ.where(builder.equal(joinSQ.get(Employee_.id), root.get(Employee_.id))));
  12. criteria.where(builder.greaterThanOrEqualTo(numOfTasksSQ.getSelection(), MIN_TASK));
  14. criteria.select(root);

查询结果将会类似于这样：

  1. SELECT e.*
  2. FROM employee e
  3. WHERE (
  4.     (SELECT COUNT(e2.id)
  5.     FROM task t
  6.     INNER JOIN employee e2 ON t.employee_id = e2.id
  7.     WHERE e2.id = e.id) > MIN_TASK
  8. );
英文:

I would refocus the query as follows, since you have to have as many parameters in the group by as there are properties in the Employee entity:

  1. CriteriaBuilder builder = session.getCriteriaBuilder();
  2. CriteriaQuery&lt;Employee&gt; criteria = builder.createQuery(Employee.class);
  3. Root&lt;Employee&gt; root = criteria.from(Employee.class);
  5. //Subquery to exists
  6. Subquery&lt;Long&gt; numOfTasksSQ= criteria.subquery(Long.class);
  7. Root&lt;Task&gt; rootSQ = numOfTasksSQ.from(Task.class);
  8. Join&lt;Task,Employee&gt; joinSQ = rootSQ.join(Task_.employee,JoinType.INNER);
  9. numOfTasksSQ.select(cb.count(joinSQ.get(Employee_.id)));
  10. numOfTasksSQ.where(cb.equal(joinSQ.get(Employee_.id),root.get(Employee_.id)));
  12. criteria.where(cb.greaterThanOrEqualTo(numOfTasksSQ.getSelection(),MIN_TASK ));
  14. criteria.select(root);

the result would be a query like this:

  1. select e.*
  2. from employee
  3. where (
  4.     (select count(e2.id)
  5.     from task t
  6.     inner join employee e2 on t.employe_id = e2.id
  7.     where e2.id = e.id) &gt; MIN_TASK
  8. );

huangapple
  • 本文由 发表于 2020年9月11日 15:30:31
  • 转载请务必保留本文链接：https://go.coder-hub.com/63842615.html
  • hibernate
  • java
  • jpa
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定