[SOLVED] Combining AND and OR (e.g. with round brackets) in Mybatis Dynamic SQL

Combining AND and OR (e.g. with round brackets) in Mybatis Dynamic SQL

I need to build a complex query, in part from a "filter framework". A filter framework is a reflective component that adds up as many and filters as possible automagically.

I have successfully created the filter framework that generates and filters based on user input via MVC. The following is a typical query

SELECT * from Entity
WHERE
    attribute1 = ?
AND attribute2 > ?
AND attribute3 IN (????);

In Mybatis Dynamic SQL, you chain up calls to AbstractWhereDSL.and and you are done.

Given that I have a reference to an automatically-generated WhereApplier, which is a Consumer<AbstractWhereDSL<?>>, I need to chain up another condition that is a complex condition. The resulting query MUST be

SELECT * from Entity
WHERE
    attribute1 = ?
AND attribute2 > ?
AND attribute3 IN (????)
AND (
        (attribute4 = ? AND attribute5 IN (???))
    OR  (attribute4 = ? AND attribute5 IN (???))
    OR  (attribute4 = ? AND attribute5 IN (???))
)
;

AbstractWhereDSL accepts only BindableColumn and ExistsPredicate.

If I do something like

ret = ret.applyWhere(where -> where
    .and(attribute4,isEqualTo(x)).and(attribute5,isIn(y))
    .or(attribute4,isEqualTo(z)).and(attribute5,isIn(a))
);

I will end up into a flat query

SELECT * from Entity
WHERE
    attribute1 = ?
AND attribute2 > ?
AND attribute3 IN (????)

AND attribute4 = ?
AND attribute5 IN (???)
OR  attribute4 = ?
AND attribute5 IN (???)
OR  attribute4 = ?
AND attribute5 IN (???)
;

Which is bad in my scenario.

Question: how can I create a sub-predicate in a where condition in Mybtis Dynamic SQL?

Context: while I have discussed this multiple times with the team, I need to segregate queries according to attribute-based visibility. The attributes are two and must be in combination. I have already optimized the query to group all values of attribute5 (legal entity ID) that come along with the same attribute4 (workflow type) in the permission set, otherwise the final query becomes an endless list of (attribute4 = ? and attribute5 = ?)

In this context, it would be extremely simple to do attribute4 in (?) and attribute5 in (?) but some results would be acceptable (you can see legal entity ACME in workflow PERFORMANCE, but not in COMPLIANCE, to provide an example)

Solution