Announcement

Collapse
No announcement yet.

PDF Template get relation field of relation

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • PDF Template get relation field of relation

    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:
    Code:
    {{#each subEntities}}
        {{name}}
        {{account.name}}
        ...
    {{/each}}
    But how do we display subSubEntities fields? I thought maybe this would work but it doesn't ....
    Code:
    {{#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

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

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

    PHP Code:

    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 $entityParams $params): stdClass
    {
    // Here you can load additional data for PDF;

    return (object) [

    ];
    }
    }
    ​ 
    espcrm

    telecastg
    Last edited by czcpf; 11-08-2022, 08:04 PM.

  • #2
    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.

    Reading this thread it seem like it is possible to get the grandchild (not sure if this correct term) of an entity: https://forum.espocrm.com/forum/deve...nd-grand-child (https://forum.espocrm.com/forum/developer-help/74511-querybuilder-join-child-and-grand-child) But that is for Query not for PDF Template Printing. Anyway

    Comment


    • #3
      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 Code:
      <?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 Code:
      <?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

      Code:
      {
          "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

      Code:
      {
          "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,

      Code:
      "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,
      Code:
      "select": "JSON_ARRAYAGG:(JSON_OBJECT:('id',{subSubEntity}.id,'name',{subSubEntity}.name))",
      will return a json array
      Code:
      "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:

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

      Comment


      • czcpf
        czcpf commented
        Editing a comment
        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
        telecastg commented
        Editing a comment
        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.

      • espcrm
        espcrm commented
        Editing a comment
        Did they just add that? I never seen it before today. Then again I don't check the section.

    • #4
      Thanks for posting this czcpf, and for bringing it to my attention espcrm. 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.

      Comment

      Working...
      X