Setting up a complex query in the Java API.
Hi everyone.
I am trying to set up a compound query in the Java API. I have two queries which seem to pull back the parts of the data I want, but am not sure how to join them. Here is the query as I would write it in a SQL where clause
where project_area = :projectArea
and owner = :owner
and type = "task"
and ((state_group= "open) or (state_group = "closed" and resolved_date between StartDate and EndDate))
Here is the query attributes I have already mapped out.
// Query where clause parameters
//Limit Project
IQueryableAttribute projectAttribute = findAttribute(teamRepository, projectArea, IWorkItem.PROJECT_AREA_PROPERTY, null);
Expression projectExpression = new AttributeExpression(projectAttribute, AttributeOperation.EQUALS, projectArea);
//Limit Owner
IQueryableAttribute ownerAttribute = findAttribute(teamRepository, projectArea, IWorkItem.OWNER_PROPERTY, null);
Expression ownerExpression = new AttributeExpression(ownerAttribute, AttributeOperation.EQUALS, getOwner(username, teamRepository));
//Limit to TASKs
IQueryableAttribute typeAttribute = findAttribute(teamRepository, projectArea, IWorkItem.TYPE_PROPERTY, null);
Expression typeExpression = new AttributeExpression(typeAttribute, AttributeOperation.EQUALS, "task");
//Limit to Open Statuses or Closed statuses closed within the last 7 days.
IQueryableAttribute stateOpenAttribute = findAttribute(teamRepository, projectArea, IWorkItem.STATE_PROPERTY, null);
Expression stateOpenExpression = new AttributeExpression(stateOpenAttribute, AttributeOperation.STATE_GROUP_EQUALS, "Open");
// Set up the where clause object
Term term = new Term(Operator.AND);
term.add(projectExpression);
term.add(ownerExpression);
term.add(typeExpression);
term.add(stateOpenExpression);
//Limit to TASKs
IQueryableAttribute stateClosedAttribute = findAttribute(teamRepository, projectArea, IWorkItem.STATE_PROPERTY, null);
Expression stateClosedExpression = new AttributeExpression(stateClosedAttribute, AttributeOperation.STATE_GROUP_EQUALS, "Closed");
// Set up the where clause object
Term termclosed = new Term(Operator.AND);
termclosed.add(projectExpression);
termclosed.add(ownerExpression);
termclosed.add(typeExpression);
termclosed.add(stateClosedExpression);
As you can see I have the two different query sets, but don't know how to structure this in the code to return both sets in the same query.
If the Term object were to accept another Term object as input, I would build the "open" Term object as an AND operator, and the "closed" term object as an AND operator, and then create the container Term object as an OR and add them both to it.
Any advice on how to go about doing this would be greatly appreciated.
|
Accepted answer
Hi Will,
From what I looked up in the API, there doesn't seem to be a clear way as to use the Term in complex queries like the one you are trying to do. It only seems to associate with several expressions.
One possible solution(possibly the quickest) would be to just run each of the queries, get the work item ids in 2 separate hashsets and then union them. This is more of a workaround.
API wise, I think the IPredicate classes are more suited to complex queries. To perform the query you want:
BaseWorkItemQueryModel queryModel = BaseWorkItemQueryModel.ROOT;
IPredicate queryPredicate = queryModel.owner().emailAddress()._eq(userEmail)._and(queryModel.workItemType()._eq(workItemType))._and((queryModel.internalState()._eq(openstateid)._or(queryModel.internalState()._eq(closedstateid)._and(queryModel.resolution()._gtOrEq(Dateinstance)._and(queryModel.resolution()._ltOrEq(Dateinstance))));
IItemQuery query = IItemQuery.FACTORY.newInstance((IItemQueryModel)queryModel);
query.filter(queryPredicate)
TeamRepository tr = getTeamRepository(); //however you get the ITeamRepository
IItemQueryPage qPage = tr.getQueryService().queryItemsInContext(query,IQueryService.EMPTY_PARAMETERS,IQueryService.ITEM_QUERY_MAX_PAGE_SIZE,new UUID[]{getProjectArea().getContextId()});
QueryPageImpl qPageImpl = (QueryPageImpl)qPage;
List<IItemHandle> itemHandles = qPage.getItemHandles();
List<IItem> items = resolveItems(itemHandles);
you can cast the IItem list to IWorkItem. To resolve the items you need to use the itemManager.fetchCompletePermissionsAware method on the IItemHandle list
William Hunsicker selected this answer as the correct answer
|
One other answer
Ralph Schoon (63.5k●3●36●46)
| answered Apr 05 '13, 2:41 p.m.
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER
You can try to look at https://rsjazz.wordpress.com/2012/11/19/using-expressions-for-automation/ and use terms to compose the conditions.
|
Your answer
Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.