Reuse joins for jurisdiction predicates [5]

[StefanKock]

Problem Description

Coming from investigation in #8637: When calling "Entity"Service.inJurisdictionOrOwned from other contexts, there should be a reusage of joins to drastically reduce LEFT JOINS for example Task -> Contact -> Case

Proposed Change

• 1. Do research where this pattern is causing too much joins. Known: Task, Sample
• 2. Instantiate the QueryJoins object on the caller side (Task -> Case) and not on the called side.
• 3. Reduce redundant getters in QueryJoins subclasses, link them to cached QueryJoins.
• 4. Change all instantiation of QueryContexts with JPA Join outside it's domain for all queries that create jurisdiction Predicates.

Out of scope: Join reuse in createUserFilter methods -> #8747

Possible Alternatives

Additional Information

In the PoC I managed to get rid of 4 Case joins.

Before/After SQL comparison

Before:

-- TaskFacadeEjbTest.testFilterTasksByUserJurisdiction -> 2b. Region user now sees tasks from district level

LEFT OUTER JOIN cases case17_ ON contact11_.caze_id=case17_.id
LEFT OUTER JOIN Person person18_ ON case17_.person_id=person18_.id
LEFT OUTER JOIN cases case19_ ON contact11_.caze_id=case19_.id  --<- to be removed
LEFT OUTER JOIN cases case20_ ON contact11_.caze_id=case20_.id  --<- to be removed
LEFT OUTER JOIN cases case21_ ON contact11_.caze_id=case21_.id  --<- to be removed
LEFT OUTER JOIN cases case22_ ON contact11_.caze_id=case22_.id  --<- to be removed
LEFT OUTER JOIN Contact contacts23_ ON case22_.id=contacts23_.caze_id

After:

LEFT OUTER JOIN cases case15_ ON contact10_.caze_id=case15_.id
LEFT OUTER JOIN Person person16_ ON case15_.person_id=person16_.id
LEFT OUTER JOIN Contact contacts17_ ON case15_.id=contacts17_.caze_id

When I look at the needed changes, I get the impression that a massive overhaul is needed:

1. Every creation of "Entity"QueryContext needs to be checked and all or most to be edited.
2. Every "Entity"Joins object needs to be checked and delegation to lazily created Sub-"Entity"Joins are needed (removes a lot of duplicated code as seen in TaskJoins).
3. The "Entity"Service.createUserFilter mostly needs to be called with a "Entity"Joins object instead of JPA Join as root object.

Helpful code snippets

To adapt the QueryContext subclasses:

// temporarly reduce to "protected", but needs to be changed back to "public" to not break createUserFilter (#8747)
	protected CaseQueryContext(CriteriaBuilder cb, CriteriaQuery<?> query, From<?, Case> root) {
		this(cb, query, new CaseJoins(root));
	}

	public CaseQueryContext(CriteriaBuilder cb, CriteriaQuery<?> query, CaseJoins joins) {
		super(cb, query, joins.getRoot(), joins);
	}

To lazily create cascaded QueryJoins:

	protected <T, A extends AbstractDomainObject, J extends QueryJoins<A>> J getOrCreate(J joins, Supplier<J> joinsSupplier, Consumer<J> setValue) {

		final J result;
		if (joins == null) {
			result = joinsSupplier.get();
			setValue.accept(result);
		} else {
			result = joins;
		}

		return result;
	}

[StefanKock]

Waiting for #8748 to be merged.

[StefanKock]

@BarnaBartha I started the work for this ticket on branch https://github.com/hzi-braunschweig/SORMAS-Project/tree/feature-8688_reuse_joins_for_jurisdiction_checks for you to pick it up.

[BarnaBartha]

Evaluation of performance improvement using national user:

Samples directory (546285 active entries / 819468 in total):

Tasks directory (30127 active entries / 92367 in total):