Activiti6 Task query API mapping SQL

1, Foreword

Activiti itself provides a set of user management, but the business system itself has a set of user management system in development. At this time, some user data in the business system should be synchronized to activiti user table management. So what if it's not synchronized? At first, many developers thought that there must be users to run the Task. In fact, the information of Task Assignee, Candidate Users and Candidate Groups has been saved in act in the form of string_ ru_ Tak and act_ RU_ In the identitylink table. Only a few query API s use ACT_ID_* Perform association query on the data in the table.

The log adjusts the avtiviti correlation to the DEBUG mode interface and prints the corresponding sql.

2, Query interface

2.1 taskAssignee

Query according to the assigned person, and directly query the table ACT_RU_TASK.
API:

taskService.createTaskQuery().taskAssignee(userId);

SQL:

select distinct RES.* from ACT_RU_TASK RES WHERE RES.ASSIGNEE_ = ?

2.2 taskCandidateGroup

Query by allocation group, no act is used_ ID_* Table data.
API:

taskService.createTaskQuery().taskCandidateGroup("division manager");

SQL:

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.GROUP_ID_ IN(?))

2.3 taskCandidateGroupIn

Query by allocation group, no act is used_ ID_* Table data.
API:

List<String> candidateGroups = new ArrayList<String>();
candidateGroups.add("division manager");
candidateGroups.add("CEO");
taskService.createTaskQuery().taskCandidateGroupIn (candidateGroups);

SQL:

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.GROUP_ID_ IN(?))

2.4 taskCandidateOrAssigned

According to the assignment group OR, the assignor queries, and the act is used_ ID_* The data of the table. Pass in userId to query the permission group.
API:

taskService.createTaskQuery().taskCandidateOrAssigned(userId);

SQL:
First, query the permissions of the user's group

SELECT
  g.*
FROM ACT_ID_GROUP g,
  ACT_ID_MEMBERSHIP membership
WHERE g.ID_ = membership.GROUP_ID_
    AND membership.USER_ID_ = ?

Then query the assigned person task or the task whose assigned person is empty and whose permission group the user belongs to

SELECT DISTINCT
  RES.*
FROM ACT_RU_TASK RES
  LEFT JOIN ACT_RU_IDENTITYLINK I
    ON I.TASK_ID_ = RES.ID_
WHERE (RES.ASSIGNEE_ = ?
        OR (RES.ASSIGNEE_ IS NULL
            AND (I.USER_ID_ = ?
                  OR I.GROUP_ID_ IN(SELECT
                                      g.GROUP_ID_
                                    FROM ACT_ID_MEMBERSHIP g
                                    WHERE g.USER_ID_ = ?))))

2.5 taskCandidateUser

API:

taskService.createTaskQuery().taskCandidateUser(userId);

SQL:
First find the group to which the user belongs (the information of the user group to which the user belongs is obtained from the ACT_ID_ * table of Activiti)

SELECT
  g.*
FROM ACT_ID_GROUP g,
  ACT_ID_MEMBERSHIP membership
WHERE g.ID_ = membership.GROUP_ID_
    AND membership.USER_ID_ = ?

If user group information is found

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_ = ?
          OR I.GROUP_ID_ IN(?))

If the group to which the user belongs cannot be found

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_ = ?)

2.6 taskOwner

Query by owner
API:

taskService.createTaskQuery().taskOwner(userId);

SQL:

SELECT DISTINCT
  RES.*
FROM ACT_RU_TASK RES
WHERE RES.OWNER_ = ?

3, Explain

Some queries use the user information provided by Activiti, such as taskcandidatorassigned. If your own permission system is used in the project, these API s are not smart. Here are three schemes for reference only:

  1. Synchronize system user permission information to Activiti's own user group, not recommended.
  2. Modify the query SQL in the source code Task.xml and replace the user table of Activiti with the user table of business system. It is not recommended.
  3. Overwrite an act with the same name with a view_ ID_ For series tables, it is recommended to ensure the data type of the created views.

Reference link: https://blog.52itstyle.vip/archives/3690/.

Tags: SQL

Posted on Wed, 10 Nov 2021 04:03:01 -0500 by plapeyre