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

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

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

问题

我有两个实体:

@Entity
public class Task {
  @Id
  private long id;

  @ManyToOne(fetch = FetchType.EAGER)
  private Employee employee;
  //... 省略部分内容
}

@Entity
public class Employee {
  @Id
  private long id;
  private String name;
  //... 省略部分内容
}

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

CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Employee> criteria = builder.createQuery(Employee.class);
Root<Task> root = criteria.from(Task.class);

//Join<Task, Employee> join = root.join(Task_.EMPLOYEE);
//criteria.select(join);
//criteria.groupBy(join);
/*criteria.having(
    builder.greaterThanOrEqualTo(
      builder.count(join),
      minTasks
    )
  );*/

criteria.select(root.get(Task_.EMPLOYEE));
criteria.groupBy(root.get(Task_.EMPLOYEE));
criteria.having(
  builder.greaterThanOrEqualTo(
    builder.count(root.get(Task_.EMPLOYEE)),
    minTasks
  )
);

Query<Employee> query = session.createQuery(criteria);
return query.getResultList();

但是这会出现错误消息:

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:

@Entity
public class Task {
  @Id
  private long id;

  @ManyToOne(fetch = FetchType.EAGER)
  private Employee employee;
  //... omitted for brevity
}

@Entity
public class Employee {
  @Id
  private long id;
  private String name;
  //... omitted for brevity
}

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

CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery&lt;Employee&gt; criteria = builder.createQuery(Employee.class);
Root&lt;Task&gt; root = criteria.from(Task.class);

//Join&lt;Task, Employee&gt; join = root.join(Task_.EMPLOYEE);
//criteria.select(join);
//criteria.groupBy(join);
/*criteria.having(
    builder.greaterThanOrEqualTo(
      builder.count(join),
      minTasks
    )
  );*/

criteria.select(root.get(Task_.EMPLOYEE));
criteria.groupBy(root.get(Task_.EMPLOYEE));
criteria.having(
  builder.greaterThanOrEqualTo(
    builder.count(root.get(Task_.EMPLOYEE)),
    minTasks
  )
);

Query&lt;Employee&gt; query = session.createQuery(criteria);
return query.getResultList();

This fails with the message:

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实体的属性数量相同的参数:

CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Employee> criteria = builder.createQuery(Employee.class);
Root<Employee> root = criteria.from(Employee.class);

// 子查询用于存在性判断
Subquery<Long> numOfTasksSQ = criteria.subquery(Long.class);
Root<Task> rootSQ = numOfTasksSQ.from(Task.class);
Join<Task, Employee> joinSQ = rootSQ.join(Task_.employee, JoinType.INNER);
numOfTasksSQ.select(builder.count(joinSQ.get(Employee_.id)));
numOfTasksSQ.where(builder.equal(joinSQ.get(Employee_.id), root.get(Employee_.id))));

criteria.where(builder.greaterThanOrEqualTo(numOfTasksSQ.getSelection(), MIN_TASK));

criteria.select(root);

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

SELECT e.*
FROM employee e
WHERE (
    (SELECT COUNT(e2.id)
    FROM task t
    INNER JOIN employee e2 ON t.employee_id = e2.id
    WHERE e2.id = e.id) > MIN_TASK
);
英文:

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:

CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery&lt;Employee&gt; criteria = builder.createQuery(Employee.class);
Root&lt;Employee&gt; root = criteria.from(Employee.class);

//Subquery to exists
Subquery&lt;Long&gt; numOfTasksSQ= criteria.subquery(Long.class);
Root&lt;Task&gt; rootSQ = numOfTasksSQ.from(Task.class);
Join&lt;Task,Employee&gt; joinSQ = rootSQ.join(Task_.employee,JoinType.INNER);
numOfTasksSQ.select(cb.count(joinSQ.get(Employee_.id)));
numOfTasksSQ.where(cb.equal(joinSQ.get(Employee_.id),root.get(Employee_.id)));

criteria.where(cb.greaterThanOrEqualTo(numOfTasksSQ.getSelection(),MIN_TASK ));

criteria.select(root);

the result would be a query like this:

select e.*
from employee
where (
    (select count(e2.id)
    from task t
    inner join employee e2 on t.employe_id = e2.id
    where e2.id = e.id) &gt; MIN_TASK
);

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

发表评论

匿名网友

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

确定