12.0p+ ds.xml Aggregation and primaryKey and SelectItem question

[Blama]

Hi Isomorphic,

I have a problem with an aggregation operation binding and using the results of this in a SelectItem.
Before I try to create a testcase (would have to do this on my computer and not in the showcase as I need to edit .ds.xml) I wanted to understand that my thinking is correct.

Imagine the supplyItem DataSource being used as followed:

• TextItem for itemName
• SelectItem that uses that TextItem to filter itemName in pickListCriteria
  • valueField: "category"
  • displayField: "category"
  • picklistFields: [{name:"category"},{name:"itemID", title:"Count"}]
• optionDataSource for this SelectItem is a supplyItem-operationBinding like this:
  

  	<operationBinding operationType="fetch" operationId="fetchCount" serverMethod="fetchCount" outputs="itemID, category">
  	            <summaryFunctions>
  	                <itemID>count</itemID>
  	            </summaryFunctions>
  	            <groupBy>
  	                <groupByField>category</groupByField>
  	            </groupBy>
  	        </operationBinding>

• The DMI for the operationBinding is that way that it adds rows with count (=itemID) 0 for categories that are not in the result via a 2nd Select.
  This is so that the SelectItem can display all categories to select from, not only those where there is a row for the current filter string. This is working fine.
• Now, depending on the filtertext in the TextItem, the aggregated result differs (expected), but because of the DMI it always shows all categories, possibly with count (=itemID) 0.
• Storage of currently selected categories in the SelectItem in an own DataSource for this
• Application of these saved categories via "value: ..."

My problem starts now when there is the same count (=itemID) value (e.g. 0) for many categories. Then this preselecting of the saved categories does not work.
My assumption is that even though I'm setting valueField: "category" somehow the count (=itemID) field is still seen as primaryKey and having double values here causes this problem.
So I think there is a problem with the primaryKey somehow luring in the background even though I set a different valueField.

