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

[yourboogieman]

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 signal
Execution execution = runtimeService.createExecutionQuery()
      .activityId("awaitYearlyMailingResponse")
      .processInstanceBusinessKey(PROSPECT_MAILING_PROCESS_BUSINESS_KEY)
      .singleResult();

////////////////////////////////////////////////////////
// This fails, as execution is unexpectedly null here //
////////////////////////////////////////////////////////
assertNotNull(execution);

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

Instead, the only way I found I could get the execution for this java receive task for the given process instance was with two separate queries as follows:

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")
      .processInstanceBusinessKey(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
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Is there a way to get the desired result in one query?

[jbarrez]

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?

[jbarrez]

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

[yourboogieman]

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.

[yourboogieman]

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

[jbarrez]

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>

[yourboogieman]

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>

[yourboogieman]

Is this a bug?

[frederikherema1]

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

[frederikherema1]

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…