left join with spring data jpa and querydsl

Question

I am using spring data jpa and querydsl and trapped on how to write simple nice query to left join two tables. Suppose I have an Project entity and a Task entity with OneToMany relationship defined in Project, I would like to do something like:

select * from project p left join task t on p.id = t.project_id where p.id = searchTerm
select * from project p left join task t on p.id = t.project_id where t.taskname = searchTerm

In JPQL, it should be:

select distinct p from Project p left join p.tasks t where t.projectID = searthTerm
select distinct p from Project p left join p.tasks t where t.taskName = searthTerm

I have a ProjectRepository interface, which extends JpaRepository and QueryDslPredicateExecutor. That gives me access to method:

Page<T> findAll(com.mysema.query.types.Predicate predicate, Pageable pageable)

I know that left join can be easily achieved by creating a new JPAQuery(entityManager). But I do not have entity manager explicitly injected with spring data jpa. Is there nice and simple way to build a predicate with left join? Wish someone here have experienced this and is able to give me an example. Thank you.

Frey.

Could you express what you need in JPQL and not in SQL? I am now not sure if you need the left join for loading or something else. — Timo Westkämper

Timo Westkämper Timo Westkämper · Accepted Answer · 2014-04-15T19:06:16

If you want to express a constraint on tasks then you can do it like this

QProject.project.tasks.any().id.eq(searchTerm)

If you want to express preloading of certain tasks instead via a left join you can't express that via a Predicate. A Predicate in Querydsl is a boolean expression for the where, join-on and having parts of the query.

left join with spring data jpa and querydsl

1 Answers