filter not working in a grid with dataFetchMode: "basic" and a fetchOperation

[claudiobosticco]

SmartClient Version: v13.0p_2023-06-21/Enterprise Deployment (built 2023-06-21)

Chrome on MacOS

Hello, I've got a problem with a grid where client filtering won't work (dataFetchMode: "basic" or paged with all records loaded), ie every record disappears when I filter for a column, and they don't show back when I erase the filter.

For now, I've not been able to replicate it in the showcase. So I'm looking for help to debug it.

The datasource has got a fetchOperation like this:

      <operationBinding operationType="fetch" operationId="fetchUsersGroupByRoles" outputs="ROLES,EMAIL,APP_ID" customFields="ROLES">
            <summaryFunctions>
                <ROLES>concat</ROLES>
            </summaryFunctions>
            <groupBy>EMAIL,APP_ID</groupBy>
        </operationBinding>

which I don't think could be a problem...but if I remove the fetchOperation, then it starts working.

Anyway, have you got some suggestion? Some logging category that I may use in the developer console to understand why client filtering is not working?

Thanks in advance

[Isomorphic]

Since this is an operation that involves grouping, you should check out the "Criteria" section here to understand how criteria work with grouping:

https://smartclient.com/smartclient-...erverSummaries

[claudiobosticco]

