cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Use QuerySpec to find latest revision

FS_10272716
7-Bedrock

Use QuerySpec to find latest revision

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hello @FS_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  

View solution in original post

9 REPLIES 9

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.

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.

Hello @FS_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  

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!

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

Hi @FS_10272716 

 

The select works only for specific one number.

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

 

PetrH 

 

 

Hello @HelesicPetr 

 

could you give me a hint how to do this?

 

Best regards

Hi @FS_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 

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

Announcements