XPath: how to check grand parent of Case in Lookup Table

I have a lookup table in my form where I am trying to list cases where the current case is either parent or the grandparent of the listed cases.

The following is working to list cases where the current case is the parent:

instance('casedb')/casedb/case[index/parent = instance('commcaresession')/session/data/case_id]

But when I try to expand the expression like below to show all the grand children of the case I get an error

instance('casedb')/casedb/case[index/parent = instance('commcaresession')/session/data/case_id or index/parent/index/parent = instance('commcaresession')/session/data/case_id]

This is the error I am getting

Logic references index/parent/index/parent which is not a valid question or value. You may have forgotten to include the full path to the question (e.g. /data/index/parent/index/parent). (Expanded reference: instance(casedb)/casedb/case[1]/index/parent/index/

I think this forum post should help in setting up an expression to iterate over grandchildren.

In your case, if you are inside of a form already you'll want to swap any references to




to reference the ID of the grandparent case.

Hi Clayton,

This has worked I am now able to iterate over grandchildren using the following expression:

instance('casedb')/casedb/case[selected(join(' ', instance('casedb')/casedb/case[index/parent = instance('commcaresession')/session/data/case_id]/@case_id), index/parent)]

When I want to expand the expression now to include grandchildren or children using the statement below the performance of the app goes down dramatically. It actually only works in web app. It then crashes when I am on mobile.

This is how I've expanded it to include grandchildren or children

instance('casedb')/casedb/case[selected(join(' ', instance('casedb')/casedb/case[index/parent = instance('commcaresession')/session/data/case_id]/@case_id), index/parent) or index/parent = instance('commcaresession')/session/data/case_id]

Is there anyway this can be optimized?

Hm, that outcome makes sense. The engine is able to heavily optimize some query patterns, but as soon as the structure deviates it has to go back to a full scan, which is a tremendous difference in runtime.

Specifically, I believe the optimized pattern in this case is

selected([ARBITRARY_VALUE], index/parent)

Since you are matching both conditions off of an index/parent match, I think you should be able to make this work by just adding the instance('commcaresession')/session/data/case_id value into the space-separated set of joined ID's from the previous query.

So instead of

selected(join(' ', instance('casedb')/casedb/case[index/parent = instance('commcaresession')/session/data/case_id]/@case_id), index/parent)

You could query for

selected(concat(join(' ', instance('casedb')/casedb/case[index/parent = instance('commcaresession')/session/data/case_id]/@case_id), ' ', instance('commcaresession')/session/data/case_id), index/parent)

If you are in a form, I might suggest putting the space separated string into an intermediate hidden value just so you can see and experiment more easily.