Hello, thanks for your reply, but even after reading the doc I'm not quite understanding why client side filtering won't work in this case where grouping is involved (is this that you're suggesting?).

Also I see that useClientFiltering is true on the created ResultSet (I've got dataFetchMode: "basic").

It works only if I use myDS.fetchData(criteria, "myGrid.setData(data)").

Otherwise I can use:
dataProperties:{useClientFiltering:false}
but maybe it doesn't make much sense with dataFetchMode: "basic"

[Isomorphic]

If you, for example, did server filtering on a field that is not included in the results, then as soon as client filtering is performed, no records will match.

Outside of these general tips, we can't help much without seeing the data and the criteria.

[claudiobosticco]

Hello, I think I have identified the trigger for the problem I am encountering.
In my dataSource, there is a field called ROLE field which is an includeFrom="AUTH_ROLES.ROLE", and another ROLES field with the same includeFrom (but with customSQL="true"), which is used in the concat summaryFunction (and the ROLE field is excluded from the same operation binding using outputs).

Interestingly, when I remove the ROLE field from the dataSource, the filtering works as expected.

Here is the code (I can include the other dataSources if useful):

<DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
            ID="AUTH_USERS_ROLES_EXT"
            serverType="sql"
            dbName="myDB"
            schema="mySchema"
            tableName="AUTH_USERS_ROLES"
            allowAdvancedCriteria="true"
            useAnsiJoins="true"
            requiresAuthentication="true"
            dropExtraFields="false"
>
    <fmt:bundle basename="i18nMessages" encoding="utf-8"/>
    <fields>
        <field name="ID_REC" primaryKey="true" type="sequence" hidden="true" sequenceName="SEQUENCE_AUTH"/>
        <field name="ID_ROLE_FK" type="integer" foreignKey="AUTH_ROLES.ID_REC" relatedTableAlias="ROLES" required="true"/>
        <field name="ID_USER_FK" type="integer" foreignKey="AUTH_USERS.ID_REC" relatedTableAlias="USERS" required="true"/>
        <field name="ROLE" type="text" includeFrom="AUTH_ROLES.ROLE" includeVia="ID_ROLE_FK"/>

        <field name="ROLES" type="text" includeFrom="AUTH_ROLES.ROLE" includeVia="ID_ROLE_FK" customSQL="true"/>
        <field name="EMAIL" type="text" includeFrom="AUTH_USERS.EMAIL" includeVia="ID_USER_FK"/>

        <field name="APP_ID" type="text" includeFrom="AUTH_ROLES.AUTH_APPS.APP_ID" includeVia="ID_ROLE_FK"/>
    </fields>
    <operationBindings>
        <operationBinding operationType="fetch" operationId="fetchUsersGroupByRoles" outputs="ROLES,EMAIL,APP_ID"
                          customFields="ROLES">
            <summaryFunctions>
                <ROLES>concat</ROLES>
            </summaryFunctions>
            <groupBy>EMAIL,APP_ID</groupBy>
        </operationBinding>
    </operationBindings>
</DataSource>

and the grid:

isc.ListGrid.create({
    ID: "elencoUtentiGrid",
    dataSource: "AUTH_USERS_ROLES_EXT",
    dataFetchMode: "basic",
    fetchOperation: "fetchUsersGroupByRoles",
    showFilterEditor: true,
    initialSort: [
        {property: "EMAIL", direction: "ascending"}
    ],
    fields: [
        {name: "EMAIL", title: "Utente"},
        {name: "ROLES", title: "Ruoli"}
    ]
});

what I see is that after the first request, which is:

{
    dataSource:"AUTH_USERS_ROLES_EXT",
    operationType:"fetch",
    operationId:"fetchUsersGroupByRoles",
    componentId:"elencoUtentiGrid",
    data:{
        APP_ID:"JcaAuth",
        ROLE:[
            "ROLE_1",
            "ROLE_2",
            "ROLE_ADMIN",
            "ROLE_JAS",
            "ROLE_JAT",
            "ROLE_JFATT",
            "ROLE_JSCOUT"
        ]
    },
    sortBy:[
        "EMAIL"
    ],
    textMatchStyle:"exact",
    resultSet:[ResultSet ID:isc_ResultSet_10 (dataSource: AUTH_USERS_ROLES_EXT, created by: elencoUtentiGrid)],
    callback:{
        caller:[ResultSet ID:isc_ResultSet_10 (dataSource: AUTH_USERS_ROLES_EXT, created by: elencoUtentiGrid)],
        methodName:"fetchRemoteDataReply"
    },
    willHandleError:true,
    showPrompt:true,
    prompt:"Ricerca dei Record corrispondenti al filtro...",
    oldValues:{
        APP_ID:"JcaAuth",
        ROLE:[
            "ROLE_1",
            "ROLE_2",
            "ROLE_ADMIN",
            "ROLE_JAS",
            "ROLE_JAT",
            "ROLE_JFATT",
            "ROLE_JSCOUT"
        ]
    },
    requestId:"AUTH_USERS_ROLES_EXT_request15",
    internalClientContext:{
        requestIndex:1
    },
    fallbackToEval:false,
    dataFetchMode:"basic",
    lastClientEventThreadCode:"MUP0",
    bypassCache:true,
    dataProtocol:"getParams"
}

which produces a query like this:

SELECT LISTAGG('' || ROLES.ROLE || '', ', ') WITHIN GROUP (ORDER BY ROLES.ROLE) AS ROLES,
       USERS.EMAIL,
       ROLES_APPS.APP_ID
FROM DBJFRAME.AUTH_USERS_ROLES
         JOIN DBJFRAME.AUTH_ROLES ROLES ON AUTH_USERS_ROLES.ID_ROLE_FK = ROLES.ID_REC
         JOIN DBJFRAME.AUTH_USERS USERS ON AUTH_USERS_ROLES.ID_USER_FK = USERS.ID_REC
         JOIN DBJFRAME.AUTH_APPS ROLES_APPS ON ROLES.ID_APP_FK = ROLES_APPS.ID_REC
WHERE (((LOWER(ROLES.ROLE) = 'role_1') OR (LOWER(ROLES.ROLE) = 'role_2') OR (LOWER(ROLES.ROLE) = 'role_admin') OR
        (LOWER(ROLES.ROLE) = 'role_jas') OR (LOWER(ROLES.ROLE) = 'role_jat') OR (LOWER(ROLES.ROLE) = 'role_jfatt') OR
        (LOWER(ROLES.ROLE) = 'role_jscout')) AND LOWER(ROLES_APPS.APP_ID) = 'jcaauth')
GROUP BY USERS.EMAIL, ROLES_APPS.APP_ID
ORDER BY EMAIL

and this response:

{
    affectedRows:0,
    data:[
        {
            APP_ID:"JcaAuth",
            ROLES:"ROLE_ADMIN",
            EMAIL:"ad@bar.com"
        },
        {
            APP_ID:"JcaAuth",
            ROLES:"ROLE_1, ROLE_ADMIN",
            EMAIL:"az@bar.com"
        },
        {
            APP_ID:"JcaAuth",
            ROLES:"ROLE_ADMIN",
            EMAIL:"cb@bar.com"
        },
        {
            APP_ID:"JcaAuth",
            ROLES:"ROLE_ADMIN",
            EMAIL:"dl@bar.com"
        },
        {
            APP_ID:"JcaAuth",
            ROLES:"ROLE_JAS, ROLE_JAT, ROLE_JFATT, ROLE_JSCOUT",
            EMAIL:"dm@bar.com"
        },
        {
            APP_ID:"JcaAuth",
            ROLES:"ROLE_ADMIN",
            EMAIL:"dp@bar.com"
        },
        {
            APP_ID:"JcaAuth",
            ROLES:"ROLE_ADMIN",
            EMAIL:"ih@bar.com"
        },
        {
            APP_ID:"JcaAuth",
            ROLES:"ROLE_ADMIN",
            EMAIL:"mp@foo.it"
        },
        {
            APP_ID:"JcaAuth",
            ROLES:"ROLE_ADMIN",
            EMAIL:"sl@foo.it"
        }
    ],
    endRow:9,
    invalidateCache:false,
    isDSResponse:true,
    operationType:"fetch",
    queueStatus:0,
    startRow:0,
    status:0,
    totalRows:9
}

what I see is that filtering won't work, ie when I enter any value in the EMAIL filter, all the records disappear (no fetch issued) and do not reappear even when the filter is canceled.

However, when I remove the ROLE field from the dataSource, the filtering starts working as intended. (*).

Please let me know if you need any further information or clarification.

(*) as a side note, it works by doing a fetch...it's still not clear to me why filtering couldn't be done at the client (BTW I see elencoUtentiGrid.data.useClientSorting is true)

[claudiobosticco]

Just to let you know, this is what I've tried to replicate it in the showcase:

isc.ListGrid.create({
    ID: "elencoUtentiGrid",
    width: "100%", height: "100%",
    gridComponents: ["filterEditor", "header", "body"],
    dataSource: "teamMembers2",
    dataFetchMode: "basic",
    fetchOperation: "myFetch",
    showFilterEditor: true,
    selectionAppearance: "checkbox",
    filterOnKeypress: true,
    fields: [
        {name: "EmployeeEmails"},
        {name: "EmployeeOrgUnit"},
        {name: "EmployeeType"}
    ],
    autoFetchData: true
})

<?xml version="1.0" encoding="UTF-8"?>
<!-- Many-to-many pivot table mapping Teams ↔ Employees -->
<DataSource isSampleDS="true"
    ID="teamMembers2"
    serverType="sql"
    tableName="teamMembers2"
    recordName="teamMember2"
    testFileName="/examples/shared/ds/test_data/teamMembers2.data.xml"
>
    <fields>
        <field name="RowId" hidden="true" type="sequence" primaryKey="true"/>
        <field name="TeamId" hidden="true" type="integer"
               foreignKey="teams.TeamId"/>
        <field name="TeamName" includeFrom="teams.TeamName"/>
        <field name="EmployeeId" hidden="true" type="integer"
               foreignKey="employees.EmployeeId"/>
        <field name="EmployeeName" includeFrom="employees.Name"/>

        <field name="EmployeeUserOrder" includeFrom="employees.userOrder" />
        <field name="EmployeeReportsTo" includeFrom="employees.ReportsTo" />
        <field name="EmployeeJob" includeFrom="employees.Job" />
        <field name="EmployeeEmail" includeFrom="employees.Email" />
        <field name="EmployeeType" includeFrom="employees.EmployeeType" />
        <field name="EmployeeStatus" includeFrom="employees.EmployeeStatus" />
        <field name="EmployeeSalary" includeFrom="employees.Salary" />
        <field name="EmployeeOrgUnit" includeFrom="employees.OrgUnit" />
        <field name="EmployeeGender" includeFrom="employees.Gender" />
        <field name="EmployeeMaritalStatus" includeFrom="employees.MaritalStatus" />

        <field name="EmployeeEmails" includeFrom="employees.Email" customSQL="true" />

    </fields>

    <operationBindings>
        <operationBinding operationType="fetch" operationId="myFetch" outputs="EmployeeEmails,EmployeeOrgUnit,EmployeeType" customFields="EmployeeEmails">
            <summaryFunctions>
                <EmployeeEmails>concat</EmployeeEmails>
            </summaryFunctions>
            <groupBy>EmployeeOrgUnit,EmployeeType</groupBy>
        </operationBinding>
    </operationBindings>
</DataSource>

but the concat doesn't seem to work:

=== 2023-06-27 08:48:43,583 [ec-8] INFO DSRequest - Executing teamMembers2.fetch[myFetch] rows: 0->-1 with
outputs: [EmployeeEmails]
criteria: {
    _constructor:"AdvancedCriteria",
    criteria:[
        {fieldName:"EmployeeType", value:"contract", operator:"iEquals"},
        {
            criteria:[],
            operator:"and"
        }
    ],
    __normalized:true,
    operator:"and"
}
=== 2023-06-27 08:48:43,584 [ec-8] INFO SQLWhereClause - empty condition
=== 2023-06-27 08:48:43,584 [ec-8] INFO SQLWhereClause - empty condition
=== 2023-06-27 08:48:43,586 [ec-8] INFO SQLDriver - Executing SQL query on 'HSQLDB': SELECT CONCAT(employeeTable.Email) AS EmployeeEmails FROM teamMembers2, employeeTable WHERE ((LOWER(employeeTable.EmployeeType)=LOWER('contract') AND employeeTable.EmployeeType IS NOT NULL) AND ('1'='1')) AND teamMembers2.EmployeeId = employeeTable.EmployeeId HAVING (('1'='1'))
=== 2023-06-27 08:48:43,586 [ec-8] INFO SQLDriver - Execute of select: SELECT CONCAT(employeeTable.Email) AS EmployeeEmails FROM teamMembers2, employeeTable WHERE ((LOWER(employeeTable.EmployeeType)=LOWER('contract') AND employeeTable.EmployeeType IS NOT NULL) AND ('1'='1')) AND teamMembers2.EmployeeId = employeeTable.EmployeeId HAVING (('1'='1')) on db: HSQLDB threw exception: java.sql.SQLSyntaxErrorException: unexpected token: ) required: , - assuming stale connection and retrying query.
=== 2023-06-27 08:48:43,588 [ec-8] WARN RequestContext - dsRequest.execute() failed:
java.sql.SQLSyntaxErrorException: unexpected token: ) required: ,
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)

