Get total parent-cases with at least one sub-case with a particular value

commcare

I'm trying to get a count of parent-cases that have at least one subcase with a particular value.

  • Parent case_type is "foo" and sub case case_type is "bar".
  • Each case_type "bar" has a variable "degrees"
  • I need the count of all "foo" with at least one "bar" where degrees < 100
  • I do not want to double-count a "foo" if it has multiple "bar" that meet the degrees requirement.
  • I expect a high count (2,000+) of case_type foo per device
  • There could be from 0 to 10+ case_type bar per foo
  • I'm currently attempting this in a form that edits cases case_type bar

I've attempted to do this with a casedb bar "count" inside a foo "count" but I couldn't figure out how to phrase the interior casedb parent @case_ID reference.

I wonder if this could be done with a nested repeat group, but I'm confused by the fact that the number of "bar" per "foo" is dynamic.

Before I invest significant more time in this I'm wondering if anyone has done something similar and what your solution looked like.

Best Answer

Create a repeat group with model iteration over the foo casetype. The model iteration ID query would be:

instance('casedb')/casedb/case[@case_type = "foo"][@status = 'open']/@case_id

This will create one repeat per foo case.

Inside the repeat group, you'd have a count of the number of bar cases are under the current foo case:

if(count(instance('casedb')/casedb/case[@case_type = "bar"][@status = 'open']
   [index/parent = current()/../@id][degrees < 100]) > 0, 1, 0)

This returns 1 if there are any bar cases under that foo case with degrees < 100.

Then outside of the repeat group, you can sum the above calculation over all the foo cases to get your total:

sum(/data/repeat_group/has_bar_case)

I don't know about the performance of this solution with the case load you have. You probably have to load test it.