How do I format query request when querying for observations in Cohort Builder's Concept/Observation

joelakwes · October 26, 2017, 10:42am

Good day Everyone.

I’m currently working on the new Cohort Builder but I’ve hit a blocker. I noticed that the result returned when querying for observations using numeric input on “Weight (Kg)” concept in the Concept/Observation tab is incorrect. From the codebase, the query request is sent to the reportingrest/adhocquery endpoint.

The current query object is shown below:

{
  "type": "org.openmrs.module.reporting.dataset.definition.PatientDataSetDefinition",
  "columns": [
    {
      "name": "firstname",
      "key": "reporting.library.patientDataDefinition.builtIn.preferredName.givenName",
      "type": "org.openmrs.module.reporting.data.patient.definition.PatientDataDefinition"
    },
    {
      "name": "lastname",
      "key": "reporting.library.patientDataDefinition.builtIn.preferredName.familyName",
      "type": "org.openmrs.module.reporting.data.patient.definition.PatientDataDefinition"
    },
    {
      "name": "gender",
      "key": "reporting.library.patientDataDefinition.builtIn.gender",
      "type": "org.openmrs.module.reporting.data.patient.definition.PatientDataDefinition"
    },
    {
      "name": "age",
      "key": "reporting.library.patientDataDefinition.builtIn.ageOnDate.fullYears",
      "type": "org.openmrs.module.reporting.data.patient.definition.PatientDataDefinition"
    },
    {
      "name": "patientId",
      "key": "reporting.library.patientDataDefinition.builtIn.patientId",
      "type": "org.openmrs.module.reporting.data.patient.definition.PatientDataDefinition"
    }
  ],
  "rowFilters": [
    {
      "key": "reporting.library.cohortDefinition.builtIn.numericObsSearchAdvanced",
      "parameterValues": {
        "timeModifier": "ANY",
        "question": "7fae062d-a5a4-4d02-885c-ddd60adde01f",
        "operator": "LESS_THAN",
        "value": "20"
      },
      "type": "org.openmrs.module.reporting.dataset.definition.PatientDataSetDefinition"
    }
  ],
  "customRowFilterCombination": "1"
}

And here’s the response object:

{
  "uuid": "b538efe4-0af8-423e-b77d-da2cee86a618",
  "metadata": {
    "columns": [
      {
        "datatype": "java.lang.Object",
        "name": "firstname",
        "label": "firstname"
      },
      {
        "datatype": "java.lang.Object",
        "name": "lastname",
        "label": "lastname"
      },
      {
        "datatype": "java.lang.Object",
        "name": "gender",
        "label": "gender"
      },
      {
        "datatype": "java.lang.Object",
        "name": "age",
        "label": "age"
      },
      {
        "datatype": "java.lang.Object",
        "name": "patientId",
        "label": "patientId"
      }
    ]
  },
  "rows": [
    {
      "firstname": "Brian",
      "gender": "M",
      "patientId": 103,
      "age": 39,
      "lastname": "Rodríguez"
    }
  ],
  "definition": {
    "class": "org.openmrs.module.reporting.dataset.definition.PatientDataSetDefinition",
    "uuid": "b538efe4-0af8-423e-b77d-da2cee86a618",
    "name": null,
    "description": null,
    "parameters": [],
    "links": [
      {
        "rel": "self",
        "uri": "http://localhost:8081/openmrs-standalonehttp://localhost:8081/openmrs-standalone/ws/reporting/v1/reportingrest/dataSetDefinition/b538efe4-0af8-423e-b77d-da2cee86a618"
      },
      {
        "rel": "full",
        "uri": "http://localhost:8081/openmrs-standalonehttp://localhost:8081/openmrs-standalone/ws/reporting/v1/reportingrest/dataSetDefinition/b538efe4-0af8-423e-b77d-da2cee86a618?v=full"
      }
    ],
    "resourceVersion": "1.8"
  },
  "links": [
    {
      "rel": "self",
      "uri": "http://localhost:8081/openmrs-standalonehttp://localhost:8081/openmrs-standalone/ws/rest/v1/reportingrest/dataSet/b538efe4-0af8-423e-b77d-da2cee86a618"
    }
  ],
  "resourceVersion": "1.8",
  "searchDescription": "Patients with observations whose question is Weight (kg) .",
  "query": {
    "type": "org.openmrs.module.reporting.dataset.definition.PatientDataSetDefinition",
    "columns": [
      {
        "name": "firstname",
        "key": "reporting.library.patientDataDefinition.builtIn.preferredName.givenName",
        "type": "org.openmrs.module.reporting.data.patient.definition.PatientDataDefinition"
      },
      {
        "name": "lastname",
        "key": "reporting.library.patientDataDefinition.builtIn.preferredName.familyName",
        "type": "org.openmrs.module.reporting.data.patient.definition.PatientDataDefinition"
      },
      {
        "name": "gender",
        "key": "reporting.library.patientDataDefinition.builtIn.gender",
        "type": "org.openmrs.module.reporting.data.patient.definition.PatientDataDefinition"
      },
      {
        "name": "age",
        "key": "reporting.library.patientDataDefinition.builtIn.ageOnDate.fullYears",
        "type": "org.openmrs.module.reporting.data.patient.definition.PatientDataDefinition"
      },
      {
        "name": "patientId",
        "key": "reporting.library.patientDataDefinition.builtIn.patientId",
        "type": "org.openmrs.module.reporting.data.patient.definition.PatientDataDefinition"
      }
    ],
    "rowFilters": [
      {
        "key": "reporting.library.cohortDefinition.builtIn.numericObsSearchAdvanced",
        "parameterValues": {
          "timeModifier": "ANY",
          "question": "7fae062d-a5a4-4d02-885c-ddd60adde01f",
          "operator": "LESS_THAN",
          "value": "20"
        },
        "type": "org.openmrs.module.reporting.dataset.definition.PatientDataSetDefinition"
      }
    ],
    "customRowFilterCombination": "1"
  }
}

