https://answers.axonivy.com/questions/1337/taskquery-and-taskquery-and-casequery-and-casequery-doesn-t-work-with-additionalproperty<p>On our project, we extensively uses the API <code>TaskQuery</code> and <code>CaseQuery</code> in order to find <code>ITask</code> and <code>ICase</code> respectively. We also make use of the <code>additionalProperty</code> of both <code>ITask</code> and <code>ICase</code> in order to store custom fields.</p> <p>However, we later found out that the method <code>and(TaskQuery</code> and <code>or(TaskQuery)</code> doesn't work with the criteria <code>additionalProperty</code>.</p> <p>Consider the below snippet of code:</p> <pre><code>public List&lt;ITask&gt; findTask() { Ivy.wf().getTaskQueryExecutor() .getResults(TaskQuery.create().where() .state().isEqual(SUSPENDED) .and(hasCustomField())); } private static TaskQuery hasCustomField() { return TaskQuery.create().where().additionalProperty("custom").isLike("customized_value"); } </code></pre> <p>Runing the code will result as an exception:</p> <pre><code>java.sql.SQLException: Column not found: ADDITIONALPROPERTYFORTASK.NAME in statement [SELECT IWA_TASKQUERY.TASKID ....... FROM IWA_TaskQuery WHERE (IWA_TASKQUERY.APPLICATIONID = ? AND (IWA_TASKQUERY."STATE" = ? AND ((ADDITIONALPROPERTYFORTASK.NAME = ? OR ADDITIONALPROPERTYFORTASK.NAME IS NULL) AND ADDITIONALPROPERTYFORTASK."VALUE" LIKE ?))) ORDER BY IWA_TASKQUERY.TASKID ASC] </code></pre> <p>While this works:</p> <pre><code>public List&lt;ITask&gt; findTask() { Ivy.wf().getTaskQueryExecutor() .getResults(TaskQuery.create().where() .state().isEqual(SUSPENDED) .and().additionalProperty("custom").isLike("customized_value"); } </code></pre> <p>Please note that only the criterion <code>additionalPropety()</code> has this problem, the other criteria don't.</p> <p>Digging a little deeper, I found that the queries produced are different:</p> <p>The former (the problematic one) produces:</p> <pre><code>SELECT * FROM IWA_Task WHERE (Column(IWA_TaskQuery.State) = 4 &amp;amp;&amp;amp; ((Column(AdditionalPropertyForTask.Name) = custom || Column(AdditionalPropertyForTask.Name) IS NULL) &amp;amp;&amp;amp; Column(AdditionalPropertyForTask.Value) LIKE customized_value)) </code></pre> <p>...whereas the latter produces (correctly):</p> <pre><code>SELECT * FROM IWA_Task LEFT JOIN IWA_TaskAdditionalProperty ON Column(IWA_TaskQuery.TaskId) = Column(IWA_TaskAdditionalProperty.TaskId) LEFT JOIN IWA_AdditionalProperty ON Column(IWA_TaskAdditionalProperty.AdditionalPropertyId) = Column(IWA_AdditionalProperty.AdditionalPropertyId) WHERE (Column(IWA_TaskQuery.State) = 4 &amp;&amp; (Column(AdditionalPropertyForTask.Name) = custom || Column(AdditionalPropertyForTask.Name) IS NULL) &amp;&amp; Column(AdditionalPropertyForTask.Value) LIKE customized_value) </code></pre> <p>This problem occurs both on <code>TaskQuery</code> and <code>CaseQuery</code> and on Axon.ivy 5.1.x (<code>additionalProperty()</code> has been introduced since 5.1.x).</p> <p>I think this is probrably a bug in Axon.ivy query API. While there is a work-around (don't use <code>and(TaskQuery</code> with <code>additionalProperty()</code>), I think it is not good to have inconsistency in the API. I hope this would be fixed soon.</p> <p>I have created a demo project which can reproduce the problem on Axon.ivy. Download it at <a href="https://app.box.com/s/9wxc8iqqrfmqotljiutqdwlvvoem55dh">box.net</a>.</p>enWed, 01 Jul 2015 16:09:44 -0400https://answers.axonivy.com/questions/1337/taskquery-and-taskquery-and-casequery-and-casequery-doesn-t-work-with-additionalproperty/1389<p>Thanks for reporting and the created demo project.</p> <p>I have just analysed and fixed the API for Axon.ivy 5.1. The next Hotfix is 5.1.6 and will be available on our <a href="http://developer.axonivy.com/download/">download page</a> till the end of next week. </p> <p>Fore more info or notifications see Issue</p> <pre><code>#25579: additionalProperty() does not work with TasQuery or CaseQuery when using .or(..), .and(..) or .not(..) </code></pre>Flavio SadeghiWed, 01 Jul 2015 16:09:44 -0400https://answers.axonivy.com/questions/1337/taskquery-and-taskquery-and-casequery-and-casequery-doesn-t-work-with-additionalproperty/1389