Joining multiple tables in Criteria ruins the expected output

I am trying to make a dynamic search in multiple tables.

When I join ONLY one table I get the expected output. When I join all the tables I get only half of the result or even none. Even If I dont include the Predicates in the criteriaBuilder.or(); just declaring the JOINS breaks the code.

Entity:

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
   
   //Other fields...
  
    @ToString.Exclude
    @JsonIgnore
    @OneToMany(mappedBy = "imageInfo", fetch = FetchType.LAZY)
    private List<GenreImageInfoMEntity> genres;

    @ToString.Exclude
    @JsonIgnore
    @OneToMany(mappedBy = "imageInfo", fetch = FetchType.LAZY)
    private List<KeywordImageInfoMEntity> keywords;

    @ToString.Exclude
    @JsonIgnore
    @OneToMany(mappedBy = "imageInfo", fetch = FetchType.LAZY)
    private List<LocationImageInfoMEntity> locations;

    @ToString.Exclude
    @JsonIgnore
    @OneToMany(mappedBy = "imageInfo", fetch = FetchType.LAZY)
    private List<PersonImageInfoMEntity> persons;

Join Entity:

@Getter
@Setter
@Entity
@Table(name = "genre_image_info_mapping",schema = "public")
public class GenreImageInfoMEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    Long id;

    @ManyToOne(cascade = CascadeType.DETACH)
    @JoinColumn(name = "fk_genre_id")
    GenreEntity genre;

    @ManyToOne(cascade = CascadeType.DETACH)
    @JoinColumn(name = "fk_image_info_id")
    ImageInfoEntity imageInfo;

}

Joined Entity

@Entity
@Getter
@Setter
@Table(name = "genre",schema = "public")
public class GenreEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    Long id;

    @Column(name = "name")
    String name;
}

The other 3 Entities looks the same.

Specification

This way work correctly:

    private  Specification<ImageInfoEntity> fullSearch(String search) {
        return (root, query, criteriaBuilder) -> {

            Join<ImageInfoEntity, GenreImageInfoMEntity> joinG = root.join(ImageInfoEntity_.GENRES);
            String searchFinal = "%" + Utility.unaccent(search.toLowerCase()) + "%";

            Predicate genrePredicate = criteriaBuilder.like(criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(joinG.get(GenreImageInfoMEntity_.GENRE).get(GenreEntity_.NAME))),searchFinal);
            return criteriaBuilder.or(genrePredicate);

        };
    }

This way I get 0 results.

    private  Specification<ImageInfoEntity> fullSearch(String search) {
        return (root, query, criteriaBuilder) -> {

            Join<ImageInfoEntity, GenreImageInfoMEntity> joinG = root.join(ImageInfoEntity_.GENRES);
            Join<ImageInfoEntity, LocationImageInfoMEntity> joinL = root.join(ImageInfoEntity_.LOCATIONS);
            Join<ImageInfoEntity, PersonImageInfoMEntity> joinP = root.join(ImageInfoEntity_.PERSONS);
            Join<ImageInfoEntity, KeywordImageInfoMEntity> joinK = root.join(ImageInfoEntity_.KEYWORDS);

            String searchFinal = "%" + Utility.unaccent(search.toLowerCase()) + "%";

            Predicate genrePredicate = criteriaBuilder.like(criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(joinG.get(GenreImageInfoMEntity_.GENRE).get(GenreEntity_.NAME))),searchFinal);
            Predicate personPredicate = criteriaBuilder.like(criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(joinP.get(PersonImageInfoMEntity_.PERSON).get(PersonEntity_.SEARCH_NAME))),searchFinal);
            Predicate keywordPredicate = criteriaBuilder.like(criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(joinK.get(KeywordImageInfoMEntity_.KEYWORD).get(KeywordEntity_.KEYWORD))),searchFinal);
            Predicate locationPredicate = criteriaBuilder.like(criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(joinL.get(LocationImageInfoMEntity_.LOCATION).get(LocationEntity_.NAME))),searchFinal);
            Predicate locationHUNPredicate = criteriaBuilder.like(criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(joinL.get(LocationImageInfoMEntity_.LOCATION).get(LocationEntity_.HUN_NAME))),searchFinal);
            Predicate locationENGPredicate = criteriaBuilder.like(criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(joinL.get(LocationImageInfoMEntity_.LOCATION).get(LocationEntity_.ENG_NAME))),searchFinal);
            Predicate photographerPredicate = criteriaBuilder.like(criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(root.get(ImageInfoEntity_.PHOTOGRAPHER).get(PersonEntity_.ABC_NAME))),searchFinal);
            Predicate ownerPredicate = criteriaBuilder.like(criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(root.get(ImageInfoEntity_.OWNER))),searchFinal);
            return criteriaBuilder.or(genrePredicate,personPredicate,keywordPredicate,locationPredicate,locationENGPredicate,locationHUNPredicate,ownerPredicate,photographerPredicate);
            

        };
    }

