.ds.xml sortByField not working correctly on load

[Blama]

Hi Isomorphic,

please see this testcase (v10.1p_2017-08-03). I have the problem here that for me (unfortunately NOT in the testcase) I have my rows initially not sorted as expected.
But I DO see a reason why this could be happening, also with this testcase: The SQL order by-clause generated is not as expected (does not use sortByField, but displayField). See below.

Prerequisites:
Change animals lifeSpan as follows (needed for join-partners):


animals.ds.xml:

<DataSource ID="animals" xmlns="lmscompany/ds" serverType="sql" tableName="animals" testFileName="animals.data.xml" useAnsiJoins="true">
    <fields>
        <field name="commonName" title="Animal" type="text" sortByField="employeeSalary" />
        <field name="scientificName" title="Scientific Name" type="text" primaryKey="true" required="true" />

        <field foreignKey="employees.EmployeeId" name="lifeSpan" displayField="employeeName" sortByField="employeeSalary" relatedTableAlias="employees1"
            type="integer" required="true" />
        <field name="employeeName" includeFrom="employees.Name" includeVia="lifeSpan" />
        <field name="employeeSalary" includeFrom="employees.Salary" includeVia="lifeSpan" />

        <field foreignKey="employees.EmployeeId" name="lifeSpan2" displayField="employeeName2" sortByField="employeeSalary2" relatedTableAlias="employees2"
            type="integer" required="true" customSelectExpression="animals.lifeSpan" />

        <field name="employeeName2" includeFrom="employees.Name" includeVia="lifeSpan2" />
        <field name="employeeSalary2" includeFrom="employees.Salary" includeVia="lifeSpan2" />

        <field name="realLifeSpan" type="integer" required="true" customSelectExpression="animals.lifeSpan" />
        <field name="realSalary" type="integer" required="true" customSelectExpression="employees1.Salary" />

        <field name="status" title="Endangered Status" type="text">
            <valueMap>
                <value>Threatened</value>
                <value>Endangered</value>
                <value>Not Endangered</value>
                <value>Not currently listed</value>
                <value>May become threatened</value>
                <value>Protected</value>
            </valueMap>
        </field>
        <field name="diet" title="Diet" type="text" />
        <field name="information" title="Interesting Facts" type="text" length="1000" />
        <field name="picture" title="Picture" type="image" detail="true" imageURLPrefix="/isomorphic/system/reference/inlineExamples/tiles/images/" />
    </fields>
</DataSource>

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.AdvancedCriteria;
import com.smartgwt.client.data.Criterion;
import com.smartgwt.client.data.DataSource;
import com.smartgwt.client.data.Hilite;
import com.smartgwt.client.data.SortSpecifier;
import com.smartgwt.client.types.AutoFitWidthApproach;
import com.smartgwt.client.types.OperatorId;
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.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();
            }
        });
        setListGridDefaultApperance();

        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("95%");
        w.setHeight("95%");
        w.setMembersMargin(0);
        w.setModalMaskOpacity(70);
        w.setTitle(" (" + Version.getVersion() + "/" + Version.getSCVersionNumber() + ")");
        w.setTitle(".ds.xml sortByField not working on load" + w.getTitle());
        w.setShowMinimizeButton(false);
        w.setIsModal(true);
        w.setShowModalMask(true);
        w.centerInPage();

        final ListGrid animalsLG = new ListGrid() {
            {
                setDataSource(DataSource.get("animals"));
                setCanEdit(true);
                setCanGroupBy(false);
                setAutoFetchData(false);
                setCanRemoveRecords(true);

                ListGridField lifeSpan = new ListGridFieldLifeSpan("lifeSpan");
                ListGridField lifeSpan2 = new ListGridFieldLifeSpan("lifeSpan2");
                ListGridField commonName = new ListGridField("commonName");
                ListGridField status = new ListGridField("status");
                ListGridField realLifeSpan = new ListGridField("realLifeSpan");
                ListGridField realSalary = new ListGridField("realSalary");

                setSort(new SortSpecifier[] { new SortSpecifier(lifeSpan.getName(), SortDirection.ASCENDING),
                        new SortSpecifier(lifeSpan2.getName(), SortDirection.ASCENDING) });

                setHilites(new Hilite[] { new Hilite() {
                    {
                        setFieldNames("realSalary");
                        setTextColor("#d0d0d0");
                        setCriteria(
                                new AdvancedCriteria(OperatorId.AND, new Criterion[] { new Criterion("realSalary", OperatorId.LESS_OR_EQUAL, 10000) }));
                    }
                } });

                setFields(lifeSpan, lifeSpan2, commonName, status, realLifeSpan, realSalary);
                fetchData();
            }
        };
        w.addItem(animalsLG);
        w.show();
    }

    private static void setListGridDefaultApperance() {
        ListGrid lg = new ListGrid() {
            {
                setAutoFitFieldsFillViewport(true);
                setShowClippedValuesOnHover(true);
                setAutoFitWidthApproach(AutoFitWidthApproach.BOTH);
                setCanMultiGroup(false);
                setCanEdit(false);
                setShowFilterEditor(false);
                setCanFreezeFields(false);
                setCanReorderFields(false);
                setCellHeight(30);
                setHeaderHeight(35);
                setFixedRecordHeights(true);
                setGroupIcon("[SKINIMG]LT/group/group.png");
                setGroupIconSize(30);
                setGroupLeadingIndent(0);
                setHoverDelay(300);
                setHoverWidth(300);
            }
        };
        ListGrid.setDefaultProperties(lg);
    }

    private class ListGridFieldLifeSpan extends ListGridField {
        public ListGridFieldLifeSpan(final String name) {
            super(name);

            SelectItem mySI = new SelectItem();
            mySI.setOptionDataSource(DataSource.get("employees"));
            mySI.setDisplayField("Name");
            mySI.setSortField("Salary");
            setEditorProperties(mySI);
            setFilterEditorProperties(mySI);
        }
    }
}

