11.1p: ListGrid Client side filtering with SelectItem with multiple:true is using substring instead of equals for IDs

[Blama]

Hi Isomorphic,

please see this video (v11.1p_2018-02-16) where client side filtering brings the wrong result and server side filtering after an invalidateCache() corrects the issue.
This only happens when the filterRow-SelectItem is setMultiple(true):



As you can see in the filter dropdown, Kirill Amirov has a "4" in the employeeId, so these rows are also shown, because the client side filtering has a bug here.

BuiltInDS.java:

package com.smartgwt.sample.client;

import com.google.gwt.core.client.EntryPoint;
import com.smartgwt.client.Version;
import com.smartgwt.client.core.KeyIdentifier;
import com.smartgwt.client.data.DataSource;
import com.smartgwt.client.data.SortSpecifier;
import com.smartgwt.client.types.Autofit;
import com.smartgwt.client.types.SortDirection;
import com.smartgwt.client.util.Page;
import com.smartgwt.client.util.PageKeyHandler;
import com.smartgwt.client.util.SC;
import com.smartgwt.client.widgets.IButton;
import com.smartgwt.client.widgets.Window;
import com.smartgwt.client.widgets.events.ClickEvent;
import com.smartgwt.client.widgets.events.ClickHandler;
import com.smartgwt.client.widgets.form.fields.ComboBoxItem;
import com.smartgwt.client.widgets.form.fields.SelectItem;
import com.smartgwt.client.widgets.grid.ListGrid;
import com.smartgwt.client.widgets.grid.ListGridField;
import com.smartgwt.client.widgets.layout.VLayout;

public class BuiltInDS implements EntryPoint {
    private VLayout mainLayout;
    private IButton recreateBtn;

    public void onModuleLoad() {
        KeyIdentifier debugKey = new KeyIdentifier();
        debugKey.setCtrlKey(true);
        debugKey.setKeyName("D");

        Page.registerKey(debugKey, new PageKeyHandler() {
            public void execute(String keyName) {
                SC.showConsole();
            }
        });

        mainLayout = new VLayout(20);
        mainLayout.setWidth100();
        mainLayout.setHeight100();

        recreateBtn = new IButton("Recreate");
        recreateBtn.addClickHandler(new ClickHandler() {
            @Override
            public void onClick(ClickEvent event) {
                recreate();
            }
        });
        mainLayout.addMember(recreateBtn);
        recreate();
        mainLayout.draw();
    }

    private void recreate() {
        Window w = new Window();
        w.setWidth("20%");
        w.setHeight("40%");
        w.setMembersMargin(0);
        w.setModalMaskOpacity(70);
        w.setTitle(" (" + Version.getVersion() + "/" + Version.getSCVersionNumber() + ")");
        w.setTitle("Filtering with Multiple SelectItem is using substring for IDs" + w.getTitle());
        w.setShowMinimizeButton(false);
        w.setIsModal(true);
        w.setShowModalMask(true);
        w.centerInPage();

        final ListGrid employeesGrid = new ListGrid(DataSource.get("employees"));
        employeesGrid.setAutoFitData(Autofit.VERTICAL);
        employeesGrid.setAutoFetchData(false);
        employeesGrid.setShowFilterEditor(true);
        employeesGrid.setFilterOnKeypress(true);

        ListGridField employeeId = new ListGridField("EmployeeId");
        employeeId.setCanEdit(false);

        ListGridField name = new ListGridField("Name");
        name.setCanEdit(false);

        ListGridField gender = new ListGridField("Gender");

        ListGridField reportsTo = new ListGridFieldReportsTo("ReportsTo");

        ListGridField job = new ListGridField("Job");

        employeesGrid.setFields(employeeId, name, gender, reportsTo, job);
        employeesGrid.setSort(new SortSpecifier[] { new SortSpecifier(name.getName(), SortDirection.ASCENDING) });
        employeesGrid.setFetchOperation("fetchA");
        employeesGrid.fetchData();
        w.addItem(employeesGrid);

        IButton reloadBtn = new IButton("Reload");
        reloadBtn.addClickHandler(new ClickHandler() {
            @Override
            public void onClick(ClickEvent event) {
                employeesGrid.invalidateCache();
            }
        });
        w.addItem(reloadBtn);
        w.show();
    }

    private class ListGridFieldReportsTo extends ListGridField {
        public ListGridFieldReportsTo(String name) {
            super(name);
            {
                ComboBoxItem managerCBI = new ComboBoxItem();
                managerCBI.setOptionDataSource(DataSource.get("employees"));
                managerCBI.setValueField(DataSource.get("employees").getPrimaryKeyFieldName());
                managerCBI.setDisplayField("Name");
                managerCBI.setPickListSort(new SortSpecifier[] { new SortSpecifier("EmployeeId", SortDirection.ASCENDING),
                        new SortSpecifier("Name", SortDirection.ASCENDING) });
                ListGridField managerCBI_empIdLGF = new ListGridField("EmployeeId");
                ListGridField managerCBI_nameLGF = new ListGridField("Name");
                managerCBI.setPickListFields(managerCBI_empIdLGF, managerCBI_nameLGF);
                managerCBI.setPickListHeaderHeight(0);
                setEditorProperties(managerCBI);
            }
            {
                SelectItem managerCBI = new SelectItem();
                managerCBI.setOptionDataSource(DataSource.get("employees"));
                managerCBI.setValueField(DataSource.get("employees").getPrimaryKeyFieldName());
                managerCBI.setDisplayField("Name");
                managerCBI.setPickListSort(new SortSpecifier[] { new SortSpecifier("EmployeeId", SortDirection.ASCENDING),
                        new SortSpecifier("Name", SortDirection.ASCENDING) });
                ListGridField managerCBI_empIdLGF = new ListGridField("EmployeeId");
                ListGridField managerCBI_nameLGF = new ListGridField("Name");
                managerCBI.setPickListFields(managerCBI_empIdLGF, managerCBI_nameLGF);
                managerCBI.setPickListHeaderHeight(0);
[B]               setMultiple(true);[/B]
                setFilterEditorProperties(managerCBI);
            }
        }
    }
}

