Limit a multi-enum field options and values based on another multi-enum field values

[telecastg]

This tutorial is applicable for Espo 7.4.3

Background Information:

In our Chats extension, an administrator can select the teams that can participate in a Team Chat and the roles linked to such teams, that can read and write new posts in the Team Chat.

The entity name is Pinboard, the multi-enum field holding the team values is participantTeams and the multi-enum holding the role values is readWriteRoles.

When the field participantTeams is loaded or changes, the options available for selection at readWriteRoles are limited to the values of roles that are associated with one or more of the selected teams in participantTeams.

Additionally, existing roles in readWriteRoles that are not linked to one of the teams existing in participantTeams are removed.

Steps:

1) Create custom view for participantTeams:

client/modules/chats/src/views/settings/fields/team-list.js

define('chats:views/settings/fields/teams-list', ['views/fields/multi-enum'], function (Dep) {

    return Dep.extend({
        
        controllingAttribute: false,
        
        setup: function () {
            Dep.prototype.setup.call(this);    
            this.controllingAttribute = this.getMetadata().get('entityDefs.'+this.model.urlRoot+'.fields.'+this.name+'.controllingAttribute') || this.controllingAttribute || false;
            if(this.controllingAttribute) {
                this.listenTo(this.model, 'change:'+this.controllingAttribute, ()=>{
                    this.setupOptions();
                });  
            }
        },
        
        setupOptions: function () {
            // initialize the translatedOptions object
            this.optionsLabels = {};
            this.params.options = [];      
            if(this.controllingAttribute) {
                // get existing selected teams
                const data = {
                    teams:this.model.get(this.controllingAttribute)              
                };
                if(this.model.get(this.controllingAttribute).length > 0) {
                    Espo.Ajax.postRequest('PinboardAdmin/action/fetchTeamsLinkedRoles',data).then(
                        function (response) {
                            response.sort((a, b) => (a.name > b.name) ? 1 : -1);
                            this.params.options = [];
                            response.forEach((optionObj)=>{
                                this.optionsLabels[optionObj.id] = optionObj.name;
                            });
                            // eliminate dependent values that do not meet the controlling field criteria
                            let existingValues = this.model.get(this.name) || [];
                            let updatedValues =[];
                            existingValues.forEach((item)=>{
                                const searchIndex = response.findIndex((targetObj) => targetObj.name===item);
                                if(searchIndex > -1) {
                                    updatedValues.push(item);
                                }                            
                            });
                            this.model.set(this.name,updatedValues);
                            // update the field display after downloading the options from the database
                            this.reRender();
                            // load the translatedOptions attribute
                            this.translatedOptions = this.optionsLabels;
                        }.bind(this)
                    );
                } else {
                    this.model.set(this.name,[]);
                    this.params.options = [];                
                    this.reRender();
                }
            } else {
                Espo.Ajax.getRequest('Team/action/list').then(
                    function (fetchedData) {
                        fetchedData.list.sort((a, b) => (a.name > b.name) ? 1 : -1);
                        this.params.options = [];
                        let t = {};
                        fetchedData.list.forEach((optionObj)=>{  
                            t[optionObj.id] = optionObj.name;
                            this.params.options.push(optionObj.id);  
                        });
                        this.translatedOptions = Espo.Utils.clone(t);
                        // update the field display after downloaing the options from the database
                        this.reRender();
                    }.bind(this)
                );                
            }
        }
        
    });
});

2) Create custom view for readWriteRoles:

client/modules/chats/src/views/settings/fields/pinboard-roles-list.js

