Need help to translate SQL subquery

Hello everyone,

I have to migrate an SQL query to QueryDSL with hsearch. But this time, SQL query is a bit more complex than simply predicates ; it contains a subquery with exists clause.
Here the link to the SQL query with annotations :
SQL query with subqueries

My problem is how to code the joins operations between query and subqueries with hSearch ? (in green)

EDIT : on the picture “…” means ommitted (unecessary) SQL code.
thank for your help.

This looks like you could simply target the index for entity ActePrive and use a nested predicate on depot? The table acteprive1_ in your SQL query would become the root, and the table depot9_ would become index field depot.

I’m not sure I understand the problem; please show what you’ve attempted to do and explain why the results do not match your needs.

ok. I never used nested index. I understood that nested predicate should be avoided as most possible. I was thinking for splitting the whole query into 4 distincts QueryDSL :
1- Code the first query without subqueries, get all the ids (and with inverse clause : repertoire3_.hypotheque = ?)
2,3- Code the two subqueries with matchAny predicate on all the ids from 1)
4- Apply the whole query at final with 2,3 with hardcoded joins so.

But I’am not sure it is right…

That could work, but:

nested predicates should be avoided when possible because they can cause performance problems. But when you need them, you need them: they are there to be used.

What you’re planning to do with multiple queries would most likely perform even worse, so you should definitely look into nested predicates.