Hi,
I'd like to know if there is a way to query the casedb and get the last added child case of a case. I am looking at something that would be equivalent to ORDER BY open_date DESC LIMIT 1 if I could write it in SQL.
Thanks!
#it4life
Hi Guillaume,
Is this in the context of a form, or outside of a form?
If you are inside of a form, I think you should be able to achieve this by using an intermediate hidden value to create a list of the open dates which is sorted appropriately, then using the first item in the list to index back into the query.
Something like:
latest_date_opened = selected-at(sort(join(" ", instance('casedb')/casedb/case[index/parent = #case/@case_id]/open_date), false()), 0)
last_child_id = instance('casedb')/casedb/case[open_date = #form/last_date_opened]/@case_id
There might be some edge cases you'll need to deal with. There could be N children added on the same day, so you'll probably want to add a [1] qualifier to take the first item, for instance, and you might need to special case what happens if there are no current children, not sure if the expression is resilient against that.
-Clayton
P.S. That expression might even work as a one-liner with no intermediate nodes/values out of a form context, but I'd be a bit worried that it might be impossible to handle all of the edge cases.
Hi Clayton,
Thanks for your help. This is in the context of a form and I think your solution should work. Thanks a lot!
Cheers,
Hi,
Confirming that this approach worked.
A few takeaways that should be helpful for others:
- Always use the
join()function to process the result of the db query. You cannot store a nodeset in a hidden value, unless the query returns a single node (cases when your db is empty or has a single record). You basically want to make sure you always work with strings. - The
sort()function does not seem to work with empty strings, so make sure to test it before calling the function. Maybe good to note this in the wiki.
Cheers,
1 Like