define('chats:views/settings/fields/pinboard-roles-list', ['views/fields/multi-enum'], function (Dep) {

    return Dep.extend({
        
        controllingAttribute: 'participantTeams',
        
        setup: function () {
            Dep.prototype.setup.call(this);    
            this.controllingAttribute = this.getMetadata().get('entityDefs.'+this.model.urlRoot+'.fields.'+this.name+'.controllingAttribute') || this.controllingAttribute || false;
            if(this.controllingAttribute) {
                this.listenTo(this.model, 'change:'+this.controllingAttribute, ()=>{
                    console.log('chats:views/settings/fields/pinboard-roles-list setup() #43 controlling attribute change',);
                    this.setupOptions();
                });  
            }
        },
                
        setupOptions: function () {    
            // initialize the translatedOptions object
            this.optionsLabels = {};
            this.params.options = [];      
            if(this.controllingAttribute) {
                // get existing selected teams
                const data = {
                    teams:this.model.get(this.controllingAttribute)              
                };
                if(this.model.get(this.controllingAttribute).length > 0) {
                    // fetch the dependent entities ids that are linked to the exisitng controlling entities ids
                    Espo.Ajax.postRequest('PinboardAdmin/action/fetchTeamsLinkedRoles',data).then(
                        function (response) {
                            response.sort((a, b) => (a.name > b.name) ? 1 : -1);
                            this.params.options = [];
                            response.forEach((optionObj)=>{
                                this.optionsLabels[optionObj.id] = optionObj.name;
                                this.params.options.push(optionObj.id);  
                            });
                            // eliminate dependent values that do not meet the controlling field criteria
                            let existingValues = this.model.get(this.name) || [];
                            if(existingValues.length > 0) {                            
                                let updatedValues =[];                                
                                existingValues.forEach((item)=>{                                    
                                    if(this.params.options.includes(item)){
                                        updatedValues.push(item);
                                    }
                                });
                                // update the field display after downloading the options from the database
                                this.model.set(this.name,updatedValues);
                            }                            
                            // load the translatedOptions attribute
                            this.translatedOptions = this.optionsLabels;  
                            this.reRender();
                        }.bind(this)
                    );
                } else {
                    this.model.set(this.name,[]);
                    this.params.options = [];                
                    this.reRender();
                }
            } else {
                Espo.Ajax.getRequest('Role/action/list').then(
                    function (fetchedData) {
                        fetchedData.list.sort((a, b) => (a.name > b.name) ? 1 : -1);
                        this.params.options = [];
                        let t = {};
                        fetchedData.list.forEach((optionObj)=>{  
                            t[optionObj.id] = optionObj.name;
                            this.params.options.push(optionObj.id);  
                        });
                        this.translatedOptions = Espo.Utils.clone(t);
                        // update the field display after downloaing the options from the database
                        this.reRender();
                    }.bind(this)
                );                
            }
        }
        
    });
});
​​

3) Create back-end classes to serve the options values when invoked by the front-end views:

Espo/Modules/Chats/Controllers/PinboardAdmin.php

namespace Espo\Modules\Chats\Controllers;

use Espo\{
    Entities\User
};

use Espo\Core\{
    Container,
    DataManager,
    Api\Request,
    InjectableFactory
};

use stdClass;

use Espo\Modules\Chats\Services\Pinboard as Service;

class PinboardAdmin
{
    protected $user;
    protected $container;
    protected $dataManager;
    protected $injectableFactory;

    public function __construct(User $user, Container $container, DataManager $dataManager, InjectableFactory $injectableFactory)
    {
        $this->user = $user;
        $this->container = $container;
        $this->dataManager = $dataManager;        
        $this->injectableFactory = $injectableFactory;
        $this->checkControllerAccess();
    }
    
    protected function checkControllerAccess()
    {
        if (!$this->user->isAdmin()) {
            throw new Forbidden();
        }
    }
    
    public function postActionFetchTeamsLinkedRoles(Request $request): array
    {
        $data = $request->getParsedBody();
        $teams = $data->teams;
        $queryResult = $this->injectableFactory->create(Service::class)->getTeamsRelatedRoles($teams);
        return $queryResult;
    }
          
} 

Espo/Modules/Chats/Services/Pinboard.php

namespace Espo\Modules\Chats\Services;

use PDO;

class Pinboard extends Record
{

