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

Tags: #<Tag:0x00007f3036cd3990> #<Tag:0x00007f3036cd3850> #<Tag:0x00007f3036cd3738>

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