Backend Calculated Fields when Fetching Entity

[blueprint]

Hello fellow Espo-ers!

I would like to know what the best practice would be to include a calculated field when fetching an Entity.

For example, say I have an Entity called `User` and there is a field called `taskCount` which represents the number of currently active Tasks assigned to them. This field could be of type `Integer` and in the `entityDefs` for the field, it would be marked as `notStorable`.

However, if I want to populate this field every time a `User` entity is fetched, where is the best place for this work to be done? Would it be in the Repository for the Entity or would it be more suited to live in the Service - given that it pulls data from another Entity?

I would also like to be able to sort by this calculated field too.

Thanks in advance!

[blueprint]

Ah, ok, I think with a bit of digging, I might well be onto something...

There are a few `notStorable` fields within the `Campaign` entity which calculate the number of emails sent, etc, for the campaign. This uses a `Loader` class to populate the values of these calculated fields which is then specified in the `recordDefs` metadata for Campaign.

I think this is my way in... except none of those calculated fields can be used as part of the search/sort mechanism....

[UPDATE]
This mechanism definitely doesn't allow filtering because of the `notStorable` attribute. With this attribute set, there is no column in the database table for the Entity and any backend filtering mechanism cannot execute a SQL statement because the column name doesn't exist.

[telecastg]

Hello @blueprint,

As you probably know, calculated values can be used in an SQL statement, but I assume that you are filtering using ORM and not raw SQL, and I don't know enough about the ORM pseudo language to tell you if that is also possible through that implementation.

If the case is that ORM does not allow the use of calculated values, and the only option is filtering using ORM (not raw SQL), perhaps an option, knowing that it is database design heresy, would be just to create an additional field with a before save hook that would recalculate the correct value every time that the table is updated.

[emillod]

I created few weeks ago something similar for my client and i'm using this in my EspoCRM. I created this in Services till 7.X. From version 7 i used FieldProcessing.
Here you have example: https://github.com/espocrm/espocrm/b...DataLoader.php

I didn't tried filtering/sorting, but it looks like the best way.

[czcpf]

Hello fellow Espo-ers!

I would like to know what the best practice would be to include a calculated field when fetching an Entity.

For example, say I have an Entity called `User` and there is a field called `taskCount` which represents the number of currently active Tasks assigned to them. This field could be of type `Integer` and in the `entityDefs` for the field, it would be marked as `notStorable`.

However, if I want to populate this field every time a `User` entity is fetched, where is the best place for this work to be done? Would it be in the Repository for the Entity or would it be more suited to live in the Service - given that it pulls data from another Entity?

I would also like to be able to sort by this calculated field too.

Thanks in advance!

This was one of the most confusing things for me when I began developing in Espo. I'm using 7.2, I think the following works in 7.X :

For simple calculated fields (like int, varchar, etc. define complex expressions something like as follows in your entityDefs)

"taskCount": {
"type": "int",
"notStorable": true,
"readOnly": true,
"isCustom": true,
"select": {
"select": "COUNT:(task.id)",
"leftJoins": [
[
"Task",
"task",
{
"task.assignedUserId:": "id",
"task.deleted": "0"
}
]
]
},
"order": {
"order": [
["COUNT:(task.id)","{direction}"]
],
"leftJoins": [
[
"Task",
"task",
{
"task.assignedUserId:": "id",
"task.deleted": "0"
}
]
]
}
},​ 

This gives you the field taskCount anywhere in ESPO application. Other approaches are more involved, and typically involve setting up class loaders like that defined here.

listLoadersForFields

listLoadersForRecords

See site/application/Espo/Modules/Crm/Resources/metadata/recordDefs for an example of how list loaders for records can be used

[mixerito]

This is most elegant way for using "taskCount" in detail or list view "anywhere in ESPO application".

But problem starts, when you want to use this field in Report as select column or even in Search Filters.

These queries end with Error 500 (SQLSTATE[HY000]: General error: 1111 Invalid use of group function)

I am having these issues in:
EspoCRM 7.2.7
Adv. pack 2.10.0
MySQL 8.0.29-21


Maybe it was solved in newer versions, or there is some workaround for these problems.
Please comment.


PS: When filtering by this value in Report, you must use "Having" clause, which makes sense.

[czcpf]

Hi, I'm not sure about the reports as I have no experience with them yet. I think for search filters you have to define the search criteria in your entity defs. See for example /entityDefs/User.json : position field syntax. That isn't exactly what you want though so if you figure out the correct syntax please post back.

[czcpf]

Hi, Can you check again please. I made an edit to the syntax above.

[czcpf]

Actually, this is not producing the correct count. I'm not entirely sure why this isn't working.