Skip to main content
T
TG_1027271612-Amethyst
12-Amethyst
November 16, 2022
Solved

Use QuerySpec to find latest revision

  • November 16, 2022
  • 2 replies
  • 5987 views

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.

FS_10272716_0-1668600800100.png

 

Best regards

 

 

 

Best answer by HelesicPetr

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  

2 replies

avillanueva
23-Emerald I
avillanueva23-Emerald I
23-Emerald I
November 16, 2022

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.
    T
    TG_1027271612-AmethystAuthor
    12-Amethyst
    November 21, 2022

    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
    22-Sapphire II
    HelesicPetr22-Sapphire IIAnswer
    22-Sapphire II
    November 16, 2022

    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  

      T
      TG_1027271612-AmethystAuthor
      12-Amethyst
      November 21, 2022

      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!

      Terms & ConditionsAccessibility statement