To reproduce this issue, go to the new Cohort Builder and on the Concept/Obersvation tab, type “weight” and select “Weight (kg)” on the list of options. Select any of the options and enter any numeric value in the values input field. Repeat for different numeric values. You’ll notice that the numeric values entered have no effect on the results returned.

I’m assuming this is happening because the request sent to the endpoint is not structured the way it is supposed to.

I have two questions

Is reportingrest/adhocquery the correct endpoint to use in this case?
How is the query request supposed to be structured?

mseaton · October 26, 2017, 12:39pm

@joelakwes, thanks for posting this with these new details. I do believe you have found a bug. It appears to me that the definition libraries that were added to the reporting module to support Numeric and Date Obs Queries were written with invalid parameter names for the values, and this would have the effect you are experiencing. I will make the necessary fix to the reporting module shortly, and post back here with an update.

Mike

joelakwes · October 26, 2017, 1:51pm

Thank’s Mike.

I’m awaiting your update

mseaton · October 26, 2017, 2:29pm

@joelakwes, I have made a fix to reporting that I hope will help address this. See this ticket:

On the Cohort Builder end, you’ll need to update the query to use “operator1” and “value1” for the lower numeric bound, and “operator2” and “value2” for the upper numeric bound. Note, you will also need to make this change for a Date Observation query.

Are you in a position to make these changes and test it out and report back as to whether that fixes things?

Thanks, Mike

joelakwes · October 27, 2017, 5:47pm

Good day, Mike,

Sorry for the late response. There’s a part of your response I do not fully understand. Please, can you explain the lower numeric and upper numeric bound as stated in your post?

I changed my query to use “operator1” and “value1” and tested it on the modules-refapp but that didn’t fix the problem.

The fix you made, is it on the Modules-refapp already?

Here is what my new query looks like:

