Spring Boot 2 - JPA + Pagable problem on ManyToMany tables

Question

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

@User-Upvotedon'tsayThanks no, when I ran this query without Paging it returned actual labor set with multiple tags and actual labor number in the table — Korben, Aug 10 '20 at 06:11
It seems there should be duplicate labor then you should use `select distinct l ...` I think — Eklavya, Aug 10 '20 at 06:17
I mean if `totalElements` is return 16 then must be you query will return 16 row after joining thats why therer is possibility of duplicate Labor for joining — Eklavya, Aug 10 '20 at 06:35
@User-Upvotedon'tsayThanks I tried distinct as you suggested. it worked. thanks. please provide solution to this question so it could be saved as solution. thank you very much — Korben, Aug 10 '20 at 06:37

score 0 · Accepted Answer · answered Aug 10 '20 at 06:46

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)")

Spring Boot 2 - JPA + Pagable problem on ManyToMany tables

1 Answers1