Need help to translate SQL subquery

Hibernate Search
1

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.

2

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.

3

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…

4

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.

5

Hi,

Do I have to index depot.depot_initial as an @IndexEmbedded with NESTED structure for this SQL query ? :

SELECT DISTINCT depot1_.id
		FROM depot depot1_
WHERE depot1_.id NOT IN (
					SELECT DISTINCT depot16_.id
					FROM depot depot16_
					LEFT OUTER JOIN depot depotscomp17_ ON depot16_.id = depotscomp17_.depot_initial_fk
					WHERE depot16_.etat_depot = 'RETOUR_MISE_EN_INSTANCE_REJET'
						AND (depotscomp17_.depot_initial_fk IS NOT NULL)
						AND depotscomp17_.regularise = 1
						AND (
							depotscomp17_.code_workflow = 'E6_A_ENVOYE_PLANETE'
							OR depotscomp17_.code_workflow = 'E6_B_DEPOSE_PAPIER'
							)
					)

I did not, but I fear that I’am wrong…Thx

6

As soon as you have multiple conditions that should apply to the same multi-valued embedded object (multi-valued joined row), then you need nested structure for Hibernate Search to process that correctly.

Assuming a “depot” can be “depot initial” for multiple other depots, that seems to be your case, so yes, you need to use @IndexedEmbedded(structure = NESTED).

See also Hibernate Search 6.1.5.Final: Reference Documentation