PivotTable does not support nested properties in datasource

Support
#1

Hi

PivotTable does not supported nested properties.

java.lang.IllegalArgumentException: Property 'invoice.date' not found in busy$CustomerInvoiceSimpleLine
	at com.haulmont.chile.core.model.impl.MetaClassImpl.getPropertyNN(MetaClassImpl.java:107)
	at com.haulmont.charts.gui.pivottable.model.gson.PivotDataItemsSerializer.addProperty(PivotDataItemsSerializer.java:68)

Indeed PivotDataItemsSerializer uses getPropertyNN instead of getPropertyPath.

This is annoying because our Sales screen uses the same Datasource for a Table and a Pivot, and Table supports them. In fact they are supported quite everywhere in CUBA which is nice. But not in Pivot :frowning:

Is there a way to override this class in some way ? Or another workaround ?

I thought defining a SaleEntry POJO and using jqpl NEW construct:
SELECT NEW SaleEntry(s.invoice.date, […]) FROM app$Sale s.

But it is not supported and if I understand correctly using JPA JPQL directly bypasses CUBA security.

Table: ok

        <columns>
                        <column id="invoice.date"/>

Pivot: nok

 <chart:properties>
                        <chart:property name="invoice.date" localizedName="Date"/>`

Mike

#3

Hi,

Could you clarify what CUBA Platform version do you use? This issue was resolved PL-8802.

#5

I tried with 6.8.5 and 6.8.7 but I may have a lead : this is specific to dates.

Attached a project allowing to reproduce the issue, there are 2 pivot tables invoice-line-browse.xml and invoice-line-browse.xml.sav. The 2nd is the one with the issue.

Both pivot sum invoice lines and try to retrieve info from the invoice. Using property ‘invoice.customer’ works, but using property ‘invoice.date’ does not.

This is due to PivotDataItemsSerializer specific code when the property is a date (code below). This is a specific case that I think is not covered by https://youtrack.cuba-platform.com/issue/PL-8802.

EDIT : to be more precise, the issue is on a date property accessed through an association, a date property at 1st level works

 protected void addProperty(JsonObject jsonObject, String property, Object value,
                               PivotJsonSerializationContext context, DataItem item) {
        Object formattedValue;
        if (value == null) {
            formattedValue = StringUtils.EMPTY;
        } else if (value instanceof Entity) {
            formattedValue = InstanceUtils.getInstanceName((Instance) value);
        } else if (value instanceof EnumClass) {
            formattedValue = messages.getMessage((Enum) value);
        } else if (value instanceof Date) {
            String formatStr;
            if (item instanceof EntityDataItem) {
                EntityDataItem entityItem = (EntityDataItem) item;
                MetaClass metaClass = metadata.getClassNN(entityItem.getItem().getClass());
                MetaProperty metaProperty = metaClass.getPropertyNN(property);

                Class type = metaProperty.getRange().asDatatype().getJavaClass();
                if (type.equals(java.sql.Date.class)) {
                    formatStr = messages.getMainMessage("dateFormat");
                } else if (type.equals(Time.class)) {
                    formatStr = messages.getMainMessage("timeFormat");
                } else {
                    formatStr = messages.getMainMessage("dateTimeFormat");
                }
            } else {
                formatStr = messages.getMainMessage("dateTimeFormat");
            }

            SimpleDateFormat dateFormat = new SimpleDateFormat(formatStr);
            formattedValue = dateFormat.format((Date) value);
        } else if (value instanceof Boolean) {
            formattedValue = BooleanUtils.isTrue((Boolean) value)
                    ? messages.getMainMessage("boolean.yes")
                    : messages.getMainMessage("boolean.no");
        } else {
            formattedValue = value;
        }

        jsonObject.add(context.getLocalizedPropertyName(property), context.serialize(formattedValue));
    }

testcuba.zip (147.1 KB)

#6

You’re right, it’s a bug. Thank you for reporting the problem, I’ve created a GitHub issue.

#7

Ok thanks Gleb. It seems small, do you think it can be patched on 6.8.9 for instance ?

I spend quite some time thinking of a workaround, but nothing simple has come to mind, and I don’t want to denormalize nearly each date in our analytics model just for that

#8

Hi,

The issue will be resolved in the very next bug fix release 6.8.9.

Regards,
Gleb

#9

Great news, thanks Gleb