[Blama]

Hi claudiobosticco, Hi Isomorphic,

regarding #6 the generated SQL does not include a GROUP BY, which seems wrong. I assume this is unrelated to the original issue.

Best regards
Blama

[claudiobosticco]

SmartClient Version: v13.0p_2023-06-27/AllModules Development Only (built 2023-06-27)

Also, I'm trying this sample : https://smartclient.com/smartclient-...summariesBasic

And if I try filtering by in the item description column:

- with amount by item: it filters client side
- with amount by customer: it makes a fetch, but the values in the column disappear and don't reappear after clearing the filter
- with minimum/maximum price: it makes a fetch to filter

Instead, in the #summariesDynamic sample, for all 4 options it's filtering client side and without problems

[Blama]

Hi claudiobosticco, Isomorphic,

problem in https://smartclient.com/smartclient-...summariesBasic (v13.0p_2023-06-27) is the operationId used for filtering:

• Start: fetch with fetchOperation: "amountByItem" from ListGrid definition (OK)
• Filter: Clientside (OK)
• Remove filter: Clientside (OK)
• Switch to "amount by customer": fetch with fetchData and {operationId: value}, value=amountByCustomer (OK)
• Filter: Servercall with amountByItem (unexpected)

Best regards
Blama

[claudiobosticco]

