Use QuerySpec to find latest revision

[TG_10272716]

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

[avillanueva]

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.

[TG_10272716]

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.

[HelesicPetr · Accepted solution]

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  

[TG_10272716]

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!

[TG_10272716]

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

[HelesicPetr]

Hi @TG_10272716 

The select works only for specific one number.

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

PetrH 

[TG_10272716]

Hello @HelesicPetr 

could you give me a hint how to do this?

Best regards

[HelesicPetr]

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 

[TG_10272716]

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