How to use order by clause in query using java api?
Nilesh Patil (17●3●41●50)
| asked Oct 24 '13, 1:29 a.m.
edited Oct 27 '13, 1:34 p.m. by Geoffrey Clemm (30.1k●3●30●35)
I have situation where I want query RTC database with order by(descending order) clause using java client api. I want to get only two records(to be very precise one record only) whose value is less than my condition(Integer number).
Can anyone help on this please. Sample code snippet will be very helpful Thanks in advance, Nilesh Patil. |
6 answers
Ralph Schoon (63.5k●3●36●46)
| answered Oct 24 '13, 6:38 a.m.
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER
Please see my new addition in http://rsjazz.wordpress.com/2012/11/19/using-expressions-for-automation/ and also see
https://jazz.net/forum/questions/13870/querying-for-sorted-list-of-quotunresolvedquot-work-item |
The fix (plus improved progress monitoring), in com.ibm.team.build.internal.ui.dialogs.definitions.FetchBuildDefinitionsInConnectedProjectAreasJob.fetchBuildDefinitions(ITeamRepository, IProgressMonitor), looks like:
SubMonitor subMonitor = SubMonitor.convert(monitor, 4);
// get the team areas for the connected project areas in the repo and // use them to narrow the search IProcessAreaHandle[] processAreaHandles = getProcessAreaHandles(repository, subMonitor.newChild(1));
// create the query IBuildDefinitionQueryModel buildDefinitionQueryModel = IBuildDefinitionQueryModel.ROOT; final IItemQuery query = IItemQuery.FACTORY.newInstance(buildDefinitionQueryModel);
IItemHandleInputArg[] itemHandleArgs = new IItemHandleInputArg[processAreaHandles.length]; for (int i = 0; i < processAreaHandles.length; i++) { itemHandleArgs[i] = query.newItemHandleArg(); }
IPredicate filter = ((BuildDefinitionQueryModel) buildDefinitionQueryModel).processArea()._in(itemHandleArgs); query.filter(filter); query.orderByDscUsingLocale(buildDefinitionQueryModel.id());
// get the results IItemManager itemManager = repository.itemManager(); ItemQueryIterator<IBuildDefinitionHandle> iter = new ItemQueryIterator<IBuildDefinitionHandle>( ClientFactory.getTeamBuildClient(repository), query, processAreaHandles); LinkedList<IBuildDefinition> buildDefinitions = new LinkedList<IBuildDefinition>(); float size = iter.size(subMonitor.newChild(1)); subMonitor.setWorkRemaining((int) (3 * Math.ceil(size / IQueryService.ITEM_QUERY_MAX_PAGE_SIZE))); while (iter.hasNext(subMonitor.newChild(1))) { List<IBuildDefinitionHandle> definitionHandles = iter.next(IQueryService.ITEM_QUERY_MAX_PAGE_SIZE, subMonitor.newChild(1)); buildDefinitions.addAll(itemManager.fetchCompleteItemsPermissionAware(definitionHandles, IItemManager.DEFAULT, subMonitor.newChild(1)).getRetrievedItems()); } return buildDefinitions.toArray(new IBuildDefinition[buildDefinitions.size()]); |
thanks.. (hate this forum for limit on comment size) I would only need approvals and their descriptors from a workitem, nothing else.. I am just trying to get a count of pending approvals for workitems in this project area.. expect ~100 maybe max. for the users returned in the role selected in this project area.. maybe 10 users.. I'm going to compare UUIDs so I don't have to load the contributor records either.
I think this is what it looks like (not working, just written)
IQueryPage qpage = fQueryService.queryItems(query, new IProjectArea[]{(IProjectArea) workItem.getProjectArea()}, IQueryService.ITEM_QUERY_MAX_PAGE_SIZE);
List<IWorkItemHandle> selectedWorkitemhandles = new ArrayList<IWorkItemHandle>();
while(qpage.hasNext())
{
selectedWorkitemhandles.addAll(((IItemQueryPage) qpage).getItemHandles());
qpage = fQueryService.fetchPage(qpage.getToken(), qpage.getNextStartPosition(), IQueryService.ITEM_QUERY_MAX_PAGE_SIZE);
}
List<IWorkItem> selectedWorkItems = (List<IWorkItem>) fRepositoryService.fetchItemsPermissionAware(selectedWorkitemhandles.toArray(new IWorkItemHandle[1]), new String[]{"test"}).getRetrievedItems();
for(IWorkItem wi:selectedWorkItems){
selectedApprovals.addAll(wi.getApprovals().getContents());
}
Comments Looks ok as a sketch. You'll want to use selectedWorkitemhandles.size() instead of 1, and give the actual property names. It's fetchPartialItemsPermissionAware for partial fetch. If you do the fetch inside the paging loop, you'll avoid the 2048 limit for max items fetched at once. Actually, qpage.hasNext() will be false if the results all fit in one page. Try:
while (qpage != null) {
…
qpage = qpage.hasNext() ? <fetch next page> : null
}
There are also some internal query iterators that help do the paging for you, e.g. com.ibm.team.build.internal.client.iterator.ItemQueryIterator<T extends IItemHandle>
Of course, I'd never recommend you use internals :).
sam detweiler
commented Jan 29 '14, 5:00 p.m.
repositoryitemservice doesn't have a fetchPartialItemsPermissionaware Sorry, I assumed you were on the client side, using IItemManager. On the server side, you can use com.ibm.team.repository.service.IRepositoryItemService.fetchItemsPermissionAware(IItemHandle[], String[]) |
| edit: watch out for the code you copy !..
from https://jazz.net/library/article/1118 there are proper literal ids.. of course private static final String PENDING = "com.ibm.team.workitem.approvalState.pending"; private static final String REJECTED = "com.ibm.team.workitem.approvalState.rejected"; private static final String APPROVED = "com.ibm.team.workitem.approvalState.approved";working code <code> private List<IApproval> querySpecificApprovalsinProjectArea( IProjectAreaHandle ProjectArea, String ApprovalState) { // save space for the list of approvals found List<IApproval> selectedApprovals = new ArrayList<IApproval>(); // create the query model, aka workitems BaseWorkItemQueryModel workItems = BaseWorkItemQueryModel.WorkItemQueryModel.ROOT; // setup one condition of the query // approvals in pending state IStringField state = workItems.internalApprovalDescriptors().cumulativeStateIdentifier(); //.internalApprovals().stateIdentifier(); IPredicate ApprovalStateFilter = state._eq(ApprovalState); IItemQuery query = IItemQuery.FACTORY.newInstance((IItemQueryModel) workItems); // setup space for the project area args in the query IItemHandleInputArg[] itemHandleArgs = new IItemHandleInputArg[]{query.newItemHandleArg()}; // the second condition for the query, in the specified project area IPredicate projectAreaFilter = workItems.projectArea()._in(itemHandleArgs); // increment the counter for this approver // combine the conditions both satisfied IPredicate CombinedFilter =ApprovalStateFilter._and(projectAreaFilter); // set the filter on the query query.filter(CombinedFilter); try { // allocate space for the returned workitems List<IWorkItem> selectedWorkItems = new ArrayList<IWorkItem>(); // execute the query, passing in the projectarea specific info IQueryPage qpage = fQueryService.queryItems(query, new IProjectAreaHandle[]{ProjectArea}, IQueryService.ITEM_QUERY_MAX_PAGE_SIZE); do { // if there are some results on this page if(qpage.getSize()>0) { // loop thru all the returned workitems for(IWorkItem wi: (List<IWorkItem>)fItemService.fetchItemsPermissionAware((IItemHandle[]) (((IItemQueryPage) qpage).getItemHandles()).toArray(new IItemHandle[1]), new String[]{IWorkItem.APPROVALS_PROPERTY,IWorkItem.APPROVAL_DESCRIPTORS_PROPERTY}).getRetrievedItems()) { // get their approvals for(IApproval ap: wi.getApprovals().getContents()) { // only copy the pending state approvals // workitem might contain multiple if(ap.getStateIdentifier().equalsIgnoreCase(ApprovalState)) selectedApprovals.add(ap); } } } if(qpage.hasNext()) qpage = fQueryService.fetchPage(qpage.getToken(), qpage.getNextStartPosition(), IQueryService.ITEM_QUERY_MAX_PAGE_SIZE); else break; } while(true); } catch (TeamRepositoryException e) { // TODO Auto-generated catch block e.printStackTrace(); } return selectedApprovals; }
</code>
Comments So you got it working? yes, thank you very much!..
My pleasure, good to hear. FYI, this kind of majority vote type thing has been requested before. See the venerable Work item voting (22990) and its dups. My understanding is it's a pretty common thing in some change management methodologies, especially in the Systems space. |
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.