Extra Server-Side Filtering removing matching records

[stonebranch1]

I have a problem with some extra filtering that occurs on the server side even though I am using a custom datasource to do the original fetching/filtering.

The problem comes down to Date fields where sometimes the filter criteria has the Date value as a String ("2014-05-27") and other times the value is a Date object (Date(1401163200000), equates to "2014-05-27" or more specifically "2014-05-27 00:00:00" in Eastern TimeZone). I understand why it sometimes may come over that way ( and that is not a problem to us) as we handle both formats in our fetch methods and do the same lookup to the database and return the same record back. In the case of the Date filter value however, our record is removed from the response before the response is sent back to the client (only when the ds.xml contains an includeFrom field, see below).

I think the reason the record gets removed is because we send all our Date values to the client as String so as to not have any TimeZone problems since no time is included in the String value we return. I guess the "extra" filtering going on cannot match up the Date value with the String value.

I was surprised there was even this extra level of filtering going on at all on the server. Interestingly (you probably are aware of it) is that this extra server-side filtering only appears to take place when I have a ds.xml that contains an includeFrom field used as the displayField for a foreignKey field.

 <field name="calendarName" includeFrom="calendar.name" /> 
 <field name="calendar" foreignKey="calendar.sysId" displayField="calendarName" type="text" />

If I remove the displayField attribute as well as the the includeFrom field (in this case "calendarName") both scenarios work fine and the record is not removed.

Here are the 2 scenario details. The 2 response outputs are as follows, "Fetch Response:" is the response we send back at the very end of our fetch method and "JSResponse:" is the response being return by the servlet ala handleDSRequest method.

Working Scenario:
Advanced Criteria received on the Server:

criteria:[
    {
        fieldName:"tdate",
        operator:"equals",
        [b]value:"2014-05-27"[/b]
    }
]

Our fetch response which has the matching record:

Fetch Response: {
    endRow:1,
    affectedRows:0,
    totalRows:1,
    isDSResponse:true,
    invalidateCache:false,
    status:0,
    startRow:0,
    data:[
        {
            sysUpdatedOn:new Date(1401198575377),
            name:"xxx",
            [b]tdate:"2014-05-27",[/b]
            sysId:"cdad3f8f92a64c29bed4ea715225fe88",
        }
    ]
}

The Servelet response:

JSResponse: {
    endRow:1,
    affectedRows:0,
    totalRows:1,
    isDSResponse:true,
    invalidateCache:false,
    operationType:"fetch",
    status:0,
    startRow:0,
    data:[
        {
            sysUpdatedOn:new Date(1401198575377),
            name:"xxx",
            [b]tdate:"2014-05-27",[/b]
            sysId:"cdad3f8f92a64c29bed4ea715225fe88",
        }
    ]
}

Non-Working Scenario:
Advanced Criteria received on the Server:

criteria:[
    {
        fieldName:"tdate",
        operator:"equals",
        [b]value:new Date(1401163200000)[/b]
    }
]

Our fetch response which has the matching record:

Fetch Response: {
    endRow:1,
    affectedRows:0,
    totalRows:1,
    isDSResponse:true,
    invalidateCache:false,
    status:0,
    startRow:0,
    data:[
        {
            sysUpdatedOn:new Date(1401198575377),
            name:"xxx",
            [b]tdate:"2014-05-27",[/b]
            sysId:"cdad3f8f92a64c29bed4ea715225fe88",
        }
    ]
}

The Servelet response:

JSResponse: {
    endRow:0,
    affectedRows:0,
    totalRows:0,
    isDSResponse:true,
    invalidateCache:false,
    operationType:"fetch",
    status:0,
    startRow:0,
    data:[
    ]
}

[Isomorphic]

There's only one situation where the framework does filtering in Java on the server (as opposed to passing criteria to the underlying database engine): when you use includeFrom between two DataSources where we cannot do a SQL/JPA/Hibernate join, we do the equivalent of a join in memory, and this requires filtering.

As we've previously indicated, you may not want to use this feature at all. Doing a join in memory in Java isn't fast, no matter how efficient the implementation.

However if you continue to use this feature, then you do have to fulfill the basic contract of your DataSources - values for a field of type "date" or "datetime" should be Java Date instances, not Strings. And it definitely does not make sense to have the filtering mechanism try to deal with unexpected String values in date fields.

