topic Re: Fetch tasks that are available to be claimed by a specific user in Alfresco Archive

Fetch tasks that are available to be claimed by a specific user

jwestra — Tue, 15 Mar 2016 23:42:27 GMT

I just want to be sure I'm not missing an API somewhere.Goal: fetch tasks that are available to be claimed by a specific user.My requirements for the list are as follows:———————————————1. Tasks must be unassigned (assignee == null)2. Includes: All tasks where candidate groups is empty and candidate

Re: Fetch tasks that are available to be claimed by a specific user

schmke — Wed, 16 Mar 2016 01:52:26 GMT

One thing you could do is use taskCandidateUser which should do two of your requirements in one, but does require that you set up your users and groups in Activiti or configure a GroupEntityManager to provide the user/group mapping.

Re: Fetch tasks that are available to be claimed by a specific user

jwestra — Wed, 16 Mar 2016 02:24:04 GMT

Interesting. The "taskCandidateUser" JavaDoc sounds like it only compares the user to the Task's "Candidate Users" list.

If it actually does compare it to both "candidate users" and all users in the Task's candidate group's, then true. I can eliminate a query.

Re: Fetch tasks that are available to be claimed by a specific user

schmke — Wed, 16 Mar 2016 04:07:19 GMT

My experience is that it works for users being candidates via group as well.

Now, I'm having trouble getting or() to work as well, so still interested in hearing from anyone on how they are able to get it to work.

Re: Fetch tasks that are available to be claimed by a specific user

schmke — Wed, 16 Mar 2016 18:06:24 GMT

I've done some more testing and it seems there are some issues with or() in some cases. Or I'm not using or() correctly so feedback welcome.

I have several tasks in different states.

1) Unassigned at step with candidate group managers
2) Standalone task assigned to andy
3) Assigned to andy at step with no candidate group

User andy is a member of group managers using a custom GroupEntityManager.

Now the scenarios and different methods:

taskCandidateUser("andy") - Using this by itself works correctly, it returns the task #1 above and uses the custom GroupEntityManager to lookup the groups for andy and use the result in the SQL. The SQL being:
<code>
select distinct RES.*
from ACT_RU_TASK RES
inner join ACT_RU_IDENTITYLINK I on I.TASK_ID_ = RES.ID_
WHERE RES.ASSIGNEE_ is null
and I.TYPE_ = 'candidate'
    and (I.USER_ID_ = 'andy'
or I.GROUP_ID_ IN ( 'managers' )
)
order by RES.ID_ asc;
</code>

taskCandidateOrAssigned("andy") - I would expect this to return the task #1 as part of it being a candidate, and then return #2 and #3 as well as they are assigned. Only the latter occurs with #2 and #3 returned but not #1. The SQL generated doesn't get the group right as it appears to use ACT_ID_MEMBERSHIP instead of my custom GroupEntityManager. This appears to be a bug.
<code>
select distinct RES.*
from ACT_RU_TASK RES
left join ACT_RU_IDENTITYLINK I on I.TASK_ID_ = RES.ID_
WHERE (RES.ASSIGNEE_ = 'andy' or
(RES.ASSIGNEE_ is null and
(I.USER_ID_ = 'andy' or I.GROUP_ID_ IN
   (select g.GROUP_ID_ from ACT_ID_MEMBERSHIP g where g.USER_ID_ = 'andy')
)
)
)
order by RES.ID_ asc;
</code>

I'm guessing the above could be fixed with changes to Task.xml.

or().taskAssignee("andy").taskCandidateUser("andy").endOr() - This would seem to be the workaround for the issue above, doing the or manually rather than using the convenience method. Alas this one doesn't work either, it returns just task #1. So this seems to be a bug too. The SQL generated is:
<code>
select distinct RES.*
from ACT_RU_TASK RES
inner join ACT_RU_IDENTITYLINK I_OR0 on I_OR0.TASK_ID_ = RES.ID_
WHERE ( RES.ASSIGNEE_ = 'andy'
or (RES.ASSIGNEE_ is null
and I_OR0.TYPE_ = 'candidate'
        and ( I_OR0.USER_ID_ = 'andy'
   or I_OR0.GROUP_ID_ IN ( 'managers' )
)
)
)
order by RES.ID_ asc;
</code>

