[
https://issues.apache.org/jira/browse/FINERACT-2488?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ralph Hopman reassigned FINERACT-2488:
--------------------------------------
Assignee: Ralph Hopman
> 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
> Assignee: Ralph Hopman
> Priority: Major
>
> 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)
