Bad SQL génération with Criteria class and complex operand

[guirak]

Hello,

I am having some trouble when generating queries with the Criteria class (org.springframework.data.relational.core.query)

It's seems not possible to make a query like : (a OR b) AND c => This will be generated as a OR b AND c : it's not the same result.

I have defined the following method to generate an OR's combination :

    private <T> Criteria createCriteria(
            final Class<T> entityClass,
            final BDFilterOr filter) {

        // Critères de chaque opérande

        return filter.getChildren()
                .stream()
                .map(child -> createCriteria(entityClass, child))
                .reduce(Criteria::or)
                .orElseThrow(() -> new MmiBadRequestException("Pas d'opérande généré pour le filtre OR"));
    }
    

And for the AND combination :

    private <T> Criteria createCriteria(
            final Class<T> entityClass,
            final BDFilterAnd filter) {

        // Critères de chaque opérande

        return filter.getChildren()
                .stream()
                .map(child -> createCriteria(entityClass, child))
                .reduce(Criteria::and)
                .orElseThrow(() -> new MmiBadRequestException("Pas d'opérande généré pour le filtre AND"));
    }

Doing this, I'm expecting to have an OR combination inside a AND combination rounded with parenthesis in the generated query. It's not the case, when the OR combination is the first operand of the AND combination.

To solve the problem, I have to ensure the OR sub criteria is not placed at the first position of a AND criteria : it can't be used in a generic engine...

Is this API still maintained ?
Is there any workaround to permit the use of this API in any OR / AND combination ?

Thank you.

[mp911de]

The API intentionally follows a style that makes it seem like you would write some criteria to not interfere with AND and OR precedence. You can create groups of nested criteria. Have you seen our QueryMapperUnitTests that show various usages and combinations of Criteria?

[spring-projects-issues]

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

[guirak]

Hello, thank you and sorry for this late response.

It's ok, I found my problem thanks to the given test code.

This following criteria :

        Criteria criteria = initial
                .and(Criteria.where("name").is("FooA")
                        .or(Criteria.where("name").is("FooB"))
                        .or(Criteria.where("name").is("FooC"))) //
                .and(Criteria.where("name").is("FooD")
                );

produces :

(person.name = ?[$1] OR (person.name = ?[$2]) OR (person.name = ?[$3])) AND (person.name = ?[$4])

while the following criteria :

        Criteria criteria = Criteria.empty()
                .or(Criteria.where("name").is("FooA"))
                .or(Criteria.where("name").is("FooB"))
                .or(Criteria.where("name").is("FooC")) 
                .and(Criteria.where("name").is("FooD")
                );

produces :

(person.name = ?[$1]) OR (person.name = ?[$2]) OR (person.name = ?[$3]) AND (person.name = ?[$4])

As it is not a direct usage and it's a generic code, I was doing the second one instead thinking I have the first one

Thank you.