英文:
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<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();
This fails with the message:
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.
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<Employee> criteria = builder.createQuery(Employee.class);
Root<Employee> root = criteria.from(Employee.class);
//Subquery to exists
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(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) > MIN_TASK
);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论