topic Re: Have to query for process instance by business key and then for execution in Alfresco Archive

Have to query for process instance by business key and then for execution

yourboogieman — Sun, 05 Jan 2014 00:29:08 GMT

I have a use case that requires me to query for all receive tasks for a given process instance. Intuitively, it seems I should be able to get this information in one query as follows:// find the wait task that is awaiting a mailing response signalExecution execution = runtimeService.createExecution

Re: Have to query for process instance by business key and then for execution

jbarrez — Mon, 06 Jan 2014 12:27:41 GMT

That does look strange: why does passing the id as business key works:

<code>
.processInstanceBusinessKey(processInstance.getId())
</code>

That shouldn't work.

What do you get back once you leave the processInstanceBusinessKey from the first query? Does it give you back a result then?

Re: Have to query for process instance by business key and then for execution

jbarrez — Mon, 06 Jan 2014 12:28:05 GMT

Also, did you understand that business key != process definition key?

Re: Have to query for process instance by business key and then for execution

yourboogieman — Wed, 08 Jan 2014 05:19:31 GMT

Cripes, my apologies. I mistyped or copy/pasted the wrong code. Here is what the second snippet is supposed to be:

<java>
ProcessInstance processInstance = runtimeService
.createProcessInstanceQuery()
.processInstanceBusinessKey(PROSPECT_MAILING_PROCESS_BUSINESS_KEY)
.singleResult();

// find the wait task that is awaiting a mailing response signal
Execution execution = runtimeService.createExecutionQuery()
.activityId("awaitYearlyMailingResponse")
.processInstanceId(processInstance.getId())
.singleResult();

//////////////////////////////////////////////////////////////////////////////////
// Returns expected result, but why should I have to do two queries? *pouts* //
//////////////////////////////////////////////////////////////////////////////////
assertNotNull(execution);

// deliver receive mailing response signal
runtimeService.signal(execution.getId(),
Collections.singletonMap("askedOffMailingList", (Object) false)); // was not an ask-off-list response
</java>

And yes, I understand that process business key != process definition ID or process instance ID.

Re: Have to query for process instance by business key and then for execution

yourboogieman — Wed, 08 Jan 2014 05:22:05 GMT

The call to the first query using the business key returns the process instance as expected.

Re: Have to query for process instance by business key and then for execution

jbarrez — Wed, 08 Jan 2014 09:37:52 GMT

Ok, now i see.

Did you try the method with the boolean, that fetches the child executions too (and not only proc instances)

<code>
/**
   * Only executions with the given business key.
   * Similar to {@link #processInstanceBusinessKey(String)}, but allows to choose
   * whether child executions are returned or not.
   */
ExecutionQuery processInstanceBusinessKey(String processInstanceBusinessKey, boolean includeChildExecutions);
</code>

Re: Have to query for process instance by business key and then for execution

yourboogieman — Fri, 10 Jan 2014 03:27:05 GMT

I changed the query to be as follows:

<java>
// find the wait task that is awaiting a mailing response signal
Execution execution = runtimeService.createExecutionQuery()
.activityId("awaitYearlyMailingResponse")
.processInstanceBusinessKey(PROSPECT_MAILING_PROCESS_BUSINESS_KEY, true)
.singleResult();
</java>

Which results in this:

<code>
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: org.h2.jdbc.JdbcSQLException: Ambiguous column name "IS_ACTIVE_"; SQL statement:
select distinct RES.* , P.KEY_ as ProcessDefinitionKey, P.ID_ as ProcessDefinitionId
    from ACT_RU_EXECUTION RES
    inner join ACT_RE_PROCDEF P on RES.PROC_DEF_ID_ = P.ID_
    inner join ACT_RU_EXECUTION INST on RES.PROC_INST_ID_ = INST.ID_
WHERE INST.BUSINESS_KEY_ = ?
and RES.ACT_ID_ = ? and IS_ACTIVE_ = ?
order by RES.ID_ asc
LIMIT ? OFFSET ? [90059-168]
### The error may exist in org/activiti/db/mapping/entity/Execution.xml
### The error may involve org.activiti.engine.impl.persistence.entity.ExecutionEntity.selectExecutionsByQueryCriteria
### The error occurred while executing a query
### SQL: select distinct RES.* , P.KEY_ as ProcessDefinitionKey, P.ID_ as ProcessDefinitionId
             from ACT_RU_EXECUTION RES
    inner join ACT_RE_PROCDEF P on RES.PROC_DEF_ID_ = P.ID_
    inner join ACT_RU_EXECUTION INST on RES.PROC_INST_ID_ = INST.ID_
    WHERE INST.BUSINESS_KEY_ = ?
    and RES.ACT_ID_ = ?
    and IS_ACTIVE_ = ?
    order by RES.ID_ asc
    LIMIT ? OFFSET ?
### Cause: org.h2.jdbc.JdbcSQLException: Ambiguous column name "IS_ACTIVE_"; SQL statement:
select distinct RES.* , P.KEY_ as ProcessDefinitionKey, P.ID_ as ProcessDefinitionId
from ACT_RU_EXECUTION RES
inner join ACT_RE_PROCDEF P on RES.PROC_DEF_ID_ = P.ID_
inner join ACT_RU_EXECUTION INST on RES.PROC_INST_ID_ = INST.ID_
WHERE INST.BUSINESS_KEY_ = ?
and RES.ACT_ID_ = ? and IS_ACTIVE_ = ?
order by RES.ID_ asc
LIMIT ? OFFSET ? [90059-168]
</code>

Re: Have to query for process instance by business key and then for execution

yourboogieman — Wed, 15 Jan 2014 06:53:44 GMT

Is this a bug?

Re: Have to query for process instance by business key and then for execution

frederikherema1 — Wed, 15 Jan 2014 09:07:37 GMT

I'll try to reproduce, but smells like a bug indeed… What version are you using?

Re: Have to query for process instance by business key and then for execution

frederikherema1 — Wed, 15 Jan 2014 10:07:15 GMT

That's indeed a bug, what using activityId and processInstanceBusinessKey(…, true) together. The IS_ACTIVE_ is no longer unique, since the process-definition is joined in, having both the ACT_RU_EXECUTION and ACT_RE_PROCDEF IS_ACTIVE_ columns in the query scope.

I've fixed this issue in the current master, will be part of 5.15:
http://jira.codehaus.org/browse/ACT-1896
https://github.com/Activiti/Activiti/commit/723d521d389384365172451e624c8044ddde11d8

As a workaround for now you can try querying the Executions based on business-key. Most of the time, this will be all execution for a single process-instance, and won't be a huge list. Next, iterate the resulting executions and filer out the ones that don't have the required activityId…

Re: Have to query for process instance by business key and then for execution

yourboogieman — Sat, 18 Jan 2014 21:35:32 GMT

Thanks for the update on this and sorry for the belated response. I'm using version 5.14. I'll look for the fix with 5.15 and, until then, I'll use the workaround you suggested.