Null selection with date field using sqlStorageStrategy="number"

[jay.l.fisher]

We have a lot of legacy database tables that store dates as numbers. Using type="date" sqlStorageStrategy="number" sqlDateFormat="yyyyMMdd" works great, except when trying to create a filter to select records where the date field in the table is zero (equivalent to null).

Since the field is defined as type="date" the FilterBuilder wants a date or range to be entered as criteria. Selecting "is null" for the criteria operator results in "dateField IS NULL" being included in the where clause instead of "dateField = 0".

Shouldn't sqlStorageStrategy="number" indicate that the comparison should be =0 instead of IS NULL?

Is there some other way to get the result we're after?

[Isomorphic]

We would have to know the DB column is not nullable to be sure that isNull can be safely converted to a check for 0, and even then it could be argued it should just be treated as invalid criteria..

What you could do is used dataSourceField.customCriteriaExpression to handle this case.

[jay.l.fisher]

Thanks for the ultra-quick reply!

Is there a way to detect when "is null" or "is not null" is the criteria operator and generate =0 or !=0, but in other cases let the default condition be generated?

[Isomorphic]

You can use the $criteriaOperator variable mentioned in the docs to detect which operator is in use, however, currently there would not be a way to just have the default SQL used. We're looking at adding a variable that would make this more convenient.

However the simplest thing to do might be to just transform the AdvancedCriteria so that you turn an isNull Criterion into a check for 0 for these fields.

[jay.l.fisher]

I'm trying to do this in one spot on the server side but am not having much luck. I'm using DSRequest.getAdvancedCriteria and then getFieldCriterions("receivedOn") to get the criterion, hoping that I can then just call setOperatorId and setValue to transform the criteria. But the call to get getFieldCriterions is returning an empty list.

The AdvancedCriteria object looks like this (using getCriteriaAsMap) ...

{criteria=[{fieldName=receivedOn, operator=isNull}], operator=and, _constructor=AdvancedCriteria}

But calling getFieldCriterions("receivedOn") still returns an empty list.

[Isomorphic]

That approach should work fine, except note that you should call dsRequest.setAdvancedCriteria() with the modified criteria once you've made your changes.

As far as getFieldCriterions() returning nothing, if can you show some of the code you're using to do this, we can comment on that.

[jay.l.fisher]

Here is the entire method. In my previous post I showed the AdvancedCriteria object contents. Running in debug I was able to stop at that point and call getFieldCriterions("receivedOn") and confirm that is returning an empty list, while a Criterion with that field name is clearly in the criteria object.

private void transformCriteria(DSRequest req) {
	AdvancedCriteria ac = req.getAdvancedCriteria();
	if (ac==null)
		return;
	for (Object dsField : getFields()) {
		DSField field = (DSField) dsField;
		if ("date".equals(field.getType()) 
				&& "number".equals(field.getProperty("sqlStorageStrategy"))) {
			for (Criterion criterion : ac.getFieldCriterions(field.getName())) {
				try {
					if ("isNull".equals(criterion.getOperatorId())) {
						criterion.setOperatorId("equals");
						criterion.setValue("0");
						log.debug("Converted isNull date criteria for field "+field.getName()+" in "+getName());
					} else if ("notNull".equals(criterion.getOperatorId())) {
						criterion.setOperatorId("notEqual");
						criterion.setValue("0");
						log.debug("Converted notNull date criteria for field "+field.getName()+" in "+getName());
					}

				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
}

[Isomorphic]

May seem a silly thing to ask, but, have you checked that you are actually calling getFieldCriterions() rather than bailing out earlier -no field of the specified type, or no fields, or something like that. We recall you guys are using both DynamicDataSourceGenerators and autoDeriveSchema, so..

[jay.l.fisher]

I'm running in debug and stepping through statement by statement so I know the method is executing.

Evaluating ac.getFieldCriterions("receivedOn") returns an empty list, but ac.getCriteriaAsMap() shows a criterion with fieldName=receivedOn.

criteria=[{fieldName=receivedOn, operator=isNull}], operator=and, _constructor=AdvancedCriteria}

[Isomorphic]

Found the problem - method was working fine for most uses but not the isNull operator because it doesn't have a "value" field.

Fixed for tomorrow's 4.0 build, fix for 3.1 should be coming soon.