We have a variable called teamId set for (almost) every task. Currently we're querying it using something along the lines of:
select t.* from ACT_RU_TASK t
left join ACT_RU_VARIABLE v1 on t.ID_=v1.TASK_ID_ and v1.name="teamId' and v1.type='integer'
left join ACT_RU_VARIABLE v2 on t.EXECUTION_ID_=v2.EXECUTION_ID_ and v2.name="teamId' and v2.type='integer'
left join ACT_RU_VARIABLE v3 on t.PROC_INST_ID_=v3.PROC_INST_ID_ and v3.name="teamId' and v3.type='integer'
where (case when v1.LONG_ is not null then v1.LONG_ else case when v2.LONG_ is not null then v2.LONG_ else v3.LONG_) in (teamList)
-- further constraints
using the teams to control visibility to the users.
So far I'm satisfied with the results, we haven't had any situations where the results have done something unexpected or unwanted, but is this query the right way to do it?
We have tried non-native queries, but they were too slow for our purposes (we used teamId=1 or teamId=2 or..., and activiti generated one join table for every team in the team list).
We're using activiti 6.0.0 and Spring 4.2.5.RELEASE.