Duplicates when sorting using Pageable

Question

Controller:

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

Service:

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

    @Column(name = "explanation")
    private String explanation;

    @Column(name = "needs_patrol")
    private boolean needsPatrol;

    @Column(name = "image_required")
    private boolean imageRequired;

    @ManyToOne
    @JoinColumn(name = "color_id", nullable = false)
    private Color color;

    @Column(name = "start_date")
    private Date startDate;

    @Column(name = "end_date")
    private Date endDate;

    @OneToMany(mappedBy = "service")
    @JsonIgnore
    private List<ObjectService> objectServices = new ArrayList<>();

    @ManyToMany(fetch = FetchType.EAGER, targetEntity = Job.class)
    @JoinTable(name = "service_has_job",
            joinColumns = { @JoinColumn(name = "service_id", referencedColumnName = "id")},
            inverseJoinColumns = { @JoinColumn(name = "job_id", referencedColumnName = "id")}
    )
    private Set<Job> jobs = new HashSet<>();

    @Column(name = "create_time", nullable = false, updatable = false)
    @JsonIgnore
    private Date createTime = new Date();

    @Column(name = "update_time", nullable = false)
    @JsonIgnore
    private Date updateTime = new Date();

    @PreUpdate
    @PrePersist
    public void onCreateOnUpdate() {
        updateTime = new Date();
    }

    public enum SeasonEnum {
        winter, summer, all_year
    }
}

A service can have many jobs.

What I'm trying to achieve is sort ASC/DESC by the first item in jobs, but currently, if a service has lets say 4 jobs, after sorting using:

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

it gives me the service 4 times, but I need it to be distinct. Does pageable have a way to remove duplicates and fix my sorting problem?

ServiceService getServicesList function:

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

Sorting by name and stuff works fine, just the jobs part is broken :/

Edit 21.04.2019: I'm sorry if I wasn't really clear before. The ideal result would be to sort by the first job alphabetically, since jobs are in order of when theyre done. Is that possible in any way? Thanks in advance!

Job entity:

@Entity
@Data
@Table(name = "job")
public class Job {

    @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 = "explanation")
    private String explanation;

    @ManyToMany(fetch = FetchType.EAGER, targetEntity = Competence.class)
    @JoinTable(name = "job_has_competence",
            joinColumns = { @JoinColumn(name = "job_id", referencedColumnName = "id")},
            inverseJoinColumns = { @JoinColumn(name = "competence_id", referencedColumnName = "id")}
    )
    private Set<Competence> competences = new HashSet<>();

    @ManyToMany(fetch = FetchType.EAGER, targetEntity = Material.class)
    @JoinTable(name = "job_has_material",
            joinColumns = { @JoinColumn(name = "job_id", referencedColumnName = "id")},
            inverseJoinColumns = { @JoinColumn(name = "material_id", referencedColumnName = "id")}
    )
    private Set<Material> materials = new HashSet<>();

    @ManyToMany(fetch = FetchType.EAGER, targetEntity = Mechanism.class)
    @JoinTable(name = "job_has_mechanism",
            joinColumns = { @JoinColumn(name = "job_id", referencedColumnName = "id")},
            inverseJoinColumns = { @JoinColumn(name = "mechanism_id", referencedColumnName = "id")}
    )
    private Set<Mechanism> mechanisms = new HashSet<>();

    @Column(name = "create_time", nullable = false, updatable = false)
    @JsonIgnore
    private Date createTime = new Date();

    @Column(name = "update_time", nullable = false)
    @JsonIgnore
    private Date updateTime = new Date();

    @PreUpdate
    @PrePersist
    public void onCreateOnUpdate() {
        updateTime = new Date();
    }

}

Answer 1

You have to set distinct and also check query return type:

Long will be called for pagination count queries

Specification<com.bitweb.syda.data.entity.service.Service> spec = (root, cq, cb) -> {
    if(!Long.class.isAssignableFrom(cq.getResultType())) {
        cq.distinct(true);
    }
    //else {
         //create a query for count case if needed 
    //}
    return null;
};

Answer for the edit:

In that case considering updateTime could indicate jobs ordering, I'd suggest you do the following:

Specification<com.bitweb.syda.data.entity.service.Service> spec = (root, cq, cb) -> {
    if(!Long.class.isAssignableFrom(cq.getResultType())) {

        if(sort.contains("jobs")) {
            Join<Service, Job> jobs = root.join("jobs");
            //check for asc or desc
            cq.orderBy(cb.asc(jobs.get("updateTime")));
        }
        cq.distinct(true);
    }
    //else {
         //create a query for count case if needed 
    //}
    return null;
};

Cheers

Answer 2

You need to have a unique order criteria. Add 'id' as the least priority sorting criteria if unsure.

The problem is: the db sorting is undefined if not specified to the record level. I.e.: if you specify sorting but leave the last bit undefined (i.e. if two rows fulfill the same sorting criteria) you will experience that even two identical queries in a row will return the same result but in a different order.

Answer 3

You can do something like this

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 distinc 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);
    }