Migrating SQL based search query in bahmni commons for patient search to HQL.

Hi All,

As we know appointments was extracted out of bahmni so that it can be used directly on an openmrs instance, As a part of the work done here we also extracted out a bahmni commons module.

In our implementation we are are also using the datafilter module.

There is a patient search API defined in bahmni commons module for which data filter is not kicking in. this is because the search query defined in PatientDaoImpl.java is a SQL query

SQLQuery sqlQuery = new PatientSearchBuilder(sessionFactory)
                .withPatientAddress(addressFieldName, addressFieldValue, addressSearchResultFields)
                .withPatientIdentifier(identifier, filterOnAllIdentifiers)
                .withPatientAttributes(customAttribute, getPersonAttributeIds(customAttributeFields), getPersonAttributeIds(patientSearchResultFields))
                .withProgramAttributes(programAttributeFieldValue, programAttributeType)
                .withLocation(loginLocationUuid, filterPatientsByLocation)
                .buildSqlQuery(length, offset);

and for datafilter to kick in it needs to be a HQL query.

We have 2 approaches to migrate it to HQL query:

  1. Migrate the entire query to HQL. (need to rely on tests for this).
  2. Create a V2 for the API and incrementally build it to replace the old API.

We are in favour of a V2 as it will be easier and faster to implement it, For our use case we only need the with name criteria which can be used as a starting point for this API, and the idea is to add more criteria as and when required.

Please share your thoughts/suggestions.


@snehabagri @mksd @wyclif @angshuonline @binduak

cc: @rrameshbtech @mddubey


I would go with 2. Introduce a V2 and progressively replace. In your context, of course you will have to device a way to figure out what to use.

Btw, it does not need to be “/v2/patient/…” … I would even think of introducing a strategy (based on some parameter) in the service level to use an appropriate DAO method.

Mmm, there is also this: https://github.com/Bahmni/bahmni-core/blob/89fc7a5447867162f38dacf054e71a644385048c/bahmnicore-omod/src/main/java/org/bahmni/module/bahmnicore/web/v1_0/controller/SqlSearchController.java#L33

Hello Everyone, We had discussed in the last PAT call on this topic. We did an internal discussion for V2 vs SearchHandler and we have decided to go ahead with the V2 endpoint for now which would provide the HQL implementation of search by name.

CC: @mksd @angshuonline @binduak


Hi everyone, following up on this, the issue of the patient search not triggering the datafilter still remains on current bahmni commons module implementation.

We observe this issue on the bahmni appointments when searching for patients. The data filter is not kicking in, hence returning all the patients, not only the ones belonging to the current location.

To easily fix this issue, we propose, similarly to the v2 discussed above, creating an additional controller but, to be used specifically by the appointments. Its API can be lighter (search only by name) and rely on openmrs service to fetch and filter patients, hence triggering the data filter. The returned answer will maintain the same format.

We would end up with the existing endpoint as-is: /openmrs/ws/rest/v1/bahmnicommons/search/patient

And another new endpoint to be used specifically by the appointments: /openmrs/ws/rest/v1/appointments/search/patient

Additional note: Making the bahmni appointments front fetch all the patients from an already existing openmrs API (that already triggers the filtering) was also considered but, wouldn’t work without refactoring the front, as response format would be different from the currently expected.

Please share you thoughts on the proposed solution.


cc: @mksd @angshuonline @binduak

1 Like