PDF Template get relation field of relation

[czcpf]

Hello,

Can someone please help me in defining the code snippet to display a 'nested' relation in PDF Template?

Example:

PDF Template Entity - MainEntity

Relations:

Entity	Foreign Link	Link Type	Link	Foreign Entity
MainEntity	mainEntity	One-to-Many	subEntities	SubEntity
SubEntity	subEntity	One-to-Many	subSubEntities	SubSubEntity
SubEntity	subEntities	Many-to-One	account	Account

The following PDF Template (based on MainEntity) will display subEntities Fields quite nicely:

{{#each subEntities}}
    {{name}}
    {{account.name}}
    ...
{{/each}}

But how do we display subSubEntities fields? I thought maybe this would work but it doesn't ....

{{#each subEntities}}
    {{#each subSubEntities}}
        {{name}}
    {{/each}}
    ...
{{/each}}

So how does one access subSubEntities ? It seems this was done using loadAdditionalFields method in custom\Espo\Services\MainEntity.php in prior versions of Espo and defining nonStorableField but this doesn't seem to be working in Espo 7.2 ?

I see there are two files in Espo 7.2 which seem to indicate this is how to load extra data in PDF Template entity but there are no examples on how to use these? Is this the proper way to do this in Espo 7.2 or is there another way using Services & nonStorable Fields or a different syntax for code snippet above?

\Modules\Crm\Resources\metadata\pdfDefs\Account.js on

{
    "dataLoaderClassNameList": [
        "Espo\\Modules\\Crm\\Classes\\Pdf\\Account\\ExampleDataLoader"
    ]
}​

\Modules\Crm\Pdf\Account\ExampleDataLoader.php

namespace Espo\Modules\Crm\Classes\Pdf\Account;

use Espo\Tools\Pdf\Data\DataLoader;
use Espo\Tools\Pdf\Params;
use Espo\ORM\Entity;

use stdClass;

class ExampleDataLoader implements DataLoader
{
public function load(Entity $entity, Params $params): stdClass
{
// Here you can load additional data for PDF;

return (object) [

];
}
}​ 

esforim

telecastg

[esforim]

Too technical for me, you can try reading this thread maybe you can figure it out somehow.

I once ask a similar question. Or is it the same question you asking, I can't tell.

https://forum.espocrm.com/forum/general/74567-pdftemplate-grandchild-relationship

[czcpf]

After reviewing various approaches, I came up with this solution (Espo 7.2)

Below is a small tutorial on how to accomplish access of a grandchild properties in pdfTemplate of grandfather through an additional field definition in the grandchild entity. Since this is a field definition, the grandchild properties should also be accessible in other areas of espoCRM.

The Setup

PDF Template get relation field of relation

PDF Template Entity - MainEntity

Relations:

Entity	Foreign Link	Link Type	Link	Foreign Entity
MainEntity	mainEntity	One-to-Many	subEntities	SubEntity
SubEntity	subEntity	One-to-Many	subSubEntities	SubSubEntity
SubEntity	subEntities	Many-to-One	account	Account

Step 1:

Create the following custom ORM converter functions. These functions can then be used throughout EspoCRM any time we wish to define a jsonObject, jsonArray, or a combination thereof to select an entities's relational attributes.

Espo\Custom\Classes\ORM\QueryComposer\Part\Functio nConverters\Json_ArrayAgg.php

<?php
namespace Espo\Custom\Classes\ORM\QueryComposer\Part\FunctionConverters;

use Espo\ORM\QueryComposer\Part\FunctionConverter;

class Json_ArrayAgg implements FunctionConverter
{
public function convert(string ...$argumentList): string
{
    return 'JSON_ARRAYAGG(' . implode(', ', $argumentList) . ')';
}
}​

Espo\Custom\Classes\ORM\QueryComposer\Part\Functio nConverters\Json_Object.php

<?php

namespace Espo\Custom\Classes\ORM\QueryComposer\Part\FunctionConverters;

use Espo\ORM\QueryComposer\Part\FunctionConverter;

class Json_Object implements FunctionConverter
{
public function convert(string ...$argumentList): string
{
    return 'JSON_OBJECT(' . implode(', ', $argumentList) . ')';
}
}​

Step 2:

Create the following json file to tell EspoCRM about these custom Function definitions.

Espo\Custom\Resources\metadata\app\orm.json

{
    "functionConverterClassNameMap_Mysql": {
        "JSON_ARRAYAGG": "Espo\\Custom\\Classes\\ORM\\QueryComposer\\Part\\FunctionConverters\\Json_ArrayAgg",
        "JSON_OBJECT": "Espo\\Custom\\Classes\\ORM\\QueryComposer\\Part\\FunctionConverters\\Json_Object"
    }
}​

Step 3:

Modify the entity definition of {subEntity}.json to include a calculated field {subSubEntitiesProps}. Note, by definition of the problem setup, {subEntity}.json should already have a linkMultiple field called {subSubEntities} and a "hasMany" link called {subSubEntities}

As shown below,

*********************** BEGIN CUSTOM FIELD *****************************
*********************** END CUSTOM FIELD *****************************

are just placeholders to identify the specific code added.


Espo\Custom\Resources\metadata\entityDefs\{subEntity}.json

{
    "fields": {​
        ...
        "{subSubEntities}": {
           "type": "linkMultiple",
           ...
        },
        *********************** BEGIN CUSTOM FIELD *****************************
        "{subSubEntitiesProps}": {
            "type": "jsonArray",
            "notStorable": true,
            "isCustom": true,
            "disabled": true,
            "select": {
                "select": "JSON_ARRAYAGG:(JSON_OBJECT:('id',{subSubEntity}.id,'name',{subSubEntity}.name),...'{key}','{subSubEntity}.{key})",
                    "leftJoins": [
                        [
                            "{SubSubEntity}",
                            "{subSubEntity}",
                            {
                                "{subSubEntity}.{subEntity}Id:": "id"
                            }
                        ]
                   ]
           }
        },​
        *********************** END CUSTOM FIELD *****************************
        ...
    },
    "links": {
        ...
        "{subSubEntities}": {
            "type": "hasMany",
            "foreign": "{subEntity}",
            "entity": "{SubSubEntity}",
            ...
        },​
        ...
    }
}

Explanation,

"select": "JSON_ARRAYAGG:(JSON_OBJECT:('id',{subSubEntity}.id,'name',{subSubEntity}.name),...'{key}','{subSubEntity}.{key})",

Here we are telling espoCRM to use our two custom functions in combination to return a JSON_ARRAY of JSON_OBJECTS with key/value pairs.

For example,

"select": "JSON_ARRAYAGG:(JSON_OBJECT:('id',{subSubEntity}.id,'name',{subSubEntity}.name))",

will return a json array

"subSubEntitiesProps":[{"id":"63172fe24db26b1f7","name":"name"},{"id": "63 17300b9878a37d6","name":"name2"}]

Step 4:

Create a pdf Template using {MainEntity}

Then, we can access subSubEntitiesProps in code editor as follows:

{{#each subEntity​}}
    <p>id:{{id}}, name:{{name}</p>
    {{#each subSubEntitiesProps}}
       <p>id:{{id}}, name:{{name}}</p>
    {{/each}}
{{/each}}​

[czcpf]

I hope that more people get involved in this project and add to that wiki. It is taking me quite a while to understand this framework so I plan to post examples as I encounter various scenarios.

[telecastg]

Excellent job czcpf !

There's a sub-forum https://forum.espocrm.com/forum/deve...oper-tutorials dedicated to tutorials for developers and I think it would be the perfect space for contributions like this one.

[esforim]

Did they just add that? I never seen it before today. Then again I don't check the section.

[migrator]

Thanks for posting this czcpf, and for bringing it to my attention esforim. I just came along, and expect to be building up my knowledge on the framework and implementations, and contribute how I can down the line. I look forward to going through your solutions.