Yashgoswami-ds opened a new pull request, #6146:
URL: https://github.com/apache/fineract/pull/6146
## Description
This PR fixes an issue in the audit pagination count query where the API
returned an incorrect `totalFilteredRecords` value even when audit records were
present.
## Issue
When calling the audit API with pagination enabled:
```http
GET /fineract-provider/api/v1/audits?paged=true&limit=5&offset=0
```
The API returned:
```json
{
"totalFilteredRecords": 0,
"pageItems": []
}
```
despite audit records being available in the `m_portfolio_command_source`
table.
## Root Cause
The issue was identified in `DatabaseSpecificSQLGenerator`, which is
responsible for generating the count query used for paginated responses.
The existing implementation attempted to remove `LIMIT` and `OFFSET` clauses
using case-sensitive regular expressions:
```java
sql.replaceAll("LIMIT \\d+", "")
.replaceAll("OFFSET \\d+", "")
```
However, the SQL generated by the pagination flow used lowercase keywords:
```sql
ORDER BY aud.id DESC limit 5 offset 0
```
Since the regular expressions were case-sensitive, the `LIMIT` and `OFFSET`
clauses were not removed from the SQL.
As a result, the generated count query still contained the pagination
clauses:
```sql
SELECT COUNT(*) FROM (
...
ORDER BY aud.id DESC limit 5 offset 0
) AS temp;
```
This caused the count query to operate on the paginated result set instead
of the complete result set, leading to an incorrect `totalFilteredRecords`
value.
## Solution
Updated the regular expressions to remove `LIMIT` and `OFFSET` in a
case-insensitive manner:
```java
sql = sql.replaceAll("(?i)LIMIT\\s+\\d+", "")
.replaceAll("(?i)OFFSET\\s+\\d+", "")
.trim();
```
Using the `(?i)` flag ensures that SQL keywords are matched regardless of
their case (`LIMIT`, `limit`, `Limit`, etc.), allowing the count query to be
generated correctly.
## Verification
The issue was reproduced and verified locally using Docker with PostgreSQL.
### Before the fix
```json
{
"totalFilteredRecords": 0,
"pageItems": []
}
```
### After the fix
```json
{
"totalFilteredRecords": 3,
"pageItems": [
{
"id": 3,
"actionName": "CREATE",
"entityName": "CLIENT"
}
]
}
```
After applying the fix, the audit pagination endpoint correctly returns both
the total number of matching audit records and the expected paginated results.
## Testing Environment
- Apache Fineract `develop` branch
- Docker environment
- PostgreSQL database
- Audit pagination API
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
