notStorable field which is count of relation

[czcpf]

Hello,

Simply trying to have a notStorable field "totalTaskCount" which counts the number or related records of an existing link for this entity called "complianceTasks".

APPROACH 1 does not work, which attempts to use the "select" object syntax in entityDefs I see in some of the core files.

So Instead, I went with APPROACH 2 and used a loaders to set the count, which does work but I don't know what to put for "order" to be able to actually order by this field in list view. It is just a simple integer.



APPROACH 1: (NOT WORKING)
entityDefs snippet

{
"fields": {​

"complianceTasks": {
"type": "linkMultiple",
"layoutDetailDisabled": false,
"layoutMassUpdateDisabled": false,
"layoutListDisabled": false,
"noLoad": false,
"importDisabled": false,
"exportDisabled": false,
"customizationDisabled": false,
"isCustom": true
},​
"totalTaskCount": {
"name": "totalTaskCount",
"label": "Flags",
"type": "int",
"notStorable": true,
"isCustom": true,
"select": {
"select": "COUNT:(complianceTask.id)",
"leftJoins": [
[
"ComplianceTask",
"complianceTask",
{
"complianceTask.complianceReportId:": "id",
"complianceTask.deleted": false
}
]
]
},
"order": {
"order": [
["COUNT:(complianceTask.id)", "{direction}"]
],
"leftJoins": [
[
"ComplianceTask",
"complianceTask",
{
"complianceTask.complianceReportId:": "id",
"complianceTask.deleted": false
}
]
]
}
}
},​
},
"links": {
"complianceTasks": {
"type": "hasMany",
"foreign": "complianceReport",
"entity": "ComplianceTask",
"audited": false,
"isCustom": true
}
},​

APPROACH 2
-[listLoader] (Works but how to order?)

selectDefs snippet

{
"readLoaderClassNameList": [
"__APPEND__",
"Espo\\Custom\\Classes\\FieldProcessing\\Complianc eReport\\ComplianceTaskCountLoader"
],
"listLoaderClassNameList": [
"Espo\\Custom\\Classes\\FieldProcessing\\Complianc eReport\\ComplianceTaskCountLoader"
]
}​

entityDefs snippet

"totalTaskCount": {
"name": "totalTaskCount",
"label": "Flags",
"type": "int",
"notStorable": true,
"isCustom": true
}​

namespace Espo\Custom\Classes\FieldProcessing\ComplianceReport;

use Espo\Custom\Entities\ComplianceTask;
use Espo\ORM\Entity;

use Espo\Core\Utils\Metadata;

use Espo\Core\{
FieldProcessing\Loader,
FieldProcessing\Loader\Params,
ORM\EntityManager,
};

/**
* @implements Loader<\Espo\Custom\Entities\ComplianceReport>
*/
class ComplianceTaskCountLoader implements Loader
{
/**
* @var string[]
*/

private EntityManager $entityManager;

private Metadata $metadata;

public function __construct(EntityManager $entityManager, Metadata $metadata)
{
$this->entityManager = $entityManager;
$this->metadata = $metadata;

}

public function process(Entity $entity, Params $params): void
{
if (
$params->hasSelect() &&
!in_array('totalTaskCount', $params->getSelect() ?? [])
) {
return;
}


$totalTaskCount = $this->entityManager
->getRDBRepository($entity->getEntityType())
->getRelation($entity, 'complianceTasks')
->count();

$entity->set('totalTaskCount', $totalTaskCount?:0);

}
}​ 

[yuri]

Hi, IIRC I wrote to you in another thread that the second approach does not support order.

[yuri]

You can create a custom complex expression function that will return a whole SELECT sub-query calculating count for a current row. Then use this expression in "select" parameter.

[czcpf]

You can create a custom complex expression function that will return a whole SELECT sub-query calculating count for a current row. Then use this expression in "select" parameter.

Ok, I still don’t understand why Approach 1 syntax doesn’t return the correct count. That same syntax (albeit without the COUNT expression) is all over the code base. Does select in that approach not work on a collection of rows or something?

if I write a custom complex expression as you suggest, what do I put for the order part ?

If you have time, could you please post a code snippet example of using approach 1 with a custom complex expression that counts the number of related entries as well as the syntax for order ? There are several posts on this from various users who have asked similar questions and it would be wonderful to have a working example.