Forum OpenACS Development: case_id X role_id

Collapse
1: case_id X role_id
Posted by Iuri Sampaio on 02/05/10 12:33 AM
Hi there,

Does anyone know how to get the workflow case_id of a respective workflow role_id?

I found [workflow::case::get_id] but it isn't the one.

I have only workflow_id, role_id and short_name to use as parameters. And i haven't figured out YET how to retrieve any relevant and indirect info to reach case_id

/api-doc/proc-view?proc=workflow%3a%3acase%3a%3aget_id

I tried to start something by my own. But it turned out to be very complex to write the query

set case_id [db_list_of_lists get_case_ids { select case_id, party_id from workflow_case_role_map where role_id = :role_id}

Collapse
2: Re: case_id X role_id (response to 1)
Posted by Brian Fenton on 02/06/10 05:47 PM
Hi Iuri!

Well, I'm not too familiar with that version of Workflow (I've used the older workflow a lot), but it looks like you need to clarify the question you're asking. Looking at the datamodel here http://fisheye.openacs.org/browse/~raw,r=1.27/OpenACS/openacs-4/packages/workflow/sql/postgresql/workflow-tables-create.sql I can guess you're trying to ask for either "all the cases having a party in a particular role" or "all the cases assigned to a particular role".

My guess is you probably want the second, which would be something like:

select distinct case_id
from workflow_case_assigned_actions
where role_id = :role_id

The first query might be something like this:
select distinct case_id
from workflow_case_role_party_map
where role_id = :role_id

hope this helps
Brian