Community Tip - You can subscribe to a forum, label or individual post and receive email notifications when someone posts a new topic or reply. Learn more! X
Need to query the WTDocument from the particular Organization, from WTDocument.class not from WTDocumentMaster.class.
If I use WTDoumentMaster.class this is working but I need for WTDocument.class
I've tried the code But having some issues, kindly let us know if any suggestions.
exampe:
Class Doc = WTDocument.class;
Class ORG= WTOrganization.class;
QuerySpec qs = new QuerySpec();
int DOC_idx = qs.addClassList(Doc , true);
int ORG_idx1 = qs.addClassList(ORG, true);
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date d1 = format.parse(screatedDateFrom);
java.util.Date d2 = format.parse(screatedDateTo);
Date dF = new Date(d1.getTime());
Date dT = new Date(d2.getTime());
Timestamp tsFrom = new Timestamp(d1.getTime());
tsFrom.setHours(23);
Timestamp tsTo = new Timestamp(d2.getTime());
tsTo.setHours(23);
SearchCondition sc0 = new SearchCondition(ORG, "name", SearchCondition.EQUAL, "ORGNAME1");
SearchCondition sc00 = new SearchCondition(Doc, "organizationReference.key.id", qc1, "thePersistInfo.theObjectIdentifier.id");
SearchCondition sc1 = new SearchCondition(Doc,"thePersistInfo.createStamp", SearchCondition.GREATER_THAN_OR_EQUAL , tsFrom);
SearchCondition sc2 = new SearchCondition(Doc,"thePersistInfo.createStamp", SearchCondition.LESS_THAN_OR_EQUAL, tsTo);
qs.appendOpenParen();
qs.appendWhere(sc0, new int []{ORG_idx1});
qs.appendCloseParen();
qs.appendAnd();
qs.appendWhere(sc00, new int []{DOC_idx, ORG_idx1});
qs.appendAnd();
qs.appendWhere(sc1, new int[] { DOC_idx });
qs.appendAnd();
qs.appendWhere(sc2, new int[] { DCO_idx });
QueryResult qr = PersistenceHelper.manager.find((StatementSpec) qs);
WTDocument Document = null;
while (qr.hasMoreElements())
{
Persistable[] ar = (Persistable[]) qr.nextElement();
Document = (WTDocument) ar[idx];
doclist.add(Document);
}
Hello @NG_9451104
build a Query Specification is tricky.
Sometimes you have to use a some workaround to get you really need.
You have to define a relation between tables. In this case it is from WDocument to WTDocumentMaster and then to WTOrganization
As I know there are some limitations if you want to define direct Column names you should define aliases to get right column information from database
(or error is presented Example: Attribute "masterReference" is not a member of class "class wt.doc.WTDocument")
Following query spec should work for you.
QuerySpec queryspec;
queryspec = new QuerySpec();
queryspec.setAdvancedQueryEnabled(true);
int indexObjectWTD = queryspec.appendClassList(WTDocument.class, true);
int indexObjectMASTR = queryspec.appendClassList(WTDocumentMaster.class, true);
int indexObjectORG = queryspec.appendClassList(WTOrganization.class, true);
String orgName = "AVENG";
//alisID (A0,A1,A2 ... atc)
String[] aliases = new String[3];
aliases[0] = queryspec.getFromClause().getAliasAt(indexObjectWTD); // alias for WTDOCUMENT
aliases[1] = queryspec.getFromClause().getAliasAt(indexObjectMASTR); // ALIAS for MASTER WTDOcument
aliases[2] = queryspec.getFromClause().getAliasAt(indexObjectORG); // ALIAS for ORG
//relation settings
CompositeWhereExpression andExpression = new CompositeWhereExpression(LogicalOperator.AND);
// (A0.idA3masterReference=A1.idA2A2) and (A1.idA3organizationReference=A2.idA2A2)
andExpression.append(new SearchCondition(new TableColumn(aliases[0], "idA3masterReference"), "=", new TableColumn(aliases[1], "idA2A2")));
andExpression.append(new SearchCondition(new TableColumn(aliases[1], "idA3organizationReference"), "=", new TableColumn(aliases[2], "idA2A2")));
queryspec.appendWhere(andExpression,new int[]{indexObjectWTD,indexObjectMASTR,indexObjectORG});
SearchCondition sc0 = new SearchCondition(WTOrganization.class, "name", SearchCondition.EQUAL, orgName); // HERE is a NAME of your ORGANIZATION
SearchCondition sc1 = new SearchCondition(WTDocument.class,"thePersistInfo.createStamp", SearchCondition.GREATER_THAN_OR_EQUAL , newTimeStamp); // PUT YOUR TIME STAMP HERE
SearchCondition sc2 = new SearchCondition(WTDocument.class,"thePersistInfo.createStamp", SearchCondition.LESS_THAN_OR_EQUAL, newTimeStamp2); // PUT YOUR TIME STAMP HERE
queryspec.appendAnd();
queryspec.appendWhere(sc0, new int[]{indexObjectORG});
queryspec.appendAnd();
queryspec.appendWhere(sc1, new int[]{indexObjectWTD});
queryspec.appendAnd();
queryspec.appendWhere(sc2, new int[]{indexObjectWTD});
QueryResult queryRes = PersistenceServerHelper.manager.query(queryspec);
Hope this can help
PetrH
Hi PetrH,
Thanks for sharing this let me check this and let you know the result.
Once again thanks.
Thanks and Regards,
Naveen Infant George
Hi PetrH,
Sorry for the delay I'm on vacation till 22nd May, I'll update you on 23rd May,
Thanks,
Naveen Infant George
Hi
Sorry for the delay,
I've tried your suggestion but I can't get the objects count, for the same date if I search in UI there is an object available. Maybe I missed something. and let you know
Hi @NG_9451104
I guess the time format could case wrong results.
How do you create Timestamp object?
I see an output with just date not time.
If I use following definition for timestamp it gives me correct results. (all documents :D)
Timestamp newTimeStamp = new Timestamp(Long.parseLong("0"));
Timestamp newTimeStamp2 = new Timestamp(System.currentTimeMillis());
PetrH
Hi HelesicPetr,
Sorry for the delay just stuck in some other projects now i have tried these lines but it is not works for me maybe i missed something,
Now I tried the same using webject and it is now working fine, Thanks for the support and once again sorry for the delay.
<ie:webject name="Query-Objects" type="OBJ">
<ie:param name="INSTANCE" data="$(properties[0]wt.federation.ie.VMName[0])" />
<ie:param name="TYPE" data="wt.inf.container.WTContainer" />
<ie:param name="ATTRIBUTE" data="name" />
<ie:param name="WHERE" data="name='<%=getParam("orgname")%>'"/>
<ie:param name="GROUP_OUT" data="container" />
</ie:webject>
<ie:webject name="Query-Objects" type="OBJ">
<ie:param name="INSTANCE" data="$(properties[0]wt.federation.ie.VMName[0])"/>
<ie:param name="type" data="wt.doc.WTDocument"/>
<ie:param name="ATTRIBUTE_TYPE_CONTEXT" data="wt.fc.Persistable"/>
<ie:param name="CONTAINER_REF" data="$(container[]obid[])" />
<ie:param name="where" data="(thePersistInfo.modifyStamp>='2021-04-01')&(thePersistInfo.modifyStamp<='2021-08-30')"/>
<ie:param name="where" data="(creator.name='workwolk')" delim=","/>
<ie:param name="attribute" data="number,name,version,lifeCycleState,creator.name,thePersistInfo.modifyStamp" delim=","/>
<ie:param name="group_out" data="All-Versions"/>
</ie:webject>