3 Replies Latest reply on Mar 14, 2006 10:58 AM by julian_k

    Querying VariableInstance Is Giving Me A Headache

    julian_k

      What is the best way to select all task instances that belong to a given process instance with three assigned variables. More specifically, the three variables I would like to search by should have a given value and these variables were set when instantiating the process. I know how to conduct this query based on one variable, but two or more becomes a series of queries. Is there a way to do it in one query or view?

      Thanks.

        • 1. Re: Querying VariableInstance Is Giving Me A Headache
          julian_k

          ok I got it, I think...here is a query using a view and joins on the view and the processinstance table to get the processinstance that is not done with given variables...using the results I can do another query to get the tasks as needed:

           select * from jbpm_variableinstance a, (select processinstance_ from
           jbpm_variableinstance where name_ = 'XYZ' and stringvalue_ = '364834') subView,
           jbpm_processinstance b
           where a.processinstance_ = subView.processinstance_
           AND a.name_ = 'ABC'
           AND subView.processinstance_ = b.id_
           AND b.end_ IS NULL;
          

          WHERE I am doing a query based on two variables, one called XYZ with a value of 364834 and another variable called ABC. I guess one would have to create more views on the fly to get more data out based on a given variable name and value. Does this look to be the best way?

          Thanks,
          Julian

          • 2. Re: Querying VariableInstance Is Giving Me A Headache
            julian_k

            ok here is my final query. sorry for the multiple posts, but I made some changes at the last second:

             select a.* from jbpm_variableinstance a, (select processinstance_ from
             jbpm_variableinstance where name_ = 'XYZ' and stringvalue_ = '364834') subView,
             jbpm_processinstance b, jbpm_processdefinition c, jbpm_taskinstance d
             where a.processinstance_ = subView.processinstance_
             AND a.name_ = 'ABC'
             AND b.id_ = subView.processinstance_
             AND b.end_ IS NULL
             AND c.name_ = 'My Process'
             AND d.token_ = a.token_
             ORDER BY a.stringvalue_;
            

            This final query also joins the process definition table so as to query based on the process definition name. Finally it also joins the token in the variableinstance table to the token in taskinstance table. The end result of all this is a retrieval of all tasks from a given process instance where the process instance contains two variables (one with a given name and value and the other with a given name). The last step is to join on jbpm_taskdefinition to get a certain task by name. Long story short I'll get the tasks and value of the variable. If anyone's interested I'll post the final query.

            • 3. Re: Querying VariableInstance Is Giving Me A Headache
              julian_k

              Any performance tips? Joining all these tables to get out this data in real time is gonna be a headache.

              Thanks