{
  "type": "org.openmrs.module.reporting.dataset.definition.PatientDataSetDefinition",
  "columns": [
    {
      "name": "firstname",
      "key": "reporting.library.patientDataDefinition.builtIn.preferredName.givenName",
      "type": "org.openmrs.module.reporting.data.patient.definition.PatientDataDefinition"
    },
    {
      "name": "lastname",
      "key": "reporting.library.patientDataDefinition.builtIn.preferredName.familyName",
      "type": "org.openmrs.module.reporting.data.patient.definition.PatientDataDefinition"
    },
    {
      "name": "gender",
      "key": "reporting.library.patientDataDefinition.builtIn.gender",
      "type": "org.openmrs.module.reporting.data.patient.definition.PatientDataDefinition"
    },
    {
      "name": "age",
      "key": "reporting.library.patientDataDefinition.builtIn.ageOnDate.fullYears",
      "type": "org.openmrs.module.reporting.data.patient.definition.PatientDataDefinition"
    },
    {
      "name": "patientId",
      "key": "reporting.library.patientDataDefinition.builtIn.patientId",
      "type": "org.openmrs.module.reporting.data.patient.definition.PatientDataDefinition"
    }
  ],
  "rowFilters": [
    {
      "key": "reporting.library.cohortDefinition.builtIn.numericObsSearchAdvanced",
      "parameterValues": {
        "timeModifier": "ANY",
        "question": "7fae062d-a5a4-4d02-885c-ddd60adde01f",
        "operator1": "LESS_THAN",
        "value1": "20"
      },
      "type": "org.openmrs.module.reporting.dataset.definition.PatientDataSetDefinition"
    }
  ],
  "customRowFilterCombination": "1"
}

mseaton · October 27, 2017, 6:26pm

@joelakwes, from looking at the running modules on that server, it appears that reporting is still running the older version (1.14.0-SNAPSHOT). As @wyclif showed me, this server is updated manually on-demand, per this page (see IRC history for discussion I just had with Wyclif on this).

You could request (perhaps of @dkayiwa), that this server be updated. You could also try pointing instead to qa-refapp.openmrs.org, which (according to the information in that linked page) is updated on every successful green build, and is running the latest reporting module snapshot (1.15.0-SNAPSHOT).

Mike

wyclif · October 27, 2017, 10:12pm

Why don’t we have https://modules-refapp.openmrs.org getting auto updated? But at a lower frequency rather requiring a manual build, may be once a day.

joelakwes · November 1, 2017, 1:49pm

Thanks, Mike,

I’ve been able to update reporting on refapp to 1.15.0-SNAPSHOT and my query is returning the right results now

mseaton · November 1, 2017, 2:32pm

@joelakwes, great news. Thanks for reporting this issue and helping to work through it together!

joelakwes · November 1, 2017, 4:53pm

Good Day, Mike,

I just noticed something.“Thomas Allen (75 year old Male)” is not part of the results returned when a request is sent to the endpoint but is part of the results in the old Cohort Builder. Do you have any idea why this happens?

mseaton · November 1, 2017, 6:19pm

@joelakwes, what query are you running? At least on modules-ref, if I search for “Weight (kg)”, ANY, <, 20, then I get 156 results. If I down this list, and search for “Thomas”, I do see a row for “Thomas Allen, 75, M”

Can you describe in more detail what exactly you are doing and what you expect?

Thanks, Mike

joelakwes · November 1, 2017, 9:59pm

@mseaton on the new Cohort Builder, If I search for “Weight (kg)”, LAST (Most Recent), >, 200, since 01/01/2017 until 01/11/2017, I get 8 results. Performing the same search on the old Cohort Builder produces the same result. A search for “Weight (kg)”, ANY, >, 200, since 01/01/2017 until 01/11/2017 produces 11 results for both the old and new Cohort Builder. But, a search for “Weight (kg)”, ANY, <, 200, since 01/11/2016 until 01/11/2017 produces 152 results for the new Cohort Builder and 153 results for the old Cohort Builder. I’ve carefully gone through the results and discovered that “Thomas Allen (75 year old Male)” is the missing one in the result from the new Cohort Builder.

I believe both old and new cohort builder should return the same results for the same search parameters. I also discovered that the old cohort builder is not using the reportingrest/adhocquery endpoint as I can’t find it in my browser’s network monitor. Could this be why this is happening?

mseaton · November 2, 2017, 12:20am

@joelakwes, I just ran that same query using the new cohort builder on modules-refapp. I also get 152 results. But one of these results is Thomas Allen, 75 year old male. So the discrepancy must be elsewhere. You are definitely correct that the old cohort builder does not use the reportingrest module under the hood - that was one of the reasons why we decided to redo it. So it is definitely possible for results to differ between the two, and for one or the other to have a bug. Can you please double check the patients in each query and see if you can identify any other patients that might be the issue?