在JPA规范中订购 [英] Order by in JPA Specification

问题描述

我正在使用Spring JPA规范进行查询，并且无法实现与订购有关的用例.请注意，由于某些原因，我对规格不满意，无法切换到JPQL.

I am using Spring JPA Specifications for querying and I am unable to implement a use-case related to ordering. Please note that I am stuck with Specifications for some reason and cant switch to JPQL.

这是修剪后的域模型:

@Entity
public class Employee {
  @Id
  @GeneratedValue
  private long id;

  private String name;

  @OneToMany(mappedBy = "employee", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
  private List<Phone> phones; //contains both "active" & "inactive" phones
}


@Entity
public class Phone {
  @Id
  @GeneratedValue
  private long id;

  private boolean active;
  private String number;
  
  @ManyToOne(fetch = FetchType.LAZY)
  private Employee employee;
}

我需要撤出所有员工并根据他们拥有的活动电话的数量对其进行排序.我已经使用了代码-

I need to pull all the employees and order them depending on the count of active phones they have. I have used the code -

List<Order> orders = new ArrayList<>();
orders.add(cb.desc(cb.size(employee.get("phones"))));
cq.orderBy(orders);

当我运行代码时，要执行的查询已经

When I run the code, the query that gets executed has

ORDER BY (SELECT 
        COUNT(phone4_.employee_id)
    FROM
        phone phone4_
    WHERE
        employee4_.id = phone4_.employee_id) DESC
        
    
    

我尝试了很多事情，但是无法将其他条件添加到WHERE逻辑中，该条件仅检查活动" 电话.请提出建议.

I tried a lot of things but unable to add another condition to the WHERE logic which checks for "active" phones only. Please suggest.

更新:

我做了一些研究，并尝试了以下方法来实现用例.但是，出现错误-"org.hibernate.hql.internal.ast.QuerySyntaxException:意外的AST节点:查询"

I did some research and tried the following to achieve the use case. However, I get the error - "org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: query"

Subquery<Long> subquery = cq.subquery(Long.class);
Root<Phone> phRoot= subquery.from(Phone.class);
Predicate p1 = cb.equal(root.get("id"), phRoot.get("employee").get("id"));
Predicate p2 = cb.isTrue(phRoot.get("active"));

cq.distinct(true);
List<Order> orders = new ArrayList<>();
orders.add(cb.desc(subquery.select(cb.count(phRoot.get(ID))).where(p1, p2)));

推荐答案

为了使CriteriaBuilder按集合的大小排序，您需要通过添加< Collection> 来指定该属性为集合.代码>.

In order to make CriteriaBuilder sort on the size of a collection you need to specify that the property is a collection by adding <Collection>.

对于您的代码，这将是

orders.add(cb.desc(cb.size(employee.<Collection<Phone>>get("phones"))));

有关详细说明，请参见 https://stackoverflow.com/a/21027934/924036 .

Please see https://stackoverflow.com/a/21027934/924036 for a more thorough explanation.

这篇关于在JPA规范中订购的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！