JPQL single collection element in WHERE

Question

I'm creating a small forum. The entities and their relations are like this:

Entity Topic has a OneToMany relation to Post, Post has a ManyToOne relation to User

I want to get all topics for one specific user by checking the author of the first post in each topic. Here is my JPQL:

SELECT t FROM Topic t JOIN t.posts p WHERE p.user=:user

But this WHERE will check all posts in topic, I only want to check the first post (This user will be considered as the author of the topic).

Thanks

score 2 · Accepted Answer · edited May 23 '17 at 12:24

I found a better solution here: Indexed element access in JPQL

Basically I can use INDEX() in JPQL to describe the index of collection:

SELECT t FROM Topic t JOIN t.posts p WHERE p.user = :user AND INDEX(p) = 0

In order to make INDEX() work, I need to add a @OrderedColumn on Topic entity's posts field:

@OrderColumn(name = "order_column")
@OneToMany(cascade = CascadeType.ALL, mappedBy = "topic")
private List<Post> posts = new ArrayList<>();

The problem is, there is a bug in Hibernate, it doesn't support @OrderedColumn and mappedBy together: JPA 2.0 @OrderColumn annotation in Hibernate 3.5

As a solution I update the order column manually in entity Post:

@Column(name = "order_column")
private Integer orderColumn;

@PrePersist
@PreUpdate
private void updateOrderColumn() {
    // for changing order
    orderColumn = topic.getPosts().indexOf(this);
    // for persist
    if (orderColumn == -1)
        orderColumn = topic.getPosts().size();
}

score 1 · Answer 2 · answered Feb 19 '15 at 22:52

Just use native query, with sub select on join, so that

@Query(value = "SELECT * FROM topics t JOIN posts p ON p.topic_id = (SELECT topic_id FROM posts p1 WHERE p1.topic_id = t.id ORDER BY p1.id ASC LIMIT 1) WHERE p.user_id = :userId", nativeQuery=true)
List<Topic> findTopicsByUser(@Param("userId") Long userId);

JPQL single collection element in WHERE

2 Answers2