JPQL for a Unidirectional OneToMany

Question

I need a jpql query for my Spring repository interface method, to retrieve all Posts for a given Semester.

@LazyCollection(LazyCollectionOption.FALSE)
@OneToMany(cascade = CascadeType.MERGE)
@JoinTable
(
 name = "semester_post",
 joinColumns = {@JoinColumn(name = "semester_id", referencedColumnName = "id")},
 inverseJoinColumns = {@JoinColumn(name = "post_id", referencedColumnName = "id", unique = true)}
)
private List<PostEntity<?>> posts = new ArrayList<>();

PostEntity doesn't have a reference to Semester, and I do not want to add one, because I plan to use this PostEntity for other things than Semester. Maybe I'll have another class (let's say Group) which will also have a OneToMany of PostEntity (like the one in Semester)

So, how do I write this SQL query as a JPQL one ?

select * from posts join semester_post on semester_post.post_id = posts.id where semester_post.semester_id = 1;

My repository

public interface PostRepository extends JpaRepository<PostEntity, Long> {

String QUERY = "SELECT p FROM PostEntity p ... where semester = :semesterId";

@Query(MY_QUERY)
public List<PostEntity> findBySemesterOrderByModifiedDateDesc(@Param("semesterId") Long semesterId);

Answer 1

A query which will get you the result that you need is:

SELECT p FROM SemesterEntity s JOIN s.posts p WHERE s.id = :semesterId

This query uses the JOIN operator to join the SemesterEntity to the PostEntity across the posts relationship. By joining the two entities together, this query returns all of the PostEntity instances associated with the relevant SemesterEntity.