`TaskQuery.and(TaskQuery)` and `CaseQuery.and(CaseQuery)` doesn't work with `additionalProperty()`.
On our project, we extensively uses the API `TaskQuery` and `CaseQuery` in order to find `ITask` and `ICase` respectively. We also make use of the `additionalProperty` of both `ITask` and `ICase` in order to store custom fields.
However, we later found out that the method `and(TaskQuery` and `or(TaskQuery)` doesn't work with the criteria `additionalProperty`.
Consider the below snippet of code:
public List<ITask> 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");
}
Runing the code will result as an exception:
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]
While this works:
public List<ITask> findTask() {
Ivy.wf().getTaskQueryExecutor()
.getResults(TaskQuery.create().where()
.state().isEqual(SUSPENDED)
.and().additionalProperty("custom").isLike("customized_value");
}
Please note that only the criterion `additionalPropety()` has this problem, the other criteria don't.
Digging a little deeper, I found that the queries produced are different:
The former (the problematic one) produces:
SELECT * FROM IWA_Task WHERE
(Column(IWA_TaskQuery.State) = 4 &&
((Column(AdditionalPropertyForTask.Name) = custom || Column(AdditionalPropertyForTask.Name) IS NULL)
&& Column(AdditionalPropertyForTask.Value) LIKE customized_value))
...whereas the latter produces (correctly):
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 && (Column(AdditionalPropertyForTask.Name) = custom || Column(AdditionalPropertyForTask.Name) IS NULL) && Column(AdditionalPropertyForTask.Value) LIKE customized_value)
This problem occurs both on `TaskQuery` and `CaseQuery` and on Axon.ivy 5.1.x (`additionalProperty()` has been introduced since 5.1.x).
I think this is probrably a bug in Axon.ivy query API. While there is a work-around (don't use `and(TaskQuery` with `additionalProperty()`), I think it is not good to have inconsistency in the API. I hope this would be fixed soon.
I have created a demo project which can reproduce the problem on Axon.ivy. Download it at [box.net][1][box.net][1].
[1]: https://app.box.com/s/9wxc8iqqrfmqotljiutqdwlvvoem55dh.https://app.box.com/s/9wxc8iqqrfmqotljiutqdwlvvoem55dh
