how to use subCriteria ?

[MrYZhou]

'subCriteria 'has private access in' org. mybatis. dynamic. sql. where. AbstractWhere DSL '

problem: sql:select count(f_id) from mt462219557998729797 where radiofield = 2 and comInputField101 = '1' or textareaField102 = '1'

expect: select count(f_id) from mt462219557998729797 where radiofield = 2 and ( comInputField101 = '1' or textareaField102 = '1' )

i found some way to solve, one is that i can use where.subCriteria . because between where and "and" i want to judge if has exits condition before .but it is private access..

base way : Provide an operator to directly add left and right parentheses.

[jeffgbutler]

This test shows many ways to group subcriteria: https://github.com/mybatis/mybatis-dynamic-sql/blob/9273d5bd3460173c1ec5dd6815e0d0d0dfed69ac/src/test/java/org/mybatis/dynamic/sql/select/SelectStatementTest.java#L73-L107

[MrYZhou]

This test shows many ways to group subcriteria:

https://github.com/mybatis/mybatis-dynamic-sql/blob/9273d5bd3460173c1ec5dd6815e0d0d0dfed69ac/src/test/java/org/mybatis/dynamic/sql/select/SelectStatementTest.java#L73-L107

Thanks for you quikly reply. but my scene is very complex.

There are two stages, the first stage is the data permission stage, where users can configure multiple data permission fields. The second stage is also optional, called data filtering configuration, where there are multiple groups. Finally, it needs to be combined. But there is a priority, and those with data permissions have higher priority. All data filtering criteria must be wrapped in parentheses. So the solution I came up with is to implement it this way.

QueryExpressionDSL<SelectModel>.QueryExpressionWhereBuilder whereFilter = SqlBuilder.select().from(sqlTable).where();

Method method = whereFilter.getClass().getDeclaredMethod("buildWhereModel");
method.setAccessible(true);
WhereModel invoke = (WhereModel) method.invoke(whereFilter);
List<AndOrCriteriaGroup> groupList = invoke.subCriteria();
where.and(DerivedColumn.of("1"), SqlBuilder.isEqualTo(1), groupList);

This way, the SQL results I mentioned above can be achieved

[jeffgbutler]

It is highly unlikely you need to use reflection to accomplish your goal. The library has a lot of support for creating standalone where clauses, using function composition with where clauses, optional conditions, etc.

I'd like to help you, but your example is incomplete and won't compile so it's hard for me to understand what you are trying to accomplish. If you will post a full example of what you are trying to do, I'll take a look.

[jeffgbutler]

@MrYZhou Here are two different methods for building where clauses in multiple stages. There's not much difference in coding, it's just a matter of personal taste. Note that the code below will only work in version 1.5.0 or higher.

Both make use of the applyWhere method on a select statement that accepts an independent where clause. Note that the renderer properly handles parentheses for the conditions in stage 2.

@Test
void testTwoStep() {
    // stage 1...
    WhereDSL.StandaloneWhereFinisher whereClause = SqlBuilder.where(firstName, isEqualTo("Sue")).and(lastName, isNotNull());

    // stage 2...
    whereClause.and(occupation, isEqualTo("Welder"), or(occupation, isEqualTo("Journalist")));

    SelectStatementProvider selectStatement = select(person.allColumns())
            .from(person)
            .applyWhere(whereClause.toWhereApplier())
            .build().render(RenderingStrategies.SPRING_NAMED_PARAMETER);

    String expected = "select * from Person where first_name = :p1 and last_name is not null and (occupation = :p2 or occupation = :p3)";

    assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
}

@Test
void testTwoStepWithLambda() {
    // stage 1...
    WhereApplier whereApplier = SqlBuilder.where(firstName, isEqualTo("Sue")).and(lastName, isNotNull()).toWhereApplier();

    // stage 2...
    whereApplier = whereApplier.andThen(w -> w.and(occupation, isEqualTo("Welder"), or(occupation, isEqualTo("Journalist"))));

    SelectStatementProvider selectStatement = select(person.allColumns())
            .from(person)
            .applyWhere(whereApplier)
            .build().render(RenderingStrategies.SPRING_NAMED_PARAMETER);

    String expected = "select * from Person where first_name = :p1 and last_name is not null and (occupation = :p2 or occupation = :p3)";

    assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
}

[MrYZhou]

@jeffgbutler Thank you for your enthusiastic response, but there will be multiple conditions (determined by user configuration) in my second stage. So it seems that it cannot be directly displayed for writing. I first loop through the user configured conditions and then add them to the AndOrCriteriaGroup collection. Finally, return a AndOrCriteriaGroup collection. All of this is done. Then execute

.build().render(RenderingStrategies.SPRING_NAMED_PARAMETER);

My fundamental requirement is to have a public method to access the property subCriteria, so that I can easily obtain AndOrCriteriaGroup

EX

List<AndOrCriteriaGroup> groupList = whereClause.getSubCriteria()

so that I can have a way in the future

[jeffgbutler]

You can also do something like this:

QueryExpressionDSL<SelectModel>.QueryExpressionWhereBuilder whereBuilder = SqlBuilder.select().from(table).where();
CriteriaGroup group = SqlBuilder.group(column2, isEqualTo(2));
whereBuilder.and(group);
SelectStatementProvider selectStatement = whereBuilder.build().render(RenderingStrategies.MYBATIS3);

The point is that the library already includes many methods for constructing dynamic where clauses. I'm not going to expose internals unless you can convince me there is a compelling use case that cannot be covered by existing functions. As I've said before, if you will provide a complete example of what you are trying to do, then I will help you understand how to accomplish that with existing methods in the library.

[MrYZhou]

@jeffgbutler This is a question of whether there is a chicken or an egg first. The parameters for SqlBuilder.group are what I need. If I had this data, I wouldn't need to obtain SqlBuilder.group You are now using the results to obtain the results. Actually, it's mainly because I started using

QueryExpressionDSL<SelectModel>.QueryExpressionWhereBuilder whereFilter = SqlBuilder.select(sqlTable.allColumns()).from(sqlTable).where();

            // ruleInfos contain  field condition info
            for (int i = 0; i < ruleInfos.size(); i++) {
                  ....
                  ....
                  ....
                genUtil.solveValue(whereFilter, sqlTable);
            }

// solveValue method
public QueryExpressionDSL<SelectModel>.QueryExpressionWhereBuilder solveValue(QueryExpressionDSL<SelectModel>.QueryExpressionWhereBuilder where, SqlTable sqlTable) {}

QueryExpressionWheeBuilder as a formal parameter. Causing difficulty in changing the logic inside, Otherwise,it would be better to use AndOrCriteriaGroup as a parameter from the beginning. Mainly for compatibility, it is quite troublesome and cannot be processed without modifying the underlying layer. As you mentioned, many of them can meet the requirements, but it is necessary to modify my underlying tool classes. There is a lot of code and it is not easy to change.

I'm not sure if you've noticed, but the problem has now arisen. I can't get the AndOrCriteriaGroup collection from the conditional section of 'QueryExpressionWheeBuilder'

Thank you again for your positive response! Hope the framework is getting better and better.

[jeffgbutler]

QueryExpressionDSL.QueryExpressionWhereBuilder is an instance of AbstractBooleanExpressionDSL - that might be an easier class to pass around.

AbstractBooleanExpressionDSL has several "and" and "or" methods that add values to the embedded AndOrCriteriaGroup collection - so it's effectively the same thing as giving you direct access to the internal list.

I will also say that you might be struggling with the DSL classes and having difficulty using them the way they were designed. But using the DSL classes is optional - you can instead build the model classes directly if that fits your use case better.