spring - 带规范的可分页排序返回重复的结果

Question

我有一个 Service 实体，它有一个 Jobs 字段，它在 ManyToMany 关系中有 Job 实体。

过滤、排序和分页工作完美，但唯一的问题是按作业排序。

当我向我发送请求时，/serviceslist?sort=jobs&sortDir=asc我会为每个连接了多个作业的服务获得重复的结果。更具体地说，如果一个 Service 有 4 个作业，则该服务将返回 4 次，依此类推。

我想我应该使用 groupBy，但是我将如何在下面的服务中实现 groupBy？

服务：

public Page<com.bitweb.syda.data.entity.service.Service> getServicesList(ServiceListRequest request, Pageable pageable) {
        Specification<com.bitweb.syda.data.entity.service.Service> spec = where(null);

        if (request.getSearch() != null) spec = spec.and(search(request.getSearch()));
        if (request.getName() != null) spec = spec.and(name(request.getName()));
        if (request.getJobs() != null) spec = spec.and(hasJobs(request.getJobs()));
        if (request.getNeedsPatrol() != null) spec = spec.and(needsPatrol(request.getNeedsPatrol()));

        return serviceRepository.findAll(spec, pageable);
    }

控制器：

@RequestMapping(path = "/serviceslist", method = RequestMethod.GET)
    public Page<ServiceResponse> getServicesList(
            @RequestParam(defaultValue = "0") Integer page,
            @RequestParam(defaultValue = "10") Integer size,
            @RequestParam(required = false) String search,
            @RequestParam(required = false) String name,
            @RequestParam(required = false) String jobs,
            @RequestParam(required = false) Boolean needsPatrol,
            @RequestParam(defaultValue = "createTime") String sort,
            @RequestParam(defaultValue = "asc") String sortDir
    ) {

        ServiceListRequest request = new ServiceListRequest(search, name, jobs, needsPatrol);

        Sort.Direction direction;

        if (sortDir.equals("asc")) {
            direction = Sort.Direction.ASC;
        } else {
            direction = Sort.Direction.DESC;
        }

        return serviceService.getServicesList(request, of(page, size, direction, sort))
                .map(ServiceResponse::new);
    }

以下是我已经尝试过的一些示例：

public Page<com.bitweb.syda.data.entity.service.Service> getServicesList(ServiceListRequest request, Pageable pageable) {
        Specification<com.bitweb.syda.data.entity.service.Service> spec = (root, query, builder) -> {
            //you can do any check here if you want with the join and check all the search parameters here if you want
            //Join<Object, Object> jobs = root.join("jobs");
            // also set query to get distinct values
            query.distinct(true);
            return null;
        };

    if (request.getSearch() != null) spec = spec.and(search(request.getSearch()));
    if (request.getName() != null) spec = spec.and(name(request.getName()));
    if (request.getJobs() != null) spec = spec.and(hasJobs(request.getJobs()));
    if (request.getNeedsPatrol() != null) spec = spec.and(needsPatrol(request.getNeedsPatrol()));

    return serviceRepository.findAll(spec, pageable);
}

尝试后一切都按预期工作，但按作业排序会给我这个错误：

PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
  Position: 652

我对 SQL 和 Criteria API 不是很精通，我不知道我能做些什么来解决这个问题。

使用query.groupBy(root);而不是 distinct 时，我收到此错误：

PSQLException: ERROR: column "job4_.id" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 780

标签： springspring-bootspring-data-jpacriteria-api