How to add distinct property to query with jpa specification

I am using jhipster criteria and jpa specification to implement an endpoint for making research.

Well it is working, but keep sending me duplicates.

There is prestations with this model

public class Prestation extends AbstractAuditingEntity implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
@SequenceGenerator(name = "sequenceGenerator")
private Long id;

@NotNull
@Column(name = "jhi_label", nullable = false)
private String label;

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

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

@NotNull
@Column(name = "activated", nullable = false)
private boolean activated;

@ManyToOne(optional = false)
@NotNull
@JsonIgnoreProperties("prestations")
private SubCategory subCategory;

@OneToMany(mappedBy = "prestation", cascade = CascadeType.ALL, orphanRemoval = true)
private List<CompanyPrestation> companies = new ArrayList<>();

And relation between company and prestations

@OneToMany(mappedBy = "company", cascade = CascadeType.ALL, orphanRemoval = true)
@LazyCollection(LazyCollectionOption.FALSE)
private List<CompanyPrestation> prestations = new ArrayList<>();

And here is the CompanySpecification class that i used to create specification that i give to the repository

public class CompanySpecification extends QueryService<Company> implements Specification<Company> {

private static final long serialVersionUID = 1L;
private CompanyCriteria criteria;

public CompanySpecification(CompanyCriteria criteria) {
    this.criteria = criteria;
}

@Override
public Predicate toPredicate(Root<Company> roots, CriteriaQuery<?> query, CriteriaBuilder builder) {

    Specification<Company> specification = Specification.where(null);
    if (criteria != null) {
        if (criteria.getName() != null) {
            specification = specification.or(buildStringSpecification(criteria.getName(), Company_.name));
        }
        if (criteria.getSiret() != null) {
            specification = specification.or(buildStringSpecification(criteria.getSiret(), Company_.siret));
        }
        if (criteria.getCodeAPE() != null) {
            specification = specification.or(buildStringSpecification(criteria.getCodeAPE(), Company_.codeAPE));
        }
        if (criteria.getLegalForm() != null) {
            specification = specification.or(buildStringSpecification(criteria.getLegalForm(), Company_.legalForm));
        }
        if (criteria.getVille() != null) {
            specification = specification
                    .and(buildReferringEntitySpecification(criteria.getVille(), Company_.address, Address_.ville));
        }
        if (criteria.getExercicePlace() != null && !criteria.getExercicePlace().getIn().isEmpty()) {
            Filter<ExercicePlace> exercicePlaceFilter = new Filter<>();
            exercicePlaceFilter.setIn(criteria.getExercicePlace().getIn().stream().map(ExercicePlace::valueOf)
                    .collect(Collectors.toList()));
            specification = specification.and(buildSpecification(exercicePlaceFilter, Company_.exercicePlace));
        }
        if (criteria.getCodePostal() != null) {
            specification = specification.and(buildReferringEntitySpecification(criteria.getCodePostal(),
                    Company_.address, Address_.codePostal));
        }
        if (criteria.getPrestationsId() != null) {
            specification = specification.and(buildSpecification(criteria.getPrestationsId(),
                    root -> root.join(Company_.prestations, JoinType.LEFT)
                            .join(CompanyPrestation_.prestation, JoinType.LEFT).get(Prestation_.id)));
        }
        if (criteria.getCatId() != null) {
            specification = specification.and(buildSpecification(criteria.getCatId(),
                    root -> root.join(Company_.prestations, JoinType.LEFT)
                            .join(CompanyPrestation_.prestation, JoinType.LEFT)
                            .join(Prestation_.subCategory, JoinType.LEFT).join(SubCategory_.category, JoinType.LEFT)
                            .get(Category_.id)));
        }
        if (criteria.getSubCatId() != null) {
            specification = specification.and(buildSpecification(criteria.getSubCatId(),
                    root -> root.join(Company_.prestations, JoinType.LEFT)
                            .join(CompanyPrestation_.prestation, JoinType.LEFT)
                            .join(Prestation_.subCategory, JoinType.LEFT).get(SubCategory_.id)));
        }
        if (criteria.getPrestationName() != null) {
            specification = specification.or(buildSpecification(criteria.getPrestationName(),
                    root -> root.join(Company_.prestations, JoinType.LEFT)
                            .join(CompanyPrestation_.prestation, JoinType.LEFT).get(Prestation_.label)));
        }
        if (criteria.getPriceMinimum() != null || criteria.getPriceMaximum() != null) {
            specification = specification.and((lroot, lquery, lcriteriaBuilder) -> {
                ListJoin<Company, CompanyPrestation> joinCompnayToCompanyPrestations = lroot
                        .join(Company_.prestations, JoinType.LEFT);
                if (criteria.getPriceMinimum() != null && criteria.getPriceMaximum() != null) {
                    return lcriteriaBuilder.between(
                            joinCompnayToCompanyPrestations.get(CompanyPrestation_.pricePerUnit),
                            criteria.getPriceMinimum().getGreaterOrEqualThan(),
                            criteria.getPriceMaximum().getLessOrEqualThan()
                    );
                } else if (criteria.getPriceMinimum() != null) {
                    return lcriteriaBuilder.greaterThanOrEqualTo(
                            joinCompnayToCompanyPrestations.get(CompanyPrestation_.pricePerUnit),
                            criteria.getPriceMinimum().getGreaterOrEqualThan());
                } else {
                    return lcriteriaBuilder.lessThanOrEqualTo(
                            joinCompnayToCompanyPrestations.get(CompanyPrestation_.pricePerUnit),
                            criteria.getPriceMaximum().getLessOrEqualThan());
                }
            });
        }
    }
    return specification.toPredicate(roots, query, builder);
}

And here is my service method using it

@Transactional(readOnly = true)
public Page<CompanyDTO> findByCriteria(CompanyCriteria criteria, Pageable page) {
    log.debug("find by criteria : {}, page: {}", criteria, page);
    Specification<Company> spec = Specification.where(null);
    CompanySpecification specification =  new CompanySpecification(criteria);
    spec = (Specification<Company>) specification;
    Page<Company> vitrinesPage = companyRepository.findAll(spec, page);
    List<CompanyDTO> list = companyMapper.toDto(vitrinesPage.getContent());
    Page<CompanyDTO> listPage = new PageImpl<>(list, page, vitrinesPage.getTotalElements());
    return listPage;
}

I found a method distinct in CriteriaQuery but i dont really know how to add it in my specification.

Some help please!

Inside of your toPredicate method, can you do;

@Override
public Predicate toPredicate(Root<Company> roots, CriteriaQuery<?> query, CriteriaBuilder builder) {
    ....
    query.distinct(true);
    return ...;
}

From > CriteriaQuery#distinct

Though what you are trying to do might not be possible, it is said to be a limitation of JPA

What you can try to do, is either remove duplicates within the code after fetch, or try to override equals & hashCode methods to try to hack the distinct definition maybe?