AdvancedCriteria IN_SET operator not working.

Smart GWT Technical Q&A
1

I have a data source with a custom <selectClause> in the ds.xml file. I’m using DataSource.fetchData and passing it an AdvancedCriteria object that I’ve created with this code.

AdvancedCriteria selectionCriteria = new AdvancedCriteria("ISKU", OperatorId.IN_SET);
ArrayList<Integer> skus = new ArrayList<Integer>();
for (ListGridRecord rec : itemGrid.getSelection()) {
	skus.add(rec.getAttributeAsInt("ISKU"));
}
Integer ia[] = new Integer[skus.size()];
selectionCriteria.addCriteria("ISKU", skus.toArray(ia));

The SQL statement that gets logged to the console shows the SELECT statement with WHERE (‘0’=‘1’) instead of the criteria I passed it. Here is the DSRequest that shows up in the developer console.

{
    dataSource:"ItemSummary", 
    operationType:"fetch", 
    data:{
        fieldName:"ISKU", 
        operator:"inSet", 
        ISKU:[
            21949, 
            21956
        ]
    }, 
    showPrompt:true, 
    requestId:"ItemSummary$62721", 
    bypassCache:true, 
    oldValues:{
        fieldName:"ISKU", 
        operator:"inSet", 
        ISKU:[
            21949, 
            21956
        ]
    }
}

I was expecting to see a WHERE ISKU IN (21949, 21956). If I change the criteria to be a simple ISKU equals 21949 condition it works. Is there a problem with the IN_SET operator?

2

Specify the set of values as the “value” property, don’t create a property named after the fieldName.

3

I don’t understand. The only available constructors for AdvancedCriteria require a field name. addCriteria is the only method I could find that accepts an array for the “value” property. Do you mean I need to format a string like a SQL IN clause and send that as the value String on the constructor? I’ve tried that, passing a third String parameter for value on the constructor and formatting the String as “(21949, 21956)”, or “21949, 21956” or [21949, 21956], or {21949, 21956}. None of them worked.

Can you provide an example or point me to any documentation that explains more about how AdvancedCriteria is supposed to work for the OperatorIds like IN_SET, BETWEEN, etc. that take more than a single “value”?

4

Replace your call to addCriteria() with a call to setAtrribute(“value”, list)

5

I’ve done that …

selectionCriteria.setAttribute(“value”, skus);

And now I get a javascript error on the call to fetchData.

com.google.gwt.core.client.JavaScriptException: (null): null

I also tried sending it as an array and got the same thing

selectionCriteria.setAttribute(“value”, skus.toArray(ia));

6

So it appears that “list” in your example has to be a String array. Using an Integer results in the null js error.

7

Thanks, we’ll take a look at why Integers would be a problem, but Strings will work for your use case.

8

An alternative to the IN_SET when using a trivial criteria is to use only a Criteria object (not an AdvancedCriteria). Re-using the original code for comparison:

Criteria criteria = new Criteria();
ArrayList<Integer> skus = new ArrayList<Integer>();
for (ListGridRecord rec : itemGrid.getSelection()) {
	skus.add(rec.getAttributeAsInt("ISKU"));
}
Integer ia[] = new Integer[skus.size()];
selectionCriteria.addCriteria("ISKU", skus.toArray(ia));

(I’m posting this in hopes it helps someone else. I didn’t see much other documentation about the IN_SET operator and this seemed to do what I was really wanting to do.)