I've been wrestling with some documents in CouchDB and when I found myself
thinking "I need a cartesian product", I decided to throw myself on the
mercy of the list for some help.
This is for a medical application where we have clients and caregivers. Each
day clients are dropped off and picked up and when dropped off, a caregiver
is assigned to them. This produces a number of documents in the form of
(ignoring _id and _rev):
{
staff_id: string,
client_id: string,
hours: integer,
date: string
}
On any given day, one staff will be assigned to 0<->Many Clients.
I was successfully able to create view where I can query by date and staff
to see who the staff worked with and how many "total" client hours they
provided.
[date,staff_id] =>
{total_client_hours,client_count,[client_id1,client_id2,client_id3]}
I was also able to create a view where I can query by date and client to see
which staff that client was assigned.
[date,client_id] => {staff_id}
What I need to be able to do is query by date and client to see how many
other clients were served by the same staff member, that is, I'd like to
have:
[date,client_id] => {staff_id,total_client_count_for_staff}
It would also be nice to have the list of other clients in the output, but
that's not super important.
Ideally, I would prefer not to change the fundamental document structure. We
have multiple locations, so it's very convenient for each location to
generate their own attendance records which are then centralized for
processing.
At the moment it is our policy that each client has exactly one staff
member. Solutions which leverage this fact are fine.
All the best,
~ Christopher