Consider adding a smart count generator for QueryExpressionDSL?

[chastro3]

Description:

Currently, when implementing pagination with MyBatis Dynamic SQL, returning a PageData with total count is not very elegant.
Developers often need to write a utility method to construct both the actual select query and a corresponding count query.

It would be very helpful to provide a method to generate a smart count DSL from a given QueryExpressionDSL, which would make PageData queries cleaner and more concise.

The desired behavior is similar to PageHelper’s DefaultCountSqlParser#getSmartCountSql
,
which automatically rewrites simple queries into count(*) and wraps complex queries as subqueries for counting.

Benefits:

No external parser dependencies (JSQLParser not required; unlike PageHelper).
Safe and type-checked.
Consistent syntax with existing DSL.
Performance-optimized count queries automatically.

Example usage:

QueryExpressionDSL<SelectModel> dsl = select(UserTable.id, UserTable.name)
    .from(UserTable)
    .where(UserTable.status, isEqualTo(1));

SelectStatementProvider countProvider = SqlBuilder.smartCountFrom(dsl);

long total = userMapper.count(countProvider);

This feature would allow pagination tools to automatically compute totals without duplicating query logic, improving code clarity and safety.

[jeffgbutler]

I think this is an interesting idea, and you can accomplish it with the library as it currently stands. There are two basic strategies:

1. We support the idea of a reusable where clause - you can create a standalone where clause and then reuse it in different statements - like a select statement and a count statement. This would be a good strategy for the "simple" case you describe
2. For more complex cases, you could write a function that transforms a SelectModel into a new SelectModel that wraps the first model into a count from subquery statement. This is the correct strategy for the "complex" case - but it could really work for any select statement

I've added an example of the second strategy to an existing test (that test already had an example of reusing a where clause). You can see both strategies in the test code here: https://github.com/mybatis/mybatis-dynamic-sql/blob/master/src/test/java/examples/simple/ReusableWhereTest.java

Let me know what you think.

[chastro3]

Hello @jeffgbutler,

Thank you so much for your detailed reply — it’s been really helpful!
The second example you shared works perfectly for handling complex SQL count queries.

I’d like to confirm one thing: for simple queries, is it also possible to build the count query directly from the existing SelectModel ?
If that’s the case, it would make building a general-purpose selectPage function much more elegant — users wouldn’t need to manually handle the count expression at all.

I’d like to test this approach in my project’s release version, but currently there are some differences between release and master, so I can’t fully validate my idea yet.

Thanks for your clear explanation and practical example!

[jeffgbutler]

for simple queries, is it also possible to build the count query directly from the existing SelectModel ?

Sure. You could build a utility method that used a "simple count" for simple queries, and used a "complex count from subquery" for complex queries. The trick is deciding when to use simple or complex. I think I would use the complex method for union queries, or any non-union query that had a "group by" model. Here's an example - but you might make different decisions about how to do the selection:

    static SelectModel toCount(SelectModel selectModel) {
        List<QueryExpressionModel> queryExpressionModels = selectModel.queryExpressions().toList();

        if (queryExpressionModels.size() > 1) {
            // union query
            return toCountWithSubQuery(selectModel);
        }

        QueryExpressionModel queryExpressionModel = queryExpressionModels.get(0);

        if (queryExpressionModel.groupByModel().isPresent()) {
            return toCountWithSubQuery(selectModel);
        }

        QueryExpressionModel model = QueryExpressionModel
                .withSelectList(List.of(new CountAll()))
                .withTable(queryExpressionModel.table())
                .withJoinModel(queryExpressionModel.joinModel().orElse(null))
                .withWhereModel(queryExpressionModel.whereModel().orElse(null))
                .withTableAliases(queryExpressionModel.tableAliases())
                .build();

        return SelectModel.withQueryExpressions(List.of(model))
                .withStatementConfiguration(selectModel.statementConfiguration())
                .build();
    }

[chastro3]

Sure. You could build a utility method that used a "simple count" for simple queries, and used a "complex count from subquery" for complex queries. The trick is deciding when to use simple or complex. I think I would use the complex method for union queries, or any non-union query that had a "group by" model. Here's an example - but you might make different decisions about how to do the selection:

That’s awesome! I’ll be able to replicate the desired behavior similar to PageHelper’s DefaultCountSqlParser#getSmartCountSql to implement a smartCount feature. By the way, may I ask when the next release is planned? It seems there hasn’t been an update for a while.