Skip to main content
N
NG_945110410-Marble
10-Marble
May 6, 2022
Question

Need to query the WTDocument from the particular Organization

  • May 6, 2022
  • 1 reply
  • 3194 views

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

1 reply

HelesicPetr
22-Sapphire II
HelesicPetr22-Sapphire II
22-Sapphire II
May 9, 2022

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

N
NG_945110410-MarbleAuthor
10-Marble
May 9, 2022

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

    HelesicPetr
    22-Sapphire II
    HelesicPetr22-Sapphire II
    22-Sapphire II
    May 13, 2022

    Hi @NG_9451104 

    Does it help?

    PetrH

    Terms & ConditionsAccessibility statement