Hi Blama you're right about the sample summariesBasic, adding:

orderItemList.setProperty('fetchOperation', value);

in the changed fixes it.

[Isomorphic]

As far as the actual app, your criteria for the "role" field is looking for exact match, but the values for the role field are now long strings produced by concatenation, so that's not going to match.

Also, removing the "role" field entirely of course fixes the problem because now the criteria against the "role" field are just ignored (they do nothing).

There are many solutions, but one is to just to move the criteria on the "role" field out of the UI later and inject it in e.g. DataSource.transformRequest().

[claudiobosticco]

Hello Isomorphic, thank you for your reply.

It's still not clear to me why client-side filtering on the EMAIL field couldn't work.

Nevertheless, following your hint, I have modified the fetch criteria to be based on the ROLES field. I generate the actual criteria with Velocity server-side. Additionally, I have set 'canFilter:false' on the ROLES ListGridField as it is not really necessary.

Now it's working for my use case, with client-side filtering on the EMAIL field.

[Isomorphic]

Once you're in the situation that you have criteria on the "roles" field that won't match any of the loaded records, it doesn't matter what other criteria you then modify - any triggering of client-side filtering will wipe all the records, since none match the criteria.

[claudiobosticco]

Finally, I think I understood. This also explains why the records don't show anymore, even if I clear the filter.

Thank you also for your persistence :-)