    public function getTeamsRelatedRoles($teams): Array
    {
        $sqlString = 'SELECT role.name As `name`, role.id As `id` FROM `role` ';
        $sqlString.= 'INNER JOIN `role_team` ON role.id = role_team.role_id ';
        $sqlString.= 'INNER JOIN `team` ON team.id = role_team.team_id ';  
        $sqlString.= 'WHERE team.id IN (';                
        for ($i = 0; $i >count($teams); $i++){
            if($i >0) {
                $sqlString.= ',"'.$teams[$i].'"';
            } else {
                $sqlString.= '"'.$teams[$i].'"';                
            }            
        }
        $sqlString.= ') GROUP BY role.id, role.name';
        $data = $this->entityManager
            ->getSqlExecutor()
            ->execute($sqlString)
            ->fetchAll(PDO::FETCH_ASSOC);        
        return $data;
    }  
} 

4) Link the front-end view files to the entity fields in metadata:

Espo/Modules/Chats/Resources/metadata/entityDefs/Pinboard.json

{
    "fields": {

        "participantTeams": {
            "type": "multiEnum",
            "storeArrayValues": true,
            "isSorted": true,
            "displayAsLabel": true,
            "options": null,
            "allowCustomOptions": false,
            "style": {},
            "isCustom": true,
            "view": "chats:views/settings/fields/teams-list",
            "required": true,
            "controllingAttribute": null
        },
        "readWriteRoles": {
            "type": "multiEnum",
            "storeArrayValues": true,
            "isSorted": true,
            "displayAsLabel": true,
            "options": null,
            "allowCustomOptions": false,
            "style": {},
            "isCustom": true,
            "view": "chats:views/settings/fields/pinboard-roles-list"
        },
    }
}​

[esforim]

I assume the conditional Option doesn't work and hence the reason you have to write this code?

Currently it work for Single-Enum.

[yuri]

It has an SQL Injection vulnerability in getTeamsRelatedRoles method. An attacker who has access to CRM, can do anything with database. Please use ORM or PDO with bound params.

[telecastg]

Thanks for the tip. I will look into those two alternatives.

Just for my education, could you elaborate on the SQL injection vulnerability in the getTeamsRelatedRoles method ?

The method does not receive any input from an external source. It can only receive an array of record ids that are provided as immutable options in a multi-enum field.

UPDATE: I set the "allowCustomOptions" as false in both multi-enum fields ("participantFields" and "readWriteRolws") to make sure that only preset options values can be entered. Thanks again for the tip.

[item]

Hi telecastg
if i understand, i translate so you sql :

SELECT role.name As name, role.id As id FROM role
JOIN role_team ON role.id = role_team.role_id
JOIN team ON team.id = role_team.team_id
WHERE team.id IN ('64522da53ab382ad3', '6440853b20e5c34fc')
GROUP BY role.id, role.name
​ 

so below do the same :
for me you just need to get teamIds as it's a array and replace my hard coded [teamId]

use Espo\Entities\Team;
use Espo\Entities\Role;
use Espo\Entities\User;
use Espo\ORM\Query\Part\Condition as Cond;
use Espo\ORM\Query\SelectBuilder as QueryBuilder;​

        $query = QueryBuilder::create()
            ->select(['id', 'name', 'createdAt'])
            ->from(Role::ENTITY_TYPE)
            ->leftJoin('roleTeam', 'entityTeam', [
                'entityTeam.roleId:' => 'id',
                'entityTeam.deleted' => false,
            ])
            ->where([
                
                    'entityTeam.teamId' => ['64522da53ab382ad3', '6440853b20e5c34fc'],
                
            ])

            ->build();

        $pdoStatement = $this->em
            ->getQueryExecutor()
            ->execute($query);

        $rowList = $pdoStatement->fetchAll(\PDO::FETCH_ASSOC);

        $this->log->error( count($rowList) );

        foreach($rowList as $row){
            $this->log->error( $row['createdAt'] ."=>"  .$row['id'] ."=>" .$row['name'] );
        }​ 

[telecastg]

thanks item I appreciate the translation.

[item]

Hi @telecastg,
you know, my translation is my english
the function query work for you ?
if not, i search more

[item]

Hi @telecastg,

i have find (learn) 2 function very helpfull for you (for me too)

$teamIdList = $contact->getLinkMultipleIdList('teams');

$entity->setLinkMultipleIdList('teams', $user->getLinkMultipleIdList('teams'));

for your case, i think is the best choose.

Best Regards