Other specification:

    public Specification<ImageInfoEntity> build(FilterImageInfoDto filterImageInfoDto) {

        List<Specification<ImageInfoEntity>> specifications = new ArrayList<>();

        if(filterImageInfoDto.getSearch()!= null){
            specifications.add(fullSearch(filterImageInfoDto.getSearch()));
        }
        if(filterImageInfoDto.getSizeX()!= null){
            specifications.add(isSizeXEq(filterImageInfoDto.getSizeX()));
        }
        if(filterImageInfoDto.getSizeY()!= null){
            specifications.add(isSizeYEq(filterImageInfoDto.getSizeY()));
        }
        if(filterImageInfoDto.getCategory()!= null){
            specifications.add(isCategoryEq(filterImageInfoDto.getCategory().getId()));
        }
        if(filterImageInfoDto.getUploadDate()!= null){
            specifications.add(isUploadDateEq(filterImageInfoDto.getUploadDate()));
        }
        if(filterImageInfoDto.getCaptureYear()!= null){
            specifications.add(isCaptureYearEq(filterImageInfoDto.getCaptureYear()));
        }
        if(filterImageInfoDto.getTechnics()!= null  && !filterImageInfoDto.getTechnics().isEmpty() ){
            specifications.add(isTechnicsEq(filterImageInfoDto.getTechnics()));
        }
        if(filterImageInfoDto.getMaterials()!= null && !filterImageInfoDto.getMaterials().isEmpty()){
            specifications.add(isMaterialsEq(filterImageInfoDto.getMaterials()));
        }
        if(filterImageInfoDto.getGenres()!= null&& !filterImageInfoDto.getGenres().isEmpty()) {
            specifications.add(isGenreEq(filterImageInfoDto.getGenres()));
        }
        if(filterImageInfoDto.getPersons()!= null && !filterImageInfoDto.getPersons().isEmpty()){
            specifications.add(isPersonEq(filterImageInfoDto.getPersons()));
        }
        if(filterImageInfoDto.getPhotographers()!= null && !filterImageInfoDto.getPhotographers().isEmpty()){
            specifications.add(isPhotographerEq(filterImageInfoDto.getPhotographers()));
        }
        if(filterImageInfoDto.getLocations()!= null && !filterImageInfoDto.getLocations().isEmpty()){
            specifications.add(isLocationEq(filterImageInfoDto.getLocations()));
        }
        if(filterImageInfoDto.getKeywords()!= null && !filterImageInfoDto.getKeywords().isEmpty()){
            specifications.add(isKeywordLike(filterImageInfoDto.getKeywords()));
        }
        if(filterImageInfoDto.getHolders()!= null && !filterImageInfoDto.getHolders().isEmpty()){
            specifications.add(isHolderEq(filterImageInfoDto.getHolders()));
        }

        return  specifications.stream()
                .reduce(Specification::and)
                .orElse(null);
    }

What am I missing ?

I have tried breaking it into different functions but nothing helps.

Solution:

Predicate personPredicate = criteriaBuilder.like(criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(**joinP.get(PersonImageInfoMEntity_.PERSON).get(PersonEntity_.SEARCH_NAME)**)),searchFinal);

In the above code snippet:

joinP.get(PersonImageInfoMEntity_.PERSON).get(PersonEntity_.SEARCH_NAME)

Makes a JOIN. But these types of JOINs are normal JOINs, which limits the output.

So basically, by joining manually on every get, you can specify the join type.

Join<GenreImageInfoMEntity, GenreEntity> joinGGenre = joinG.join(GenreImageInfoMEntity_.GENRE, JoinType.LEFT);

Predicate genrePredicate = criteriaBuilder.like(criteriaBuilder.function("unaccent", String.class, criteriaBuilder.lower(joinGGenre.get(GenreEntity_.NAME))),searchFinal);