How to adapt the field "select" parameters in entityDefs to work in the Espo 7.4

[telecastg]

We have a Service Ticket entity which has a field "ageing" where the system automatically calculates how many weeks has the Service Ticket been open.

Prior to Espo 7.4 the Service Ticket entityDefs json file looked like this:

"fields" {
        "ageing" : {
            "type": "int",
            "select": "TIMESTAMPDIFF(WEEK, service_ticket.created_at, now())",
            "readOnly": true,
            "isCustom": true
        },
}

This feature worked fine, but when we upgraded to 7.4, we immediately got this ERROR 500 message originated from the new DefaultSqlExecutor class:

ERROR: (42S22) SQLSTATE[42S22]: Column not found: 1054 Unknown column 'service_ticket.created_at' in 'field list'

And after some hours of searching through the code base and testing, we were able to figure out that the new way to invoke this functionality was as follows:

"fields" {
        "ageing" : {
            "type": "int",
            "select": {
                "select": "TIMESTAMPDIFF_WEEK:(createdAt, NOW:())"
            },
​            "readOnly": true,
            "isCustom": true
        },
}​

To see a list of all "SQL" functions available in the ORM check this script: https://github.com/espocrm/espocrm/b...ctions.php#L30

To see how these functions are implemented for MySql, Maria and similar databases, see this script: https://github.com/espocrm/espocrm/b...mposer.php#L57

Too see how these functions are implemented for Postgresql see this script: https://github.com/espocrm/espocrm/b...mposer.php#L45

[yuri]

I'm not sure why the old way stopped working in your case. But the new way (actually it was there for years) is definitely better as it's independent on database system (will work in Postgresql).

[espcrm]

Hi telecastg, I got a similar column error issue. I 'fixed' it by removing the relationship... which I want to get it back by the next time I add a new Record to it.

Did you figure out how to fix the error?

https://forum.espocrm.com/forum/general/90853-v7-4-3-bug-error-and-discussion#post90900

[yuri]

There reason why the first expression does not work is that now the main table is aliased. In this case it's serviceTicket alias. It was needed to be able to optimize some queries.