Spring Boot 2 - JPA + Pagable problem on ManyToMany tables

In Spring Boot 2 JPA, I have the following two many to many Entities.

1- Labor:

@Entity
public class Labor {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    @Column(length = 100, nullable = false)
    private String name;
    @Column(length = 50)
    private String mobile;
    private Date dateOfBirth;
    private boolean male;
    private boolean active;
    private String brife;
    @Column(length = 500)
    private String specifcation;
    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "labor_tag",
            joinColumns = @JoinColumn(name = "labor_id"),
            inverseJoinColumns = @JoinColumn(name = "tag_id"))
    private Set<Tag> tags = new HashSet<>();
}

and Tag table:

@Entity
public class Tag {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    @Column(length = 100, unique = true)
    private String name;
    private boolean active = true;
    @ManyToMany(cascade = CascadeType.ALL, mappedBy = "tags")
    @JsonIgnore
    private Set<Labor> labors = new HashSet<>();
}

Then I defined Labor Repository to query Labors with certain Tag ID ,gender, or ages

@Repository
public interface LaborReoistory extends JpaRepository<Labor, Long> {

    @Query("select l from Labor l join l.tags t where (:tid is null or t.id in :tid) and " +
            "(:isMale is null or :isMale = TRUE) and " +
            "((:startYear is null or :endYear is null or :startYear >  :endYear) or year(l.dateOfBirth) >= :startYear and year(l.dateOfBirth) <= :endYear)")
    Page<Labor> findLaborsByCondition(@Param("tid") final long[] tid,
                                      @Param("isMale") final Boolean isMale,
                                      @Param("startYear") final Integer startYear,
                                      @Param("endYear") final Integer endYear,
                                      final Pageable pageable);

}

When I use this repository in my controller, I find the totalElements property of the Pagable returned counts to records in labor_tag(in this case 16 records),but what I actually want is to have totalElements count on Labors with given conditions. does JPA Pagable support such query or how can I find a workaround?

Thanks

Solution

After joining there will be duplicate Labor but totalElements is the count of total number of row using the query. So you should use Distinct on Labour to get the count of distinct Labour

@Query("select distinct l from Labor l join l.tags t where (:tid is null or t.id in :tid) and " +
            "(:isMale is null or :isMale = TRUE) and " +
            "((:startYear is null or :endYear is null or :startYear >  :endYear) or year(l.dateOfBirth) >= :startYear and year(l.dateOfBirth) <= :endYear)")