QuerySpec that returns multiple WTPartMaster based on Number and Organization name

[AndersKullenber]

Hi,

Need help with defining a QuerySpec. In pure SQL I would define it as.

select * from WTPARTMASTER PM
JOIN WTORGANIZATION O ON O.IDA2A2 = PM.IDA3ORGANIZATIONREFERENCE
WHERE concat(PM.WTPARTNUMBER, O.NAME) IN ('0000000081Demo Organization','0000000081Other Organization'.......);

I need to be able to search for hundreds of parts in one go, that's why I use the WHERE IN. (ArrayExpression in the QuerySpec) And since I need both the Number and the Org name I concat them, otherwise I cannot use the IN clause.

Any ideas are welcome.

BR

Anders

[fpradier-2]

I guess you already found something for your query, but it can help other people

You have both WTPartMaster and WTOrganization in same result ; organization list is split into individual search criteria grouped by OR.

You could also use organizationName from WTPartMaster if you just use organization name as a filtering criteria ; also with parenthesis and OR condition

[...]

String org_list = "a comma separated list of organization names";

try {
QuerySpec qs = new QuerySpec();
int ind_WTPartMaster = qs.appendClassList(wt.part.WTPartMaster.class, true);
int ind_WTOrganization = qs.appendClassList(wt.org.WTOrganization.class, true);

qs.appendOpenParen();

int cnt = 0;
StringTokenizer st = new StringTokenizer(org_list, ",");
while (st.hasMoreTokens()) {
org_name = st.nextToken();
if (cnt > 0) {
qs.appendOr();
}
SearchCondition sc_org=new SearchCondition(WTOrganization.class, "name", SearchCondition.EQUAL, org_name);
qs.appendWhere(sc_org, new int []{ind_WTOrganization});
cnt++;
}
qs.appendCloseParen();

SearchCondition sc_master=new SearchCondition(WTPartMaster.class, "organizationReference.key.id", WTOrganization.class, "thePersistInfo.theObjectIdentifier.id");
qs.appendAnd();
qs.appendWhere(sc_master,new int []{ind_WTPartMaster,ind_WTOrganization});

QueryResult qr = PersistenceHelper.manager.find(qs);

while (qr != null && qr.hasMoreElements()) {
Object[] o1 = (Object[])qr.nextElement();
Persistable ob = (Persistable)o1[0];
System.out.println("ob: " + ob);
System.out.println("ob.class: " + ob.getClass().getName());
if (ob instanceof wt.part.WTPartMaster) {
WTPartMaster wtpm = (WTPartMaster)ob;
System.out.println(" wtpm: " + wtpm.getDisplayIdentifier());
}

Persistable ob2 = (Persistable)o1[1];
System.out.println("ob2: " + ob2);
System.out.println("ob2.class: " + ob2.getClass().getName());
if (ob2 instanceof wt.org.WTOrganization) {
WTOrganization wtorg = (WTOrganization)ob2;
System.out.println(" wtorg: " + wtorg.getName());
}
}
}

catch (Exception ex) {
ex.printStackTrace();
}

[...]