groupBy many fields .ds.xml issue

[Blama]

Hi Isomorphic,

w.r.t to this answer of yours I tried to rewrite my aggregation .ds.xml, but was unsuccessful in doing so.

Please see this BuiltInDS based testcase (v12.0p_2019-01-12, using Oracle so that the query generation is the same it is for me):

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.DSRequest;
import com.smartgwt.client.data.DataSource;
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.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("95%");
        w.setHeight("95%");
        w.setMembersMargin(0);
        w.setModalMaskOpacity(70);
        w.setTitle(" (" + Version.getVersion() + "/" + Version.getSCVersionNumber() + ")");
        w.setTitle("GroupBy SQL issue" + w.getTitle());
        w.setShowMinimizeButton(false);
        w.setIsModal(true);
        w.setShowModalMask(true);
        w.centerInPage();

        IButton btn1 = new IButton("Fetch1", new ClickHandler() {
            @Override
            public void onClick(ClickEvent event) {
                DataSource.get("supplyItem").fetchData(null, null, new DSRequest() {
                    {
                        setOperationId("fetch1");
                    }
                });
            }
        });
        w.addItem(btn1);

        IButton btn2 = new IButton("Fetch2", new ClickHandler() {
            @Override
            public void onClick(ClickEvent event) {
                DataSource.get("supplyItem").fetchData(null, null, new DSRequest() {
                    {
                        setOperationId("fetch2");
                    }
                });
            }
        });
        w.addItem(btn2);
        w.show();
    }
}

supplyItem.ds.xml:

<DataSource
    ID="supplyItem"
    serverType="sql"
    tableName="supplyItem"
    titleField="itemName"
>
    <fields>
        <field name="itemID"      type="sequence" hidden="true"       primaryKey="true"/>
        <field name="itemName"    type="text"     title="Item"        length="128"       required="true"/>
        <field name="SKU"         type="text"     title="SKU"         length="10"        required="true"/>
        <field name="description" type="text"     title="Description" length="2000"/>
        <field name="category"    type="text"     title="Category"    length="128"       required="true"
               foreignKey="supplyCategory.categoryName"/>
        <field name="units"       type="enum"     title="Units"       length="5">
            <valueMap>
                <value>Roll</value>
                <value>Ea</value>
                <value>Pkt</value>
                <value>Set</value>
                <value>Tube</value>
                <value>Pad</value>
                <value>Ream</value>
                <value>Tin</value>
                <value>Bag</value>
                <value>Ctn</value>
                <value>Box</value>
            </valueMap>
        </field>
        <field name="unitCost"    type="float"    title="Unit Cost"   required="true">
            <validators>
                <validator type="floatRange" min="0" errorMessage="Please enter a valid (positive) cost"/>
                <validator type="floatPrecision" precision="2" errorMessage="The maximum allowed precision is 2"/>
            </validators>
        </field>
        <field name="inStock"   type="boolean"  title="In Stock"/>
        <field name="nextShipment"  type="date" title="Next Shipment"/>
        <field name="cntField" type="integer" customSQL="true" customSelectExpression="1" />
    </fields>
    <operationBindings>
        <operationBinding operationType="fetch" operationId="fetch1" outputs="itemID, itemName, SKU, cntField">
            <customFields>cntField</customFields>
            <summaryFunctions>
                <cntField>count</cntField>
            </summaryFunctions>
            <groupBy>itemID</groupBy>
            <groupBy>itemName</groupBy>
            <groupBy>SKU</groupBy>
        </operationBinding>

        <operationBinding operationType="fetch" operationId="fetch2" outputs="itemID, itemName, SKU, cntField">
            <customFields>cntField</customFields>
            <summaryFunctions>
                <cntField>count</cntField>
            </summaryFunctions>
            <groupBy>
              <field>itemID</field>
              <field>itemName</field>
              <field>SKU</field>
          </groupBy>
        </operationBinding>
    </operationBindings>
</DataSource>

server.properties change (+add ojdbc6.jar to .classpath):

sql.defaultDatabase: Oracle
sql.defaultDatabase.jndi.publish.path: isomorphic/jdbc/defaultDatabase
sql.Oracle.database.type: oracle
sql.Oracle.driver: oracle.jdbc.pool.OracleDataSource
sql.Oracle.driver.serverName: localhost
sql.Oracle.driver.portNumber: 1521
sql.Oracle.driver.databaseName: XE
sql.Oracle.driver.user: xxx
sql.Oracle.driver.password: xxx

Generated query for Button 1 (expected):

SELECT
     supplyitem.itemid,
     supplyitem.itemname,
     supplyitem.sku,
     COUNT(1) AS cntfield
 FROM
     supplyitem
 WHERE
     ( '1' = '1' )
 GROUP BY
     supplyitem.itemid,
     supplyitem.itemname,
     supplyitem.sku

Generated query for Button 2 (not expected):

SELECT COUNT(1) AS cntField FROM supplyItem WHERE ('1'='1')

Best regards
Blama

[Isomorphic]

We've switched "groupBy" in OperationBinding to use "multiple" syntax (as in your second binding), but the old syntax (first binding) should still work too. This is effective with the nightly builds dated 2018-01-23, back to SGWT 5.1p/SC 10.1p.

[Blama]

Hi Isomorphic,

I can see that the same query is now beeing generated in both cases using v12.0p_2019-01-23.
I'll double check in my application and then switch to the suggested syntax.

Thank you & Best regards
Blama

[Blama]

Hi Isomorphic,

this is also working as expected in the application.

Thank you & Best regards
Blama