[stonebranch1]

OK. So when you say "you may not want to use this feature at all", are you referring to that we may not want to use the includeFrom fields since we have a custom datasource and just include those fields ourselves thereby removing the includeFrom attribute?

I am experimenting with doing that but just wanted to make sure that was what you meant.

I tried switching our code to send Date objects (instead of String) for the Date fields and it is nicely handled for different timezones for the testing I performed.

[Isomorphic]

What we mean is: when you use includeFrom but we have no ability to do a SQL join, we implement it as an in-memory Java join, because that's the only thing we can do.

We don't know what the implementation is behind your custom DataSource, but maybe you have a more efficient way of implementing the equivalent of includeFrom than doing it as an in-memory join in Java.

[stonebranch1]

Is there any setting I can use that puts the full lookup responsibility on my custom DataSource. That is, I will do the SQL join and return all the data as expected?

[Isomorphic]

There is no way that includeFrom can be implemented without using server-side filtering, so what you would need to do is simply not use the includeFrom property and have your DataSource return the field as a normal field.

[stonebranch2]

If you remove the includeFrom, and the field is displayed as a ComboBoxItem (using optionDataSource) and the displayName is different from the field name on the optionDataSource, the ComboBoxItem will blank display names.
The displayName cannot be the same as the field name in the optionDataSource because their is a name conflict.

[Isomorphic]

To clarify, you can still go ahead and have, for example, a foreignKey field where displayField is used to have the displayed value come from another field.

What's we're saying is that this other field will need to be populated by your custom logic, instead of using includeFrom to populate it.

If this is unclear, please revisit the docs for includeFrom. It explains the stored-vs-displayed value pattern that includeFrom helps you implement, but you don't need includeFrom to implement it.

[stonebranch2]

I believe I need to provide some clarification here.

"What's we're saying is that this other field will need to be populated by your custom logic, instead of using includeFrom to populate it."

> We understand this, and we already do this.

Here is our problem.

We have a form, backed by ds1, with a CombBoxItem for field ds2Option (see how field ds2Option is defined in datasource ds1 below).

<field name="ds2OptionName" type="text"></field>
<field name="ds2Option" foreignKey="ds2.id" displayField="ds2OptionName" type="text"></field>

When loading the form, we want the CombBoxItem to use the display name from the displayField ds2OptionName for the current ds2Option field value instead of performing a fetch to get that display name. Same when displaying the record in a list grid, we want to leverage the ds2OptionName field for the display name of ds2Option.

However, if CombBoxItem.getDisplayField() is "ds2OptionName", when clicking the picker on the CombBoxItem, if fetches all options and displays blank for the display names because ResultSet records have field "name" (i.e. ds2.name), not "ds2OptionName".

On our forms, we can deal with the various scenarios here, however, this introduces issues with respect to the filter builder as it will not know our intentions.

Thanks

[Isomorphic]

Use DataSourceField.displayField to configure which field in the *same record* that holds display values. In a FormItem with an optionDataSource, use FormItem.displayField to configure the field in *related records* that has the display value.

[stonebranch2]

Correct me if I am wrong, but from my observations, if you do not explicitly set the FormItem.displayField, it will use DataSourceField.displayField.

For example, in the FilterBuilder, where FormItem.displayField would not be set explicitly for this field, the select list is populated with all the appropriate options, but no display names (a bunch of blank options). If you select a record, the id will show up in the input.

If I fake out the response on the server side to send "ds2OptionName" as the field in the ResultSet, the display names appear. So under this scenario it seems to be using DataSourceField.displayField as the default displayField.

[Isomorphic]

There is still an API you can use to provide the formItem.displayField setting in the FilterBuilder (getValueFieldProperties).

Obviously, it's more convenient if you can make the fields have the same name.

We'll look at possible introducing another setting at the DataSource level so that it's not necessary to find a way to set FormItem.displayField in various contexts like this, but that convenience feature will probably be for the next version only.

[stonebranch2]

Thanks,

The getValueFieldProperties API worked for the FilterBuilder case.

Certainly, adding another setting at the DataSource level would be much appreciated.

Likewise, includeVia would benefit from allowing fully customized handling too.

Regards