[FIXED] SpringData Pageable Projection with LeftJoin with JPA2.1

Issue

I have a project with 2.1.4.RELEASE with spring boot data.

The project has the following relation entities:
ApplicationEntity
ApplicationTranslateEntity
LanguageEntity

Its a locale relation tables in database (ManyToMany), with extra table for located text in different languages extra column in this table (ApplicationTranslateEntity).

ApplicationEntity:

    @Getter
    @Setter
    public class ApplicationEntity {

    @Id
    private Long id;

    private String urlImage;
    private String urlStoreiOS;
    private String urlStoreAndroid;

    @OneToMany(mappedBy = "application")
    Set<ApplicationTranslationEntity> applicationTranslationEntities;

}

ApplicationTranslateEntity:

@Getter
@Setter
public class ApplicationTranslationEntity {
    @EmbeddedId
    ApplicationTranslationKey id;

    @ManyToOne
    @MapsId("application_id")
    @JoinColumn(name = "application_id")
    ApplicationEntity application;

    @ManyToOne
    @MapsId("language_id")
    @JoinColumn(name = "language_id")
    LanguageEntity language;

    @Column(length = 100)
    private String name;

    @Column(length = 1000)
    private String description;

}

Projection:

public interface ApplicationProjection {

    Long  getId();
    String getName();
    String getDescription();
    String getUrlImage();
    String getUrlStoreiOS();
    String getUrlStoreAndroid();
}

Repository with Query:

 @Query("select  a.id as id, a.urlImage as urlImage, at.name as name, at.description as description  from ApplicationEntity a left join a.applicationTranslationEntities at on at.language.key = :language")
Page<ApplicationProjection> findAllByLanguage(Pageable pageable, Language language);

Rest Controller Application:

 @GetMapping()
Page<ApplicationDto> all(Pageable pageable, @RequestHeader(value= headerAcceptEncoding, required = false) Language language){
    return applicationService.findAll(pageable,language);
}

All work fine with pagination and sort with id. But when I try to sort by name which is on ApplicationTranslationEntities, I saw that hibernate try to make sort in ApplicationEntity not in ApplicationTranslateEntity. Why did this happen?

The error is:

org.hibernate.QueryException: could not resolve property: name of: ******entity.ApplicationEntity [select a.id as id, a.urlImage as urlImage, a.urlStoreiOS as urlStoreiOS, a.urlStoreAndroid as urlStoreAndroid, at.name as name, at.description as description from ******.entity.ApplicationEntity a left join a.applicationTranslationEntities at on at.language.key = :language order by a.id asc, a.name asc]; nested exception is java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: name of: ******.entity.ApplicationEntity [select a.id as id, a.urlImage as urlImage, a.urlStoreiOS as urlStoreiOS, a.urlStoreAndroid as urlStoreAndroid, at.name as name, at.description as description from *******.entity.ApplicationEntity a left join a.applicationTranslationEntities at on at.language.key = :language order by a.id asc, a.name asc]

Solution

You are using pagination property of name while it should be (idk which one is the proper one) either applicationTranslationEntities.name (property path) or a.name (as per join path).

Answered By - Antoniossss
Answer Checked By - Marilyn (JavaFixing Volunteer)