- Notifications
You must be signed in to change notification settings - Fork136
Open
Description
Reason
I would like to request support for usingfetchPage() withGROUP BY aggregation queries.
In many real-world scenarios, we need to perform aggregation queries with pagination, such as:
- Statistical reports grouped by dimensions (e.g., sales by region, enrollments by university)
- Leaderboards and rankings
- Dashboard analytics with large datasets
- Any aggregated data that needs to be displayed in paginated tables
Currently, Jimmer doesn't supportfetchPage() on queries withgroupBy(), which forces us to either:
- Fetch all aggregated results and paginate in memory (inefficient for large datasets)
- Manually write separate COUNT and data queries (loses the convenience of Jimmer's API)
Description
Example Use Case
Scenario: Display a paginated list of universities and their enrollment counts for an activity.
publicPage<ActivityUniversityEnrollDTO>queryUniversityEnrollNum(longactivityId,@NullableStringuniversityName,Pageablepageable) {UserActivityActionTableuaa =Tables.USER_ACTIVITY_ACTION_TABLE;UserTableuser =Tables.USER_TABLE;varuserIds =jSqlClient .createSubQuery(uaa) .where(uaa.activityId().eq(activityId)) .where(uaa.actionType().eq(UserActivityActionTypeEnum.SUBMIT_FORM)) .select(uaa.userId());// Would like this to work ✨returnjSqlClient .createQuery(user) .where(user.id().in(userIds)) .where(user.university().isNotNull()) .whereIf(universityName !=null && !universityName.trim().isEmpty(), () ->user.university().like("%" +universityName +"%")) .groupBy(user.university()) .orderBy(SpringOrders.toOrders(user,pageable.getSort())) .select(ActivityUniversityEnrollDTOMapper.university(user.university()) .enrolledCount(user.count())) .fetchPage(pageable.getPageNumber(),pageable.getPageSize());}
Expected SQL Generation
WhenfetchPage() is called on agroupBy() query, Jimmer could generate:
COUNT Query:
SELECTCOUNT(*)FROM (SELECTu.universityFROM user uWHEREu.idIN (...)ANDu.universityIS NOT NULLGROUP BYu.university)AS grouped_results
Data Query:
SELECTu.university,COUNT(*)as enrolled_countFROM user uWHEREu.idIN (...)ANDu.universityIS NOT NULLGROUP BYu.universityORDER BY enrolled_countDESCLIMIT10 OFFSET0
Current Limitation
Currently, callingfetchPage() on agroupBy() query throws:
IllegalStateException: The current query uses group by clause, it cannot be reselectedExisting solutions
No response