Activiti Troubleshooting to-do list queries complex, API unfriendly design scenarios

We use the workflow engine, a very important function is to get to-do list, in Activiti, we can query through the relevant API Taskservice, these API design elegant, but the actual use is often not convenient, and lack of flexibility, The following are some of the main issues that do not reach the technical solution requirements:

1. In most cases it is not possible to invoke an API to meet the requirements, and a real problem is that the result set needs to be merged and sorted, which makes it more cumbersome.

2. Difficulties associated with the project business table;

The related queries in 3.Activiti return the entities defined by Activiti, which may not contain enough information;

Entities in 4.Activiti may conflict with object-relational mapping (ORM) in the project;

For these reasons, in some large-scale projects, Activiti provides the query API, the actual use of small value, we need to find another solution. In the Activiti query API, the original SQL query interface is also available, but after a lot of use, the code is not elegant and maintenance is difficult. In fact, from the developer perspective, the query with the user's ID, with the simplest SQL query out all the desired information is the most ideal.

Analysis of the above shortcomings and requirements, we believe that through the API approach to query, there are always a variety of defects, so put the target on the database, if you can define the way the view to solve the problem, it will completely solve the query convenience, flexibility, universality problems.

After analyzing Activiti's database tables, we found that the tables that were not complicated and were related to the backlog, including Act_ru_task, act_ru_identitylink,act_ru_task, stored the task-relevant information, act_ru_ The candidate group and candidate information is stored in the Identitylink, a more important question is, the candidate group in the Activiti, how the candidate is related to the user, organization, and role in the system, the solution provided in this article assumes that there is a system named Sys_role_ User table, which stores the roles and user correspondence, and the candidate groups and roles in Activiti are the same concept, what is specific in the developer's system and needs the developer extrapolate, this article only provides a design idea.

In Activiti, for a node, can be divided into trustees, candidates and candidate groups of three cases, the latter two can be set multiple, separated by commas, corresponding to the database, will be split into a number of act_ru_identitylink records, which we need to consider, The details can be implemented by union, below is the sample code, which is based on the Oracle database, the version of the other database, which is explained later.

create view v_tasklist asselect a.id_ as task_id,        A.PROC_INST_ID_ PROC_INST_ID,       A.TASK_DEF_KEY_  as act_id,       a.name_ as act_name,        a.assignee_ as assignee,       a. delegation_ as delegation_id,       a.description_ as  Description,       to_char (a.create_time_,  ' YYYY-MM-DD HH24:MI:SS ')  as create_time,       to_char (A.DUE_DATE_, ' YYYY-MM-DD  HH24:MI:SS ')  AS DUE_DATE,       I.USER_ID CANDIDATE   FROM ACT_RU_TASK A  LEFT JOIN  (select distinct * from  (Select task_id_, to_cHAR (user_id_)  USER_ID                     FROM ACT_RU_IDENTITYLINK I, ACT_RU_TASK T                        WHERE TASK_ID_ IS NOT NULL                         and user_ id_ is not null                         AND I.TASK_ID_ = T.ID_                          AND T.ASSIGNEE_ IS NULL                         and type_ =  ' Candidate '                       UNION                      SELECT TASK_ID_, R.USER_ID                         FROM ACT_RU_IDENTITYLINK I,SYS_ROLE_USER R,ACT_RU_TASK T                        WHERE I.TASK_ID_ IS NOT NULL                         and i.group_id_  is not null                         AND I.TASK_ID_ = T.ID_                         and  T.ASSIGNEE_ IS NULL                         AND TYPE_ =  ' candidate '                          and i.group_id_ = r.role_id) U)  i--candidate Group and Business Role User Table Association     on a.id_ = i.task_id_

This view is relatively simple, mainly query the task information, if you need additional information, such as process instances, process definitions, etc., you can add additional table associations, such as to associate with the business table need a very important field is Business_key_, this and act_ru_ Execution Table Association.

Once this view is well defined, the agent query can be implemented with a more concise SQL implementation as follows:

SELECT * from v_tasklist WHERE ASSIGNEE =: UserID OR candidate =: UserID

In this case, and Business Table Association is also very convenient, not subject to the API restrictions, and does not involve the system of ORM compatibility issues, basically want to query what information can be a simple SQL query to what information, basic can be used as a general solution.

The above example only provides the Oracle code, for the compatibility of multi-database design, a lot of trouble, a variety of databases are created on the view to make a lot of restrictions, such as SQL Server can not write in the by,mysql in the order of the FROM clause can not be nested subqueries, As well as different database field type definitions, in our solution, we basically split the SQL above, define a few very small views, and then query those views v_tasklist the view. In particular, the developer can be flexible, this article is no longer expanded.

Activiti Troubleshooting to-do list queries complex, API unfriendly design scenarios