QueryDSL for SpringData joined entities filtering

Question

I'm trying to use QueryDSL in my Spring Data service to implement query similar to SQL

SELECT member.*
FROM member
    LEFT JOIN project on member.projectid = project.id
WHERE
    member.role = 'EXPERT' AND (
    project.hackathonid = :hackathonId OR
    member.hackathonid = :hackathonId
)

The DB structure is :

Member could be member of Project or Hackathon. Project is part of Hackathon. I'm trying to find all members of Hachaton and child Projects.

The QueryDslPredicateExecutor interface didn't work for me because Predicate produced cross join:

memberEntity.role.eq(roleSelector).andAnyOf(
    memberEntity.hackathonEntity.id.eq(hackathonId),
    memberEntity.projectEntity.hackathonEntity.id.eq(hackathonId)
)

I've tried to use JPAQuery to be able manualy manage JOIN strategy but also faced the same problem:

query.from(memberEntity)
    .leftJoin(projectEntity).on(memberEntity.projectEntity.id.eq(projectEntity.id))
    .where(
            memberEntity.role.eq(roleSelector).andAnyOf(
                memberEntity.hackathonEntity.id.eq(hackathonId),
                memberEntity.projectEntity.hackathonEntity.id.eq(hackathonId)
            )
        )

Hibernate: select memberenti0_.id as id1_10_, memberenti0_.hackathonid as hackatho4_10_, memberenti0_.userid as userid5_10_, memberenti0_.projectid as projecti6_10_, memberenti0_.publishstatus as publishs2_10_, memberenti0_.role as role3_10_ from public.member memberenti0_ left outer join public.project projectent1_ on (memberenti0_.projectid=projectent1_.id) cross join public.project projectent2_ where memberenti0_.projectid=projectent2_.id and memberenti0_.role=? and (memberenti0_.hackathonid=? or projectent2_.hackathonid=?)

What is the correct way to use QueryDSL for filtering joined entities? Or is there any good alternative technologies for query building?

Answer 1

Changing the andAnyOf part of your query to

.andAnyOf(memberEntity.hackathonEntity.id.eq(hackathonId), 
    projectEntity.hackathonEntity.id.eq(hackathonId)));

should get you only one left join and no cross joins in Hibernate.

EDIT: if this still doesn't work for you, I'd recommend editing your question to include how you mapped your entities.