Besides the animals PK being text, this matches 1:1 my setting. On load I see this (OK here):


As you can see, the data is sorted as expected (by lifeSpan(=employeeSalary), lifeSpan2(=employeeSalary2)). You can easily see that by the "Real Salary" column I included for debug reasons.
In my application, the data is sorted by the displayed name, so alphabetically by employeeName, employeeName2.

I don't know why I don't reproduce here, I really set everything as in my application. Also, the data volume is not very different (so no paging in either my Application (30 rows) or here (5 rows)).
The one thing I noticed which is definitely a bug is this:
The SQL generated is:

SELECT LIMIT 0 75  
    animals.commonname,
    animals.scientificname,
    animals.lifespan,
    animals.lifespan AS lifespan2,
    animals.lifespan AS reallifespan,
    employees1.salary AS realsalary,
    animals.status,
    animals.diet,
    animals.information,
    animals.picture,
    employees1.name AS employeename,
    employees1.salary AS employeesalary,
    employees2.name AS employeename2,
    employees2.salary AS employeesalary2
FROM
    animals
    JOIN employeetable employees1 ON animals.lifespan = employees1.employeeid
    JOIN employeetable employees2 ON animals.lifespan = employees2.employeeid
WHERE
    (
        '1' = '1'
    )
ORDER BY
[B]    employeename,
    employeename2[/B]

The "order by" here is definitely wrong (expecting salary here, twice). If now for my application there would be no client-side reorder after fetch, this would explain the issue.

2nd smaller issue:
I can consistently see two identical fetches in the Developer Console:


I don't know why this happens - there is setAutoFetchData(false) and also, if you disable the manual fetchData() there is no fetch at all.

Best regards
Blama

[Isomorphic]

In general when the user sorts on a field, we sort by the displayField if there is one, because otherwise it appear definitely wrong to the user. The sortByField is intended to mimic a user sort, so this is all as designed and, we think, as desired.

If you have some odd situation where you want to have a sort that doesn't use the value the user actually sees, that seems very odd to us, but it could be achieved by not setting the displayField property and instead using a CellFormatter to output the value from the field that used to be marked as the displayField. In this case the sort will be based on the field value, even though that's not displayed to the user.

[Blama]

Hi Isomorphic,

In general this might be OK, but not always, and for exactly this you created sortByField in 5.1 (see this thread).
Use case is an ordered list of possible values that does not match lexical ordering, e.g. "Hot - Warm - Cold" or "High - Medium - Low".

In my opinion two things are happening:

1. The SQL ORDER BY is wrong. It should be "employeesalary, employeesalary2"
2. In the testcase, client side sort is happening and this fixes the wrong ORDER BY.
   In my application, client side sort does not happen and therefore I see the issue I reported. It don't know if this is an issue itself, but it wouldn't be a problem with a correct SQL ORDER BY.

Also see the sortByField docs, which state that this also affects server sort.

Causes values for this field to be sorted according to values for another field, for both client- and server-side sorting.

Best regards
Blama

[Isomorphic]

We see this one and we'll update here when it's been fixed.

[Isomorphic]

This has been fixed for builds dated August 14 and later.

[Blama]

Hi Isomorphic,

I can see this is fixed using v11.1p_2017-08-16 in the provided testcase. I'll retry my application as well.

Best regards
Blama