rymghosn opened a new pull request, #6536: URL: https://github.com/apache/fineract/pull/6536
## Description JIRA: https://issues.apache.org/jira/browse/FINERACT-2872 On PostgreSQL, `GET /v1/centers?officeId=..&staffId=..&meetingDate=..` (the lookup behind the productive collection sheet) returns HTTP 500: `column "ce.id" must appear in the GROUP BY clause or be used in an aggregate function`. `CenterReadPlatformServiceImpl.retriveAllCentersByMeetingDate` selects `ce.id`, `ce.display_name`, `g.office_id`, `g.staff_id` and `s.display_name` next to aggregated repayment amounts, but groups only by calendar, calendar instance and some child-group columns, none of which is the primary key of `m_group` or `m_staff`. MySQL/MariaDB accept it, PostgreSQL does not. The `staffId == null` branch had no `GROUP BY` at all (not reachable through the API today, since `staffId` is validated as mandatory). The missing columns are added to the `GROUP BY`, which is now shared by both branches. They are determined by the existing grouping keys, so the rows and totals are unchanged and the SQL stays valid on MySQL/MariaDB. ### Tests New `CenterIntegrationTest.testListCentersByMeetingDate`: creates an office and a staff member and lists centers by meeting date for them (through a new `NonPagedListingApi.listCentersByMeetingDate`, since the endpoint answers a bare array). The query fails at analysis time on PostgreSQL, so no center data is needed to reproduce it. Verified locally against PostgreSQL with the same request: - `develop`: HTTP 500 (`column "ce.id" must appear in the GROUP BY clause ...`), both for a new office/staff and for office 1/staff 1. - With this change: HTTP 200 `[]` for both. ## Checklist - [x] Write the commit message as per [our guidelines](https://github.com/apache/fineract/blob/develop/CONTRIBUTING.md#pull-requests) - [x] Acknowledge that we will not review PRs that are not passing the build _("green")_ - it is your responsibility to get a proposed PR to pass the build, not primarily the project's maintainers. - [x] Create/update [unit or integration tests](https://fineract.apache.org/docs/current/#_testing) for verifying the changes made. - [x] Follow our [coding conventions](https://cwiki.apache.org/confluence/display/FINERACT/Coding+Conventions). - [ ] Add required Swagger annotation and update API documentation at fineract-provider/src/main/resources/static/legacy-docs/apiLive.htm with details of any API changes (no API change) - [x] [This PR must not be a "code dump"](https://cwiki.apache.org/confluence/display/FINERACT/Pull+Request+Size+Limit). Large changes can be made in a branch, with assistance. Ask for help on the [developer mailing list](https://fineract.apache.org/#contribute). - [x] If merging this PR resolves a JIRA issue, I will mark that issue as resolved and set "Fix Version/s" appropriately. - [x] I followed the [AI Policy](https://github.com/apache/fineract/blob/develop/CONTRIBUTING.md#ai-policy). Your assigned reviewer(s) will follow our [guidelines for code reviews](https://cwiki.apache.org/confluence/display/FINERACT/Code+Review+Guide). -- 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]
