使用“可分页”进行排序时出现重复项

Question

控制器：

@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);

    }

服务：

@Entity

@Data

@Table(name = "service")

public class Service {

    @Id

    @GeneratedValue(strategy = GenerationType.IDENTITY)

    @Column(unique = true, nullable = false, columnDefinition = "serial")

    private Long id;

    @Column(name = "name", nullable = false)

    private String name;

    @Column(name = "price", nullable = false)

    private float price;

    @Column(name = "season", nullable = false, columnDefinition = "season_enum")

    @Enumerated(EnumType.STRING)

    @Type(type = "pgsql_enum")

    private SeasonEnum season;

    )

   

}

一个服务可以有许多作业。

我试图实现的是按作业中的第一个项目对 ASC/DESC 进行排序，但目前，如果服务有 4 个作业，则在排序后使用以下方法进行排序：

localhost:8080/serviceslist?sort=jobs&sortDir=asc

它给了我4次服务，但我需要它与众不同。可分页是否有办法删除重复项并解决我的排序问题？

按名称和东西排序工作正常，只是工作部分坏了：/

编辑21.04.2019：如果我之前不太清楚，我很抱歉。理想的结果是按字母顺序对第一个作业进行排序，因为作业是按完成时间的顺序排列的。这有什么可能吗？提前致谢！

Answer 1

您必须设置不同的，并检查查询返回类型：将调用长分页计数查询Specification<com.bitweb.syda.data.entity.service.Service> spec = (root, cq, cb) -> {&nbsp; &nbsp; if(!Long.class.isAssignableFrom(cq.getResultType())) {&nbsp; &nbsp; &nbsp; &nbsp; cq.distinct(true);&nbsp; &nbsp; }&nbsp; &nbsp; //else {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;//create a query for count case if needed&nbsp;&nbsp; &nbsp; //}&nbsp; &nbsp; return null;};编辑的答案：在这种情况下，考虑到 updateTime 可能指示作业排序，我建议您执行以下操作：Specification<com.bitweb.syda.data.entity.service.Service> spec = (root, cq, cb) -> {&nbsp; &nbsp; if(!Long.class.isAssignableFrom(cq.getResultType())) {&nbsp; &nbsp; &nbsp; &nbsp; if(sort.contains("jobs")) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Join<Service, Job> jobs = root.join("jobs");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //check for asc or desc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cq.orderBy(cb.asc(jobs.get("updateTime")));&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; cq.distinct(true);&nbsp; &nbsp; }&nbsp; &nbsp; //else {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;//create a query for count case if needed&nbsp;&nbsp; &nbsp; //}&nbsp; &nbsp; return null;};干杯

Answer 2

您需要有一个唯一的订单标准。如果不确定，请添加“id”作为最低优先级的排序条件。问题是：如果未指定到记录级别，则数据库排序未定义。即：如果您指定排序但保留最后一位未定义（即，如果两行满足相同的排序条件），您将遇到即使一行中的两个相同查询也会返回相同的结果，但顺序不同。

Answer 3

你可以做这样的事情public Page<com.bitweb.syda.data.entity.service.Service> getServicesList(ServiceListRequest request, Pageable pageable) {&nbsp; &nbsp; &nbsp; &nbsp; Specification<com.bitweb.syda.data.entity.service.Service> spec = (root, query, builder) -> {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //you can do any check here if you want with the join and check all the search parameters here if you want&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //Join<Object, Object> jobs = root.join("jobs");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // also set query to get distinc values&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; query.distinct(true);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return null;&nbsp; &nbsp; &nbsp; &nbsp; };&nbsp; &nbsp; &nbsp; &nbsp; if (request.getSearch() != null) spec = spec.and(search(request.getSearch()));&nbsp; &nbsp; &nbsp; &nbsp; if (request.getName() != null) spec = spec.and(name(request.getName()));&nbsp; &nbsp; &nbsp; &nbsp; if (request.getJobs() != null) spec = spec.and(hasJobs(request.getJobs()));&nbsp; &nbsp; &nbsp; &nbsp; if (request.getNeedsPatrol() != null) spec = spec.and(needsPatrol(request.getNeedsPatrol()));&nbsp; &nbsp; &nbsp; &nbsp; return serviceRepository.findAll(spec, pageable);&nbsp; &nbsp; }