employees.ds.xml:

<DataSource
    ID="employees"
    serverType="sql"
    tableName="employeeTable"
    recordName="employee"
    testFileName="/examples/shared/ds/test_data/employees.data.xml"
    titleField="Name"
    useAnsiJoins="true"
>
    <fields>
        <field name="userOrder"       title="userOrder"       type="integer"  canEdit="false"    hidden="true"/>
        <field name="Name"            title="Name"            type="text"     length="128"/>
        <field name="EmployeeId"      title="Employee ID"     type="integer"  primaryKey="true"  required="true"/>
[B]       <field name="ReportsTo"       title="Manager"         type="integer"  foreignKey="employees.EmployeeId" displayField="ReportsToName" joinType="outer" />
        <field name="ReportsToName"   includeFrom="employees.Name" />[/B]
        <field name="Job"             title="Title"           type="text"     length="128"/>
        <field name="Email"           title="Email"           type="text"     length="128"/>
        <field name="EmployeeType"    title="Employee Type"   type="text"     length="40"/>
        <field name="EmployeeStatus"  title="Status"          type="text"     length="40"/>
        <field name="Salary"          title="Salary"          type="float"/>
        <field name="OrgUnit"         title="Org Unit"        type="text"     length="128"/>
        <field name="Gender"          title="Gender"          type="text"     length="7">
            <valueMap>
                <value>male</value>
                <value>female</value>
            </valueMap>
        </field>
        <field name="MaritalStatus"   title="Marital Status"  type="text"     length="10">
            <valueMap>
                <value>married</value>
                <value>single</value>
            </valueMap>
        </field>
    </fields>
[B]   <operationBindings>
        <operationBinding operationType="fetch" operationId="fetchA">
            <criteria fieldName="Name" operator="startsWith" value="A" />
        </operationBinding>
    </operationBindings>[/B]
</DataSource>

Please note that this does not happen when applying the "name startsWith A" filter on the client side as there for some reason client side filtering is not used even though all rows are loaded. I'll open a different thread for this one.

This does also happen in my application.

Best regards
Blama

[Isomorphic]

It doesn't look like you make any attempt to set a search operator on the field, so the default search operator is used.

You may be hoping that "equals" would be automatically chosen just because the field happens to be a foreignKey. This isn't done in this and similar circumstances because it would force AdvancedCriteria to be used, and lots of server backends can't support this, so we require an explicit configuration to use it.

[Blama]

Hi Isomorphic,

this does not explain the difference after invalidateCache(), where in SQL IN (....), which translates to many equals-criteria, is used.

Otherwise, the SQL needed to be "OR x like '%value1%' OR x like '%value2%'".

So there is clearly some mismatch.

Best regards
Blama

[Blama]

Also, as written, this only happens when the filterRow-SelectItem is setMultiple(true):

[Blama]

Hi Isomorphic,

actually, the criteria are indeed generated as OR... OR... when clicking many entries in the filter-SelectItem. I did not notice this before.

With setFilterOperator(OperatorId.EQUALS) I also do get the expected result for clientside filtering and server requests after invalidateCache().

So the only issue is with the different results for client/server filtering in the original sample where the server uses equals and the client uses contains or iContains. This again only happens with setMultiple(true).

In the original sample without setMultiple(true) it is like this: equals on client and server, which is what I would expect, but is contrary to your 2nd sentence here:

You may be hoping that "equals" would be automatically chosen just because the field happens to be a foreignKey. This isn't done in this and similar circumstances because it would force AdvancedCriteria to be used, and lots of server backends can't support this, so we require an explicit configuration to use it.

Best regards
Blama

[Isomorphic]

In each of your test cases the server request is always a simple criteria, however, when you add the operationBinding criteria for the server query you are forcing the server to coerce the simple criteria into an advanced criteria to match the operationBinding criteria type. When the filter value is an integer the operator chosen is just equals. When the filter value is an array the chose operator is also equals.

On the client a simple criteria is always used for you samples so the textMatchStyle determines the filter operator. If this simple criteria was converted to an advancedCriteria on the client you would see the same equals operator used.

So, by setting the filterOperator explicitly you trigger an advancedCriteria everywhere and get consistent results.

[Blama]

Hi Isomorphic,

understood for the server part.

For the client part, this still does IMHO not explain the difference between with and without setMultiple(true). Here it is equals with multiple:false and contains/iContains with multiple:true.
textMatchStyle is unchanged but the results differ.

This is now very minor for me, as it is working in my application with just setting filterOperator explicitly.

Best regards
Blama

[Isomorphic]

There is one more client-side check that applies when multiple:false: if both values are not strings then the operator is automatically changed to equals.

[Blama]

Hi Isomorphic,

now everything is sorted out :)

As I said, everything fine for me as just setting filterOperator soves my problem.

Thanks for the explanations & Best regards
Blama