The problem here is the inner join and specifying a type of candidate misses tasks #2 and #3 that either don't have a row in ACT_RU_IDENTITYLINK or have a type of participant.

Things are consistent though as swapping the order, e.g. or().taskCandidateUser("andy").taskAssignee("andy").endOr() has the same SQL and result.

Unless I'm using or() wrong, these seem to be bugs so I'll open issues. But should I come up with a fix, what the process for submitting it for consideration?

Re: Fetch tasks that are available to be claimed by a specific user

schmke — Wed, 16 Mar 2016 18:28:06 GMT

Researching issues, there is already 2081 (https://activiti.atlassian.net/browse/ACT-2081) for the taskCandidateOrAssigned issue and I've added a comment to it. This is a major issue open for awhile so hopefully it can be fixed soon?

And I also opened 4134 for the issue with the incorrect results using the or() (https://activiti.atlassian.net/browse/ACT-4134).

Re: Fetch tasks that are available to be claimed by a specific user

jwestra — Thu, 17 Mar 2016 15:34:53 GMT

I may just have to do a native query on a view that pulls the data together according to our requirements listed in OP above. That said, I'd need to figure out how to create a view at start-up in the H2 DB, so our unit tests all work.

Re: Fetch tasks that are available to be claimed by a specific user

jwestra — Wed, 23 Mar 2016 05:15:12 GMT

Here's the ANSI standards compliant view and query we are using to be able to fetch "Available" tasks in a single query.
The assumptions are that you go find the groups for the user first and put them into the query. An example query is shown below as well.

<code>
DROP VIEW v_available_tasks;

CREATE VIEW v_available_tasks AS
    SELECT
    id_,
    name_,
    description_,
    category_,
    due_date_,
    proc_inst_id_,
    proc_def_id_,
    execution_id_,
    task_def_key_,
    owner_,
    assignee_,
    delegation_,
    priority_,
    parent_task_id_,
    suspension_state_,
    form_key_,
    user_id_,
    group_id_ as group_id_,
    coalesce(type_,'CANDIDATE') as type_,
    tenant_id_,
    create_time_,
    rev_
FROM (
SELECT act_ru_task.id_ as id_,
    act_ru_task.name_ as name_,
    act_ru_task.description_ as description_,
    act_ru_task.category_ as category_,
    act_ru_task.due_date_ as due_date_,
    act_ru_task.proc_inst_id_ as proc_inst_id_,
    act_ru_task.proc_def_id_ as proc_def_id_,
    act_ru_task.execution_id_ as execution_id_,
    act_ru_task.task_def_key_ as task_def_key_,
    act_ru_task.owner_ as owner_,
    act_ru_task.assignee_ as assignee_,
    act_ru_task.delegation_ as delegation_,
    act_ru_task.priority_ as priority_,
    act_ru_task.parent_task_id_ as parent_task_id_,
    act_ru_task.suspension_state_ as suspension_state_,
    act_ru_task.form_key_ as form_key_,
    act_ru_identitylink.user_id_ as user_id_,
    act_ru_identitylink.group_id_ as group_id_,
    act_ru_identitylink.type_ as type_,
    act_ru_task.tenant_id_ as tenant_id_,
    act_ru_task.create_time_ as create_time_,
    act_ru_task.rev_ as rev_
   FROM act_ru_task
     LEFT JOIN act_ru_identitylink ON act_ru_identitylink.task_id_::text = act_ru_task.id_::text
WHERE act_ru_task.assignee_ IS NULL) i;
</code>

Then a query like the one below meets all 3 of the OP requirements for an "Available" task in our system.

<code>
select *
from v_available_tasks
where (
group_id_ in ('Managers', 'Administrators')
or (group_id_ is null and user_id_ is null)
or (user_id_ = 'jason'))
</code>

It selects the same tasks multiple times (as it is a denormalized view after all!). I like that, because then I can collect the IdentityLinks too and the logic to weed out the dups in the end is easy.

Finally, if running H2 embedded, I have a Spring bean run JdbcTemplate.execute() on this view script to create it on-the-fly in the in-memory DB. This really seems to work well, if you if you are OK with not using Activiti TaskQuery APIs and going directly to a view.

Re: Fetch tasks that are available to be claimed by a specific user

jbarrez — Fri, 25 Mar 2016 12:56:28 GMT

Thanks for sharing this. I'm sure it'll help many people!