topic Re: Use QuerySpec to find latest revision in Windchill Customization

Use QuerySpec to find latest revision

TG_10272716 — Fri, 15 Mar 2024 14:05:23 GMT

Hello,

I am currently using a Query Spec to look for all WTParts that match a specific query (query for name or number).

I am also applying a SearchCondition with Iterated.LATEST_ITERATION = true to look for the latest iteration.

spec.appendWhere(new SearchCondition(WTPart.class, Iterated.LATEST_ITERATION, SearchCondition.IS_TRUE), new int[]{index});

I am now trying to expand the query by a latest revision filter since with the Iterated.LATEST_ITERATION = true it returns the latest iterations of all revisions but I only need the latest iteration of the latest revision of this part.

Using the Windchill Search I was able to do exaclty this but I need this as a Java Query. You can see the required functionality in this picture.

Best regards

Re: Use QuerySpec to find latest revision

avillanueva — Wed, 16 Nov 2022 12:45:54 GMT

How complex is your QuerySpec? Perhaps you can break it up a bit. If you have a WTPart, it can be passed to the VersionControlHelper class to get all versions. The method below will

QueryResult allVersionsFrom(Versioned version)
                     throws WTException,
                            PersistenceException

Finds all of the versions of a master from the first one created to the given version. The result is an ordered list of versions (i.e., latest iterations) from the given iteration to the first one created. For example, if version C is given, the list returned is C, B, A.

This can be done with WTPartMasters too:

allVersionsOf

QueryResult allVersionsOf(Mastered master)
                   throws WTException,
                          PersistenceException

Finds all of the versions of the given master. The result is an ordered list of versions (i.e., latest iterations) from the most recent one to the first one created, e.g. (B, A).

You just need to take the first one that returns and it will be the latest version.

Re: Use QuerySpec to find latest revision

HelesicPetr — Wed, 16 Nov 2022 14:05:51 GMT

Hello @TG_10272716

To query the object with latest revision and iteration you can use following query :

example works with WTParts

