Custom QueryServicesClient query filtering by multiple process instance variables, with paging and sorting

263 views
Skip to first unread message

Marcin Świderski

unread,
Aug 20, 2019, 11:55:03 AM8/20/19
to jBPM Usage
Hello,

I have some trouble writing custom query for filtering process instances by multiple process instance variables at the same time.

I want to query with paging and multiple optional filters. Here is m setup:

I have created a process with following variables:

PROCESS_VARIABLES.png

Now I want to filter by processName variable AND by year variable.

My approach is to use QueryServicesClient, <T> List<T> query(String queryName, String mapper, QueryFilterSpec filterSpec, Integer page, Integer pageSize, Class<T> resultType) method.

I register query with this content:
private static final String PROCESS_INSTANCE_QUERY = "select pil.*, " +
        "v_year.variableId as v_year_variableId, v_year.value as v_year_value, " +
        "v_processName.variableId as v_processName_variableId, v_processName.value as v_processName_value " +
        "from ProcessInstanceLog pil " +
        "inner join (select vil.processInstanceId, vil.variableId, max(vil.ID) maxvilid from VariableInstanceLog vil where vil.variableId='year' group by vil.processInstanceId, vil.variableId) x_year " +
        "on (x_year.processInstanceId = pil.processInstanceId) " +
        "inner join VariableInstanceLog v_year " +
        "on (v_year.variableId = x_year.variableId and v_year.id = x_year.maxvilid and v_year.processInstanceId = pil.processInstanceId) " +
        "inner join (select vil.processInstanceId, vil.variableId, max(vil.ID) maxvilid from VariableInstanceLog vil where vil.variableId='processName' group by vil.processInstanceId, vil.variableId) x_processName " +
        "on (x_processName.processInstanceId = pil.processInstanceId) " +
        "inner join VariableInstanceLog v_processName " +
        "on (v_processName.variableId = x_processName.variableId and v_processName.id = x_processName.maxvilid and v_processName.processInstanceId = pil.processInstanceId) ";

I am setting up the filter like this:

    private QueryFilterSpec filterSpec(ActiveProcessesFilterAndPaging filterAndPaging) {
        QueryFilterSpecBuilder builder = new QueryFilterSpecBuilder();
        builder.oderBy(filterAndPaging.getOrderBy(), true);
        setProcessParameters(filterAndPaging, builder);
        return builder.get();
    }

    private void setProcessParameters(ActiveProcessesFilterAndPaging filterAndPaging, QueryFilterSpecBuilder builder) {
        maybeAddTitle(filterAndPaging, builder);
        maybeAddYear(filterAndPaging, builder);
        builder
            .addColumnMapping("v_year_variableId", "string")
            .addColumnMapping("v_year_value", "integer")
            .addColumnMapping("v_processName_variableId", "string")
            .addColumnMapping("v_processName_value", "string")
        ;
    }

    private void maybeAddTitle(ActiveProcessesFilterAndPaging filterAndPaging, QueryFilterSpecBuilder queryFilterSpecBuilder) {
        String title = filterAndPaging.getTitle();
        if (nonNull(title)) {
            queryFilterSpecBuilder
                .equalsTo("v_processName_variableId", "processName")
                .likeTo("v_processName_value", false, "%" + title + "%");
        }
    }

    private void maybeAddYear(ActiveProcessesFilterAndPaging filterAndPaging, QueryFilterSpecBuilder queryFilterSpecBuilder) {
        Integer year = filterAndPaging.getYear();
        if (nonNull(year)) {
            queryFilterSpecBuilder
                .equalsTo("v_year_variableId", "year")
                .equalsTo("v_year_value", year);
        }
    }

Then I query the kie server:

List<ProcessInstance> processes = queryClient.query(
            ACTIVE_PROCESSES_QUERY_NAME,
            QueryServicesClient.QUERY_MAP_PI_WITH_VARS,
            filterSpec(activeProcessesFilterAndPaging),
            activeProcessesFilterAndPaging.getPageNumber(),
            activeProcessesFilterAndPaging.getPageSize(),
            ProcessInstance.class
        );

And, instead of the result, I get exception on server side:

Caused by: javax.ejb.EJBTransactionRolledbackException: Unexpected HTTP response code when requesting URI 'http://jbpm:8080/kie-server/services/rest/server/queries/definitions/ACTIVE_PROCESSES_QUERY_NAME/filtered-data?mapper=ProcessInstancesWithVariables&page=0&pageSize=100'! Error code: 500, message: "Unexpected error during processing: Error marshalling input
"

What am I lacking for this to work?

Kind regards,
Marcin Świderski

Maciej Swiderski

unread,
Aug 21, 2019, 3:08:44 PM8/21/19
to Marcin Świderski, jBPM Usage
I don’t think this will perform in a decent way considering rather big volume of data. The VariableInstanceLog table logs all variable changes which makes it rather expensive to search by latest version. So I’d recommend to push out variable info into another table via process event listener that will be then used for searches. You can easily fine tune it as you know your process design and by that you can use columns instead of rows for the same process instance and thus make it efficient to search through.

Maciej

On 20 Aug 2019, at 17:55, Marcin Świderski <marcin.l....@gmail.com> wrote:

Hello,

I have some trouble writing custom query for filtering process instances by multiple process instance variables at the same time.

I want to query with paging and multiple optional filters. Here is m setup:

I have created a process with following variables:


--
You received this message because you are subscribed to the Google Groups "jBPM Usage" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jbpm-usage+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jbpm-usage/1cbed004-f30f-4675-a63f-40766c2a880f%40googlegroups.com.
<PROCESS_VARIABLES.png>

Marcin Świderski

unread,
Aug 22, 2019, 4:57:33 AM8/22/19
to jBPM Usage
Thank you for advice. Is there a manual on adding new tables and embedding them in the process (to be used by process event listener)?

Kind regards,
Marcin Świderski


W dniu środa, 21 sierpnia 2019 21:08:44 UTC+2 użytkownik Maciej Swiderski napisał:
I don’t think this will perform in a decent way considering rather big volume of data. The VariableInstanceLog table logs all variable changes which makes it rather expensive to search by latest version. So I’d recommend to push out variable info into another table via process event listener that will be then used for searches. You can easily fine tune it as you know your process design and by that you can use columns instead of rows for the same process instance and thus make it efficient to search through.

Maciej

To unsubscribe from this group and stop receiving emails from it, send an email to jbpm-...@googlegroups.com.

karan kanojiya

unread,
Aug 22, 2019, 5:29:57 AM8/22/19
to jBPM Usage
Hi Maciej,

Please make any video tutorial or blog  manual on adding new tables and embedding them in the process using by process event listener for process variable data.
Its a major requirement and need of all JBPM User

Regards,
Karan Kanojiya


On Thursday, August 22, 2019 at 12:38:44 AM UTC+5:30, Maciej Swiderski wrote:
I don’t think this will perform in a decent way considering rather big volume of data. The VariableInstanceLog table logs all variable changes which makes it rather expensive to search by latest version. So I’d recommend to push out variable info into another table via process event listener that will be then used for searches. You can easily fine tune it as you know your process design and by that you can use columns instead of rows for the same process instance and thus make it efficient to search through.

Maciej

To unsubscribe from this group and stop receiving emails from it, send an email to jbpm-...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages