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

Need to query the WTDocument from the particular Organization

NG_9451104
7-Bedrock

Need to query the WTDocument from the particular Organization

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);
}

8 REPLIES 8

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 @NG_9451104 

Does it help?

PetrH

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

NG_9451104_2-1653491969162.png

 

NG_9451104_1-1653491867402.png

 

Hi @NG_9451104 

 

Try to use Like for OrgName instead of equal.

 

PetrH

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')&amp;(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>

 

 

 

 

 

 

PREVIEW
 
 
 
Announcements