public static WTPart getPartLatestIterationBySubQuery(String partNumber) { Class targetClass = wt.part.WTPart.class; try { QuerySpec subSelect = new QuerySpec(); subSelect.getFromClause().setAliasPrefix("B"); //sub FROM int subIndex = subSelect.appendClassList(targetClass, false); int[] fromIndicies = {subIndex}; ClassAttribute branchIDAtt = new ClassAttribute(targetClass, WTPart.BRANCH_IDENTIFIER); // IMPORTANT: SUB SELECT has to have own new CLASS ATTRIBUTE SQLFunction maxFunction = SQLFunction.newSQLFunction(SQLFunction.MAXIMUM, branchIDAtt); subSelect.appendSelect(maxFunction, fromIndicies, false); //sub SELECT ClassAttribute numberAtt = new ClassAttribute(targetClass, WTPart.NUMBER); RelationalExpression theExpression = ConstantExpression.newExpression(partNumber, numberAtt.getColumnDescriptor().getJavaType()); SearchCondition theCondition = new SearchCondition(numberAtt, SearchCondition.EQUAL, theExpression); subSelect.appendWhere(theCondition); //sub WHERE //wt.query.ConditionsClause. QuerySpec select = new QuerySpec(); int index = select.appendClassList(targetClass, true); ClassAttribute brID = new ClassAttribute(targetClass, WTPart.BRANCH_IDENTIFIER); // IMPORTANT: SUB SELECT has to have own new CLASS ATTRIBUTE select.appendWhere(new SearchCondition(brID, SearchCondition.IN, new SubSelectExpression(subSelect)) , new int[]{index}); select.appendAnd(); ClassAttribute lastIter = new ClassAttribute(targetClass, WTPart.LATEST_ITERATION); // IMPORTANT: SUB SELECT has to have own new CLASS ATTRIBUTE select.appendWhere(new SearchCondition(lastIter, SearchCondition.IS_TRUE)); select.setAdvancedQueryEnabled(true);//velmi dulezite nastavit advanced QueryResult qr = PersistenceServerHelper.manager.query((StatementSpec) select); while (qr.hasMoreElements()) { Object[] nextObj = (Object[]) qr.nextElement(); return (WTPart) nextObj[0]; } } catch (WTPropertyVetoException e) { e.printStackTrace(); } catch (PersistenceException e) { e.printStackTrace(); } catch (QueryException e) { e.printStackTrace(); } catch (WTException e) { e.printStackTrace(); } return null; }

This code is little bit faster then control all revisions one by one.

Hope this can help.

PetrH

Re: Use QuerySpec to find latest revision

TG_10272716 — Mon, 21 Nov 2022 11:56:37 GMT

Thank you for your reply @avillanueva.

The problem is that i somehow need to include this in the database query because i also specify a query limit and this way I would query for lets say 50 parts which is my current query limit, but then I only take 10 of the parts because all other are not the newest version and then the query limit would not make sense.

Re: Use QuerySpec to find latest revision

TG_10272716 — Mon, 21 Nov 2022 12:44:07 GMT

Thank you @HelesicPetr, your query does exactly what I need, not I will try to merge it with my query to make it work. Thank you!

Re: Use QuerySpec to find latest revision

TG_10272716 — Mon, 21 Nov 2022 14:21:23 GMT

Hello @HelesicPetr,

while implementing my search conditions i realized that the code posted by you only returns one part of each type - in my case this returns a maximum of one article and one manufacturer part. However I am using a SearchCondition.LIKE with "%"+name+"%" to query for all matches of the name. I tried to figure it out but it won't work.

In this case it should query for all parts that have a match with the search query and return only the latest version (like in your code but for all matches)

Is there a way to do it, is this only a small change to your code or does this require a totally different structure.

Best regards

Re: Use QuerySpec to find latest revision

HelesicPetr — Mon, 21 Nov 2022 15:42:30 GMT

Hi @TG_10272716

The select works only for specific one number.

If you need to use LIKE the query needs to be rewritten.

PetrH

Re: Use QuerySpec to find latest revision

TG_10272716 — Wed, 23 Nov 2022 06:17:24 GMT

Hello @HelesicPetr

could you give me a hint how to do this?

Best regards

Re: Use QuerySpec to find latest revision

HelesicPetr — Wed, 23 Nov 2022 07:10:35 GMT

Hi @TG_10272716

The hint is that you need to rebuild the condition with number (or name whateever). It should looks like as you create subselect in a query builder to filter latest revision.

Now the number condition is in the subselect and search for max value of the BRANCH_IDENTIFIER and the given number together so only one row is returned.

You need to define the condition with number in normal select and set condition for subselect where WTPARTnumber from select equal WTPARTnumber from SUBselect.

I know generally how to create select in database, but it is complicated to rewrite it to QuerySpec because it is not documented.

Sorry for that. If I had more time to help you I would.

My example of QuerySpec is created based on following select

select * from wcuser.WTPart A0 where A0.latestiterationInfo=1 and A0.branchIditerationInfo in (SELECT MAX(B0.branchIditerationInfo) as branchID FROM wcuser.WTPart B0 INNER JOIN wcuser.WTPartMaster B0B ON (B0.idA3masterReference = B0B.idA2A2) WHERE ((B0B.WTPartNumber LIKE N'MV%')))

Example of final select what you need

This select should be rewrite to QuerySpec.

SELECT A0B.WTPartNumber FROM wcuser.WTPart A0 INNER JOIN wcuser.WTPartMaster A0B ON (A0.idA3masterReference = A0B.idA2A2) WHERE ((A0.latestiterationInfo = 1) AND A0.versionSortIdA2versionInfo IN (SELECT MAX(AA0.versionSortIdA2versionInfo) FROM wcuser.WTPart AA0 INNER JOIN wcuser.WTPartMaster AA0B ON (AA0.idA3masterReference = AA0B.idA2A2) WHERE ((AA0B.WTPartNumber = A0B.WTPartNumber)))) and A0B.WTPartNumber like N'MV%'

PS: in the last sql select example I use versionSortIdA2versionInfo instead of BRANCH_IDENTIFIER

PetrH

Re: Use QuerySpec to find latest revision

TG_10272716 — Wed, 23 Nov 2022 07:33:53 GMT

Hello @HelesicPetr,

thank you for your help, I would not have been able to solve this without you, I was able to achieve the desired result by rewriting the search condition for number and adding one for name and using a group by on NAME.

Best regards