I wanted to double check with you that this setup is expected to work. If not, what am I supposed to do here?
In general an aggregation that does not include the primaryKey in the groupBy (an then it's not really an aggregation, as all primaryKeys will be different) will result in either no value for the primaryKey or possibly doubled values like in my case.

The only other solution I could imagine is having a separate DataSource for this with category as primaryKey. But that defies the idea of operationBindings a bit IMHO if I have to duplicate DataSources for every aggregation.

Best regards
Blama

[Isomorphic]

When you say this "preselecting of the saved categories does not work", what are you referring to? It sounds like your DMI does an additional select to get the list of all categories, unfiltered, so you can fill in zeros for categories that don't match the filter criteria. But if it's this additional select that isn't working, you haven't shown how you're doing it, or explained what's wrong with the results, so we can't help..

[Blama]

Hi Isomorphic,

here is the real test data from my current development:

The value of the multiple:true-SelectItem is in both cases set to ["workInProgress", "resubmission", "offer", "appointment"] (see data for STATUS_SHORTNAME, which is the value-field of the SelectItem, below).
In the 1st case all four boxes are checked (expected).
In the 2nd case only the boxes for "workInProgress", "resubmission" are checked (unexpected).

1st case is working fine. No special filter for the request pushing the result to zero, therefore no "duplicate primary key" (I'm putting this in quotes, as the primary key should not be important here as I define a valueField).

{
    affectedRows:0,
    data:[
        {
            LEAD_ID:27, --this is the count from the serverside aggregation and not the primary key, see <itemID>count</itemID> in #1. See below for my real-world operationBinding in #1
            STATUS_POSITION:7,
            STATUS_SHORTNAME:"workInProgress", --this is the valueField of the SelectItem
            STATUS_PICKED:true
        },
        {
            LEAD_ID:6,
            STATUS_POSITION:8,
            STATUS_SHORTNAME:"resubmission",
            STATUS_PICKED:true
        },
        {
            LEAD_ID:2,
            STATUS_POSITION:11,
            STATUS_SHORTNAME:"offer",
            STATUS_PICKED:true
        },
        {
            LEAD_ID:0, --this row is added in the DMI but from the client's perspective it looks like the other rows. It's the only row in this fetch that is added in the DMI and not from real aggregation
            STATUS_POSITION:9,
            STATUS_SHORTNAME:"appointment",
            STATUS_PICKED:true
        }
    ],
    endRow:4,
    invalidateCache:false,
    isDSResponse:true,
    operationType:"fetch",
    queueStatus:0,
    startRow:0,
    status:0,
    totalRows:4
}

Now a fetch with extra criteria, resulting in less aggregation-rows and more added count:0-rows and my problem:

{
    affectedRows:0,
    data:[
        {
            LEAD_ID:4,--this is the count from the serverside aggregation
            STATUS_POSITION:7,
            STATUS_SHORTNAME:"workInProgress", --this is the valueField of the SelectItem
            STATUS_PICKED:true
        },
        {
            LEAD_ID:0, --this row is added in the DMI but from the client's perspective it looks like the other rows
            STATUS_POSITION:8,
            STATUS_SHORTNAME:"resubmission",
            STATUS_PICKED:true
        },
        {
            LEAD_ID:0, --this row is added in the DMI but from the client's perspective it looks like the other rows
            STATUS_POSITION:9,
            STATUS_SHORTNAME:"appointment",
            STATUS_PICKED:true
        },
        {
            LEAD_ID:0, --this row is added in the DMI but from the client's perspective it looks like the other rows
            STATUS_POSITION:11,
            STATUS_SHORTNAME:"offer",
            STATUS_PICKED:true
        }
    ],
    endRow:4,
    invalidateCache:false,
    isDSResponse:true,
    operationType:"fetch",
    queueStatus:0,
    startRow:0,
    status:0,
    totalRows:4
}

As you can see, the "primaryKey" 0 is present three times, and I think this is what is leading to this problem.
But again even though the column LEAD_ID is "primaryKey" in the DataSource it can't be used clientside as a primaryKey as the field is aggregated in this fetch:

<operationBinding operationType="fetch" operationId="fetchCount" serverMethod="fetchCount" outputs="LEAD_ID, STATUS_SHORTNAME, STATUS_POSITION, STATUS_PICKED">
            <summaryFunctions>
                <LEAD_ID>count</LEAD_ID>
            </summaryFunctions>
            <groupBy>
                <groupByField>STATUS_SHORTNAME</groupByField>
                <groupByField>STATUS_POSITION</groupByField>
                <groupByField>STATUS_PICKED</groupByField>
            </groupBy>
        </operationBinding>

And it should not be used as primaryKey, as I define a valueField of STATUS_SHORTNAME in the SelectItem.

I hope this makes the situation clearer.

Best regards
Blama

[Isomorphic]

At a high level, this usage looks OK, as long as you have set things up so that a fetch using the valueField as criteria does actually work (required: see fetchMissingValues).

Possibly you’ve discovered a bug where there is still an expectation that the PK is also unique, but right now, we can’t tell - we’d need a full test case.

If your suspicion is correct and the PK is the problem, you could just do the count aggregation on another field, and if that clears up the problem, then it would make sense to blame the problem on the PK field being treated specially.

[Blama]

Hi Isomorphic,

I should have said that. I already tried that idea, same issue.
fetchMissingValues is true in my case (by default) and all the values of STATUS_SHORTNAME (=valuefield) that are set in values are in both responses, so I don't think this should cause a problem.

It seems I have to create a testcase then.

• I know how to create a BuiltInDS based SmartGWT testcase, where I'd have access to supplyitem.
• I also know how to create a clientonly SmartClient testcase.
• I assume I could also just fire up SmartGWT BuiltInDS and create a SmartClient based testcase there, but what would be the easiest way for SmartClient users (using https://www.smartclient.com/builds/S...ent/12.0p/Eval) to create a server backed testcase? It does not seem there is a BuiltInDS sample here.

Thank you & Best regards
Blama

[Isomorphic]

In SmartClient, because there's no compilation process, the whole SDK is basically one big test environment. Fire up the included embedded server, and modify sample .ds.xml files or copy them. Create a .html or .jsp file anywhere to contain your code (or just write it inside the Developer Console). There are templates that have all the right script includes in the templates/ directory.

[Blama]

Hi Isomorphic,

thanks for the advice. I used SmartGWT BuiltInDS to create a SmartClient testcase (v12.1p_2022-04-21, also in 12.0p), as I needed an Eclipse project for my serverside DMI code anyway. This is working fine and a good workflow for me. Now it would be great if Eclipse had built-in TypeScript IntelliSense support.

Please see this testcase and this picture showing the result without and with filtering. Both have issues, as the first five entries should be checked in both cases.
The value for the SelectItem is in both cases set like this in the code:

values: [{
                category: ["Accessories", "Account Books", "Adding Machine/calculator Roll", "Address Labels", "Adhesive Tape"]
            }],

• In the 1st case one row is not selected, because an other row also has a count of "6" (unexpected)
• In the 2nd case three rows are not selected, because an other row also has a count of "0" (unexpected)
• I'd expect 5 selected entries in both cases, as the valueField is "category" and the primaryKey itemID(=count, because this is aggregated) field should not matter.

Best regards
Blama

Testcase.js (loaded in <body> of BuildInDS.html):

isc.VLayout.create({
    width: "100%",
    height: "100%",
    membersMargin: 30,
    members: [
        isc.DynamicForm.create({
            ID: "filterForm",
            width: 400,
            titleWidth: 250,
            fields: [{
                name: "itemName",
                title: "Item Name filter",
                operator: "startsWith",
                autoDraw: false,
                wrapTitle: false,
                type: "text"
            }],
            values: [{
                itemName: "Add"
            }],
            itemChanged: function(item, newValue) {
                isc.logWarn("Changed");
                dsListGrid.setImplicitCriteria({
                    _constructor: "AdvancedCriteria",
                    operator: "and",
                    criteria: [
                        countForm.getValuesAsCriteria(),
                        filterForm.getValuesAsCriteria()
                    ]
                });
            }
        }),
        isc.DynamicForm.create({
            ID: "countForm",
            width: 400,
            titleWidth: 250,
            fields: [{
                name: "category",
                title: "Category",
                wrapTitle: false,
                type: "select",
                optionDataSource: "supplyItem",
                displayField: "category",
                valueField: "category",
                autoDraw: false,
                operator: "inSet",
                multiple: true,
                optionOperationId: "fetchCount",
                sortField: "category",
                pickListWidth: 400,
                pickListProperties: {
                    dataProperties: {
                        fetchMode: "basic"
                    }
                },
                pickListFields: [{
                    name: "category"
                }, {
                    name: "itemID",
                    title: "Count"
                }],
                getPickListFilterCriteria: function() {
                    return {
                        _constructor: "AdvancedCriteria",
                        operator: "and",
                        criteria: [filterForm.getValuesAsCriteria(), {
                            fieldName: "itemName",
                            operator: "notEqual",
                            value: new Date().toJSON()
                        }]
                    }
                },
            }],
            values: [{
                category: ["Accessories", "Account Books", "Adding Machine/calculator Roll", "Address Labels", "Adhesive Tape"]
            }],
            itemChanged: function(item, newValue) {
                isc.logWarn("Changed");
                dsListGrid.setImplicitCriteria({
                    _constructor: "AdvancedCriteria",
                    operator: "and",
                    criteria: [
                        countForm.getValuesAsCriteria(),
                        filterForm.getValuesAsCriteria()
                    ]
                });
            }
        }),
        isc.ListGrid.create({
            ID: "dsListGrid",
            width: "100%",
            height: "100%",
            autoFetchData: true,
            autoDraw: false,
            dataSource: "supplyItem",
            sortField: "itemName",
            implicitCriteria: {
                _constructor: "AdvancedCriteria",
                operator: "and",
                criteria: [
                    countForm.getValuesAsCriteria(),
                    filterForm.getValuesAsCriteria()
                ]
            }
        })
    ]
});

supplyItem.ds.xml addition:

<serverObject lookupStyle="new" className="com.smartgwt.sample.server.listener.SupplyItem" />
    <operationBindings>
           <operationBinding operationType="fetch" operationId="fetchCount" serverMethod="fetchCount" outputs="itemID, category">
            <summaryFunctions>
                <itemID>count</itemID>
            </summaryFunctions>
            <groupBy>
                <groupByField>category</groupByField>
            </groupBy>
        </operationBinding>
           <operationBinding operationType="fetch" operationId="fetchCategories" outputs="category">
            <groupBy>
                <groupByField>category</groupByField>
            </groupBy>
        </operationBinding>
    </operationBindings>

SupplyItem.java to add all the 0-entries where there are no aggregated rows after filtering:

package com.smartgwt.sample.server.listener;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import com.isomorphic.datasource.DSRequest;
import com.isomorphic.datasource.DSResponse;
import com.isomorphic.datasource.DataSource;

public class SupplyItem {

    @SuppressWarnings({ "serial", "unchecked" })
    public DSResponse fetchCount(DSRequest request, HttpServletRequest servletRequest) throws Exception {
        DSResponse firstResponse = request.execute();
        List<Map<String, Object>> firstResponseList = firstResponse.getDataList();

        DSRequest allCategoriesRequest = new DSRequest("supplyItem", DataSource.OP_FETCH, request.getRPCManager());
        allCategoriesRequest.setOperationId("fetchCategories");
        allCategoriesRequest.setSortBy("category");
        DSResponse allCategoriesResponse = allCategoriesRequest.execute();

        for (Object o : allCategoriesResponse.getDataList()) {
            String category = (String) ((Map<String, Object>) o).get("category");
            if (firstResponseList.stream().noneMatch(x -> category.equals((String) x.get("category")))) {
                firstResponseList.add(new LinkedHashMap<String, Object>() {
                    {
                        put("category", category);
                        // Fake count
                        put("itemID", 0);
                    }
                });
            }
        }
        return firstResponse.setData(firstResponseList).setTotalRows(firstResponseList.size()).setEndRow(firstResponseList.size());
    }
}

[Isomorphic]

Thanks, we'll check this out.

As far as workarounds, you are most likely correct that there is an assumption somewhere that we can index rows by the PK, which is not valid in this unusual situation of applying aggregation to the PK field.

If this is indeed what the problem is, you should be able to get around it fairly simply, eg, an inheritsFrom DataSource where the category is declared as the PK & valueField.

As far as tools, since you do seem to be enjoying playing with SmartClient, VSCode is a good editor for TypeScript-enhanced JavaScript, and you can use Server Scripting to embed small bits of logic right in your .ds.xml files (in Groovy, JavaScript, or whatever).

[Blama]

Hi Isomorphic,

thanks. I also thought about the exact same workaround before. But as I have to update because of the fix for setImplicitCriteria() anyway, I think I'll wait here.

The problem is not with "aggregation to the PK field". It's with aggregation in general. Aggregation of the unique PK field does not make sense (row count stays the same) and any aggregation of any other field(s) will always lead to either possibly duplicate values for the PK-field (as here) or no value at all for the PK field (same problem, your suggestion from #4).

Also thanks for the tips on the tools. Yes, I'll look into VSCode. What I enjoy about SmartClient compared to SmartGWT is the reload times, that are instant.
Back in 2013 with no IntelliSense for JS I definitely preferred GWT, but now this is different.
Also I realized that we programmed way to much in SmartGWT and did not rely enough on built-in declarative features of the framework.
The goal of any project should be serverside code (with heavy use of Dynamic DataSources where needed) and clientside widgets-only as much as possible. But what is also true is that back in the day there were much less declarative features. I'm thinking of showIf, visibleWhen, readOnlyWhen, Hilites, implicitCriteria (only added in 6.1/11.1).

Now Reify is taking this even a step further. Also looking forward to this :)

Thank you & Best regards
Blama

[Blama]

Hi Isomorphic,

while I think this is a bug, I found a different workaround.
In order to get an unique aggregation value for the PK field, min/max do work, while count/avg won't. This is not optimal, but a possibility:

Changes:

.ds.xml:
<operationBinding operationType="fetch" operationId="fetchCount" serverMethod="fetchCount" outputs="itemID, unitCost, category">
            <summaryFunctions>
                <itemID>max</itemID>
                <unitCost>count</unitCost> <!-- must be a required="true" field in order to get correct numbers -->
            </summaryFunctions>



.java:
firstResponseList.add(new LinkedHashMap<String, Object>() {
                    {
                        put("category", category);
                        // Fake count
                        put("unitCost", 0);
                        // Fake PK
                        put("itemID", new Random().nextInt());
                    }
                });



.js:
pickListFields: [{
                    name: "category"
                }, {
                    name: "unitCost",
                    title: "Count"
                }],

Best regards
Blama

[Blama]

Hi Isomorphic,

did you already find out in the code what the root cause is here?

Best regards
Blama

[Isomorphic]

Hi Blama
Fundamentally the framework expects that if a primary key field is declared, it has to have unique values -- this is a documented and well understood rule that the client logic relies on in various places.
Using aggregation doesn't relax that requirement, so technically this isn't a bug (values that include duplicate values in the primary key field are unsupported, even if the values were produced by aggregation). However we absolutely agree that this is something the framework could handle better, and we have some proposed steps on our roadmap to improve this.

There are multiple ways that aggregated results can be produced by a dataSource -- operationBindings, custom server logic that does aggregation, requestProperties, so the planned feature would add support for a new boolean attribute on a dsResponse object indicating that a request produced an aggregated data set, have this be set automatically where appropriate [and allow server logic to explicitly set this flag for cases where custom logic performs its own aggregation of results], and then modify client side logic to detect this case and adapt as appropriate [avoid identifying records by primary key, etc].

These changes would resolve this issue at the framework level, without any need for special application logic to work around the problem. If you'd like us to prioritize it, it would be a valid candidate for feature sponsorship. Let us know if this is a route you'd like to pursue.

Alternatively you can work around this at the application level in a couple of ways:

1) You could create a Facade dataSource that inherits from the underlying dataSource but drops the primary key field. If you use this as your optionDataSource it should work fine out of the box.
2) You could issue a fetch directly against your target dataSource and assemble the results into an explicit valueMap to be applied to the form item instead of specifying an optionDataSource at all.

Hope this helps!
Isomorphic Software

[Blama]

Hi Isomorphic,

thanks for the explanation. I don't think this is a feature sponsorship for us, as there are ways to work around this.
As I don't want to clutter my DataSources and I also want to use optionDataSource, I think I'll go the route of my workaround in #10.

Is this currently planned in your roadmap for 13.1 or a later release?

Best regards
Blama

[Isomorphic]

Hi Blama
We don't currently have an ETA for this feature

Regards
Isomorphic Software

[Blama]

In case anyone finds this thread via search:
The workaround from #10 is working fine - I even managed dump the value of a field I GROUP BY (=unique) into the PK-field, so no Random() and no min/max-aggregation of the real PK is necessary and the clientside is always happy.