[FIXED] ERROR: syntax error at or near "." - JPA Pageable

Issue

repository:

@Repository
public interface PostRepository extends PagingAndSortingRepository<Post, Long> {

    @Query(value = "SELECT p.postComments FROM Post p WHERE p.webId = ?1")
    Page<PostComment> findCommentsByWebId(String webid, Pageable pageable);

}

Post entity:

public class Post {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "web_id")
    private String webId;

    @Column(nullable = false, name = "title")
    private String title;

    @Column(nullable = false, name = "description")
    private String description;

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

    @OneToOne(mappedBy = "post")
    private Cover cover;

    @ManyToOne
    @JoinColumn(name = "user_id")
    private User user;

    @OneToMany(mappedBy = "post")
    private List<PostView> postViews;

    @ManyToMany
    @JoinTable(name = "post_tag",
            joinColumns = @JoinColumn(name = "post_id"),
            inverseJoinColumns = @JoinColumn(name = "tag_id"))
    private List<Tag> tags;

    @OneToMany(mappedBy = "post")
    private List<PostDownvote> postDownvotes;

    @OneToMany(mappedBy = "post")
    private List<PostUpvote> postUpvotes;

    @OneToMany(mappedBy = "post")
    private List<PostComment> postComments;

    @Column(name = "created_at")
    private Timestamp createdAt;

    @Column(name = "updated_at")
    private Timestamp updatedAt;
}

The problem: When returning plain List<PostComment> from the query method everything works fine. But if I change it to Page<PostComment> (I need total elements count), I get the following error:

2022-08-03 22:29:41.399 ERROR 9192 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: syntax error at or near "."
  Position: 14
Hibernate: select tags0_.post_id as post_id1_6_0_, tags0_.tag_id as tag_id2_6_0_, tag1_.id as id1_10_1_, tag1_.name as name2_10_1_ from post_tag tags0_ inner join tag tag1_ on tags0_.tag_id=tag1_.id where tags0_.post_id=?
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

It is very difficult to debug this. Does anyone have any clue on what is wrong?

I need BOTH paging and total amount of elements.

Solution

Basically you are not able to fetch the part of the inner collection. But you could reach it from the another side of the bi-directional relationship

@Repository
public interface PostCommentRepository extends PagingAndSortingRepository<PostComment, Long> {

     @Query(value = "SELECT pc FROM PostComment pc WHERE pc.post.webId = ?1")
     Page<PostComment> findCommentsByWebId(String webid, Pageable pageable);

     // or better using Spring Data naming conventions just
     Page<PostComment> findAllByPostWebId(String webid, Pageable pageable);
}

Answered By - Andriy Slobodyanyk
Answer Checked By - Marie Seifert (JavaFixing Admin)