Ralph Hopman created FINERACT-2488:
--------------------------------------
Summary: Fix duplicate client results in /api/v2/clients/search
when clients have multiple identifiers
Key: FINERACT-2488
URL: https://issues.apache.org/jira/browse/FINERACT-2488
Project: Apache Fineract
Issue Type: Bug
Components: Client
Reporter: Ralph Hopman
The {{/api/v2/clients/search}} endpoint returns duplicate entries when a client
has multiple identifiers matching the search criteria. When searching by
identifier document key, clients with multiple matching identifiers appear
multiple times in the result set.
h2. Root Cause
The search query in {{SearchingClientRepositoryImpl.searchByText()}} performs a
LEFT JOIN with the {{m_client_identifier}} table to enable searching by
document keys. However, the query does not enforce distinct results, so when a
client has multiple identifiers that match the search text, the JOIN produces
multiple rows for the same client.
The query includes this join:
{code:java}
Join<Client, ClientIdentifier> identity = r.join("identifiers", JoinType.LEFT);
{code}
Without {{{}query.distinct(true){}}}, each matching identifier creates a
separate result row.
h2. Impact
* Search results contain duplicate client entries
* Pagination totals ({{{}totalElements{}}}, {{{}totalPages{}}}) are incorrect
* Users see the same client multiple times in search results
* API consumers must perform client-side deduplication
h2. Solution
Add {{q.distinct(true)}} to the Specification in
{{SearchingClientRepositoryImpl}} to ensure the query returns unique clients
regardless of how many identifiers match. This will:
* Eliminate duplicate client rows in the result set
* Fix pagination counts to reflect actual unique clients
* Preserve existing search behavior for all other fields (name, external ID,
account number, mobile number)
* Apply both to the data query and the count query via the existing
{{CriteriaQueryFactory}} logic
h2. Testing
{*}File{*}:
{{integration-tests/src/test/java/org/apache/fineract/integrationtests/client/ClientSearchTest.java}}
Test case:
{{testClientSearchDoesNotDuplicateResults_WhenIdentifierHasMultipleMatches}}
* Creates a client with two identifiers containing the same search token
* Searches by that token
* Asserts result contains exactly one client entry
h2. Alternative Solutions Considered
* Use {{EXISTS}} subquery instead of {{JOIN}} - More complex query, similar
result
* Post-process results to deduplicate - Incorrect pagination totals,
inefficient
* Limit to one identifier per client in the join - Would miss valid matches
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
