Reference: Espo GUI - script map guide, where can I change something ?

[telecastg]

What is the program flow for an API call ?

API calls invoke a back end controller which generally then calls for a service class to execute the requested action.

API call > router > back end controller > service class

To see an example check this post. https://forum.espocrm.com/forum/deve...3408#post63408

[telecastg]

How to modify in an "upgrade safe" way the script views/record/search.js



Check this post for detailed instructions to incorporate custom code changes to the search class without changing any core scripts

https://forum.espocrm.com/forum/developer-help/70737-how-to-customize-the-search-js?p=70782#post70782

[telecastg]

How are layouts used to render an entity detail display:

Layouts are json files created through the Administration > Layout Manager facility and tell Espo how to order and render the entity's fields in different display modes.

For the "detail" display mode, a record (entity) is rendered in one or more panels, each panel containing the fields and organization of these fields specified in the layout file like this:


If an entity does not have any detail layout associated with it, like in the case of a new custom entity, Espo will assume a detail layout that contains only the "name" attribute occupying one half of the single row.

The above information is referred to as "simpleLayout" in the client/src/views/record/detail.js class.

The "simpleLayout" is processed by the above class through the method getGridLayout() to create a complete specification of not just which fields are displayed where but also which field view classes are to be used to render each field in detail or edit modes, the document element where each field is to be rendered and the model providing the data bound to each field.

The client/src/views/record/detail.js class takes then the output from the method getGridLayout() and uses it to render the record (entity) in the "middleView" class which is rendered in the "middle" html div where the record's details are displayed in the screen.

[telecastg]

How is Layout data retrieved by the Layout Manager ?

Layout data is stored in json files located under the affected entity's module namespace.

For example for the "Call" entity, which is defined under the module "Crm", the various layout files (one for each type, like "detail", "detailSmall", "list", "listSmall", etc) are located inside the directory application/Espo/Modules/Crm/Resources/layouts/Call/

Therefore the detail layout data is stored in the file application/Espo/Modules/Crm/Resources/layouts/Call/detail.json



When a User clicks on the Administration > Layout Manager > Calls > Detail menu option the following actions take place inside Espo

1) Front end script client/src/layout-manager.js looks for the layout data in cache and if not found, makes an API (Ajax get request) call: Call/layout/detail

2) Json script application/Espo/Resources/routes.json interprets the instruction and invokes method getActionRead() at Controller class application/Espo/Controllers/Layout.php providing two parameters: $scope = "Call", $name = "detail".

3) Controller class application/Espo/Controllers/Layout.php invokes method getForFrontend($scope, $name) at Service class application/Espo/Services/Layout.php

4) Service class application/Espo/Services/Layout.php checks permissions and team membership to determine if the user request requires the retrieval of a specific layout file or the default for all users. If a specific layout is required the Service class invokes method get($scope, $name) at Utils class application/Espo/Core/Utils/Layout.php , otherwise invokes method getDefault($scope, $name) at the same Utils class.

5) Utils class application/Espo/Core/Utils/Layout.php determines the correct path where the requested layout file exists, reads the file and returns the data as a JSON object to the Service class which passes the same to the Controller class which then passes it to the front end script.

[item]

Hello @telecastg,
i write here because i think it's here

out-of-box, we have layout "listForAccount" and "listForContact".. it's for bottom panel,
where we can create this kind of layout for custom entity ? witch files to modify ?
sample :
Patient -> bottomPanel = contacts => listForPatient
Institution -> bottomPanel => contacts => listForInstitution

so i need bottomPanel to have differents field if it's Patient or Institution.

Regards

[telecastg]

Hello @item,

I think that the easiest way would be to include all fields in one layout and then use dynamic handler or dynamic logic to hide or display fields based on the type of subject (Patient or Institution)

To use a custom layout for different subjects, instead of manipulating one layout through dynamic logic or dynamic handler, would require some coding, I'll write the instructions in a separate post so it's easier to find by other users that have the same requirements.

[telecastg]

What is the program flow to render a single record(entity) in Espo ?

Following up on the previous post "How are new records created ?" https://forum.espocrm.com/forum/deve...1398#post61398 the view class client/src/views/record/detail.js is invoked to render the panels and fields which comprise the display to view or edit a single entity.

This is the program flow executed within client/src/views/record/detail.js to render the record (entity) panels and fields (attributes):



1) After executing the core methods init() and setup() the core method afterSetup() is executed by default.

2) afterSetup() invokes method build()

3) method build() verifies whether it is required to render any side panels or bottom (relationship panels) in addition to the record panel ("middle") and then invokes method createMiddleView()

4) createMiddleView() halts the program flow until the "middle" view is actually crated, then invokes method getGridLayout() and provides as a parameter for it, a callback function that will create a view, extended from the view class client/src/views/record/detail-middle.js which will actually render the record (entity) panels and fields.

However in order to be able to create the "middle" view above, the information resulting from the execution of getGridLayout() is necessary, and that is why the program execution has to be halted and the instructions to build the "middle" view are encapsulated in a callback function which will be executed after getGridLayout() is done.

5) getGridLayout() invokes the helper class layoutManager to fetch the entity's layout information (panels and fields layout) stored as a json file (see post above regarding how the layout json files are created and updated manually through the Layout Manager Administration facility) and then provides this data to the method convertDetailLayout()

6) convertDetailLayout() takes the layout json data (simple layout) , which describes which panels to render and which fields inside those panels to render, and builds a complex object that inserts a field view class for each of the fields specified in the simple layout.

As an analogy, one can think of the simple layout, which is manipulated through the Layout Manager Admin facility, and the layout for a house that tells you how many rooms the house has and how are distributed and the results provided by convertDetailLayout() as the specifications of how to build each of the rooms.

7) convertDetailLayout() returns the complete layout data back to getGridLayout() which then executes the callback instruction created at createMiddleView() (number 4 above) to actually render the record (entity) in the "middle" section of the display.

[telecastg]

How to create two different layouts for displaying a single entity and apply either layout depending on a value of the entity's (record's) attribute (field)

Please note that the following example had been extremely simplified, and it is not fully tested nor optimized for highest speed of execution, since the objective is only to show how to create two custom layouts and apply them dynamically, not the actual solution to the hypothetical situation presented.

Also note that in this implementation, changing the layout requires a display reRender(), every time that the value of the target field changes, so the entity is automatically saved and that interferes with the inline editing and normal saving mechanism, therefore this example must be considered as partially implemented ONLY.

In the real world, such a simple variation, like the one used in this example (show a different field based on the value of another field), could easily be achieved using dynamic logic or dynamic handler to manipulate the visibility of fields instead of having a custom layout for each case.

However, using custom layouts for different cases would allow not only to hide or display fields, but to actually have a complete different distribution of fields and panels in the screen like changing templates, so for those interested in such capability this can be a good starting point

For this example, we assume the existence of an entity called SystemClient, which has, amongst its attributes, an enum field called clientType.

The field clientType has two possible values: Consumer and Institution and depending on this value, we will want to use a "consumerDetailLayout" or an "institutionDetailLayout" to render the Client record.

the "consumerDetailLayout" will include a link field to ratings of Medical Institutions called "institutionRatings" and the "institutionalDetailLayout" will include instead a link to a history of medical insurance claims by consumers called "consumerRatings"

Step 1: create a custom view class with the following content:

client/custom/src/views/system-client/record/detail.js

Espo.define('custom:views/system-client/record/detail', 'views/record/detail', function (Dep) {

    return Dep.extend({

        /* define a default display layout */
            defaultLayout: [
                {
                    "rows": [
                        [
                            {
                                "name": "name"
                            },
                                false
                        ],
                        [
                            {
                                "name": "clientType"
                            },
                            false
                        ]
                    ],
                    "style": "default",
                    "label": "Client Data"
                }
            ],

            /* define here the specifications for the consumer display,
             note that you can choose not only what fields are displayed but also the actual placement of the fields within each panel and even having a different number of panels for                 each layout */

            consumerDetailLayout: [
                {
                    "rows": [
                        [
                            {
                                "name": "name"
                            },
                            false
                        ],
                        [
                            {
                                "name": "clientType"
                            },
                            {
                                "name": "institutionRatings"
                            }
                        ]
                    ],
                    "style": "default",
                    "label": "Consumer Data"
                }      
            ],

            /* define here the specifications for the institution display */

            institutionDetailLayout: [
                {
                    "rows": [
                        [
                            {
                                "name": "name"
                            },
                            false
                        ],
                        [
                            {
                                "name": "clientType"
                            },
                            {
                                "name": "consumerRatings"
                            }
                        ]
                    ],
                    "style": "default",
                    "label": "Institution Data"
                }
            ],

        setup: function() {
            // invoke the original method from the extend base view class
            Dep.prototype.setup.call(this);
            this.detailLayout = this.defaultLayout;
            // add code to select the appropriate layout based on the value of the field and rebuild the display
            const self = this;
            self.listenToOnce(self, "after:render", function(){
                self.listenTo(self.model, "change:clientType", function() {
                    let clientType = self.model.get("clientType");
                    self.model.save();
                    if(clientType === "Consumer") {
                        self.detailLayout = self.consumerDetailLayout;
                    } else if (clientType === "Institution") {
                        self.detailLayout = self.institutionDetailLayout;
                    } else {

                    }
                    this.gridLayout = null;
                    self.build();
                    self.reRender();
                });
            });
        }
    });
});

Step 2: modify the custom entity's clientDefs script to let Espo know that it should invoke our custom view class to render a detail or edit display and not the default core view class.

custom/Espo/Custom/Resources/metadata/clientDefs/SystemClient.json

{
    "recordViews": {
        "detail": "custom:views/system-client/record/detail"
    }
}

[telecastg]

How to display different bottom (Relationship) panels in a Detail view depending on the value of one of the Entity's (Record) field

Replying to my friend and active contributor item question above, here is how you can toggle between different relationship panels in response to a change of one of the entity's field values.

In this example, when the client type is "Auditor" there are 2 relationship panels: "Treatments" and "Claims", when the client type is "Consumer" only the panel "Treatments" is displayed and when the client type is "Institution" only the panel "Claims" is displayed.







To accomplish this, follow the steps below:

1) Create custom record detail view class client/custom/src/views/system-client/record/detail.js

define('custom:views/system-client/record/detail', 'views/record/detail', function (Dep) {

    return Dep.extend({

        // define the specifications for the auditor bottom (relationship) panel
        auditorBottomPanelsDetail:
        {
            "_delimiter_": {
                "disabled": true
            },
            "treatments": {
                "index": 0
            },
            "claims": {
                "index": 1
            }
        },

        // define the specifications for the consumer bottom (relationship) panel
        consumerBottomPanelsDetail:
        {
            "_delimiter_": {
                "disabled": true
            },
            "treatments": {
                "index": 0
            }
        },

        // define the specifications for the institution bottom (relationship) panel
        institutionBottomPanelsDetail:
        {
            "_delimiter_": {
                "disabled": true
            },
            "claims": {
                "index": 0
            }
        },

        // initialize a layout data container object
        layoutData: {},

        // point to the custom bottom (relationship) panel view class
        bottomView: "custom:views/system-client/record/detail-bottom",

        setup: function() {
            // invoke the original method from the extend base view class
            Dep.prototype.setup.call(this);
             // render the appropriate bottom panels for the existing record
            this.toggleBottomPanel();
            // add code to switch the bottom panels based on the value of the control field
            this.listenTo(this.model, "change:clientType", function() {
                this.toggleBottomPanel();
            }, this);
        },

        // define a custom method to create the bottom (relationship) panel view instance
        createBottomView: function () {
            var el = this.options.el || '#' + (this.id);
            this.createView('bottom', this.bottomView, {
                model: this.model,
                scope: this.scope,
                el: el + ' .bottom',
                readOnly: this.readOnly,
                type: this.type,
                inlineEditDisabled: this.inlineEditDisabled,
                recordHelper: this.recordHelper,
                recordViewObject: this,
                portalLayoutDisabled: this.portalLayoutDisabled,
                layoutData: this.layoutData
            }, function(bottomView){
                bottomView.render();
            });
        },

        toggleBottomPanel: function () {
            const clientType = this.model.get("clientType");
            if(clientType === "Consumer") {
                this.layoutData = this.consumerBottomPanelsDetail;
            } else if (clientType === "Institution") {
                this.layoutData = this.institutionBottomPanelsDetail;
            } else if (clientType === "Auditor") {
                this.layoutData = this.auditorBottomPanelsDetail;
            }
            this.createBottomView();
        }

    });
});

2) Create custom bottom section view class client/custom/src/views/system-client/record/detail-bottom.js

define('custom:views/system-client/record/detail-bottom', 'views/record/detail-bottom', function (Dep) {

    return Dep.extend({

        // function adapted from views/record/detail-bottom
        setup: function () {

            this.type = this.mode;

            if ('type' in this.options) {
                this.type = this.options.type;
            }

            this.panelList = [];

            this.setupPanels();

            this.layoutData = this.options.layoutData;

            let panelNameList = [];

            this.panelList = this.panelList.filter((p) => {
                panelNameList.push(p.name);

                if (p.aclScope) {
                    if (!this.getAcl().checkScope(p.aclScope)) {
                        return;
                    }
                }

                if (p.accessDataList) {
                    if (!Espo.Utils.checkAccessDataList(p.accessDataList, this.getAcl(), this.getUser())) {
                        return false;
                    }
                }

                return true;
            });

            if (this.relationshipPanels) {
                let linkDefs = (this.model.defs || {}).links || {};
                if (this.layoutData) {
                    for (const name in this.layoutData) {
                        if (!linkDefs[name]) {
                            continue;
                        }

                        let p = this.layoutData[name];

                        if (!~panelNameList.indexOf(name) && !p.disbled) {
                            this.addRelationshipPanel(name, p);
                        }
                    }
                }
            }

            this.panelList = this.panelList.map((p) => {
                let item = Espo.Utils.clone(p);

                if (this.recordHelper.getPanelStateParam(p.name, 'hidden') !== null) {
                    item.hidden = this.recordHelper.getPanelStateParam(p.name, 'hidden');
                }
                else {
                    this.recordHelper.setPanelStateParam(p.name, 'hidden', item.hidden || false);
                }
                return item;
            });

            this.panelList.forEach((item) => {
                item.actionsViewKey = item.name + 'Actions';
            });

            this.alterPanels();

            this.setupPanelsFinal();

            this.setupPanelViews();

        }

    });
});

3) Create custom clientDefs json definition custom/Espo/Custom/Resources/metadata/clientDefs/Test3.json

{
    "controller": "controllers/record",
    "recordViews": {
        "detail": "custom:views/system-client/record/detail"
    },
    "boolFilterList": [
        "onlyMy"
    ]
}

4) Clear cache and rebuild.

[telecastg]

How is data persisted in the database when I click the "Save" button in an entity edit view ?



Clicking the "Save" button above triggers the following workflow:

L	Script	Method	Description
0	client/src/views/record/detail.js	actionSave()	Invokes action save()
1	client/src/views/record/base.js	save()	Invokes action fetch()
2	client/src/views/record/base.js	fetch()	Requests the "view" class for each field in the form
3	client/src/views/record/detail.js	getFieldViews()	Returns the "view" class for all fields in all sections in the form
4	client/src/views/record/detail-middle.js	getFieldViews()	Returns the "view" classes for the fields in the "middle" section
4	client/src/views/record/panels/side.js	getFieldViews()	Returns the "view" classes for the fields in the "side" section
4	client/src/views/record/panels/bottom.js	getFieldViews()	Returns the "view" classes for the fields in the "bottom" section
2	client/src/views/record/base.js	fetch()	Extracts the data from each field and returns as a "setAttribbutes" object
1	client/src/views/record/base.js	save()	Invokes action set()
2	client/src/model.js	set()	updates the model attribute values
1	client/src/views/record/base.js	save()	Invokes action save()
2	Backbone.model	save()	Invokes action sync()
3	Backbone	sync()	uses jQuery.ajax to make a RESTful JSON request and returns a jqXHR
4	application\Espo\Core\Controllers\RecordBase.php	postActionCreate()	If its a new record, Invokes method create() at the entity's back end Service class
4	application\Espo\Core\Controllers\RecordBase.php	putActionUpdate()	If its an update, invokes method update() at the entity's back end Service class

[telecastg]

How to transpose columns to rows in a list view ?

Based on a question posted by rem4332 and a hint provided by shalmaxb this is how to transpose columns and rows in a table view:






1) Create a custom list view class to render the target entity's list display: (In this example the entity is ServiceTech)

client/custom/src/views/service-tech/record/list.js

define('custom:views/service-tech/record/list', 'views/record/list', function (Dep) {

return Dep.extend({

dropdownItemList: [
{
name: 'transposeColumnsToRows',
label: 'Transpose Columns to Rows'
},
{
name: 'resetTransposition',
label: 'Reset Transposition'
}
],

actionTransposeColumnsToRows: function () {
$('tr.list-row').css("display","block");
$('tr.list-row').css("float","left");
$('td.cell').css("display","block");
$('th').css("display","none");
},

actionResetTransposition: function () {
$('tr.list-row').css("display","");
$('tr.list-row').css("float","");
$('td.cell').css("display","");
$('th').css("display","");
}

});

});

2) Create custom clientDefs file to let Espo know that the above view class should be invoked when rendering a list of service Techs

custom/Espo/Custom/Resources/metadata/clientDefs/ServiceTech.json

{
"recordViews": {
"list": "custom:views/service-tech/record/list"
}
}

3) Clear cache and rebuild

[telecastg]

Thanks for pointing it out Athensmusic you are right, I corrected the posting

[shalmaxb]

I resolved all mentioned questions of this post, see next comment. @telecastg: It is quite a time ago, when this was created, but still works in expoCRM 8.x. I am only missing two thing, which I did not get to resolve:

1. How can I display the the tableheader row as first column in the swapped view?
2. In the swapped view, the rows will not be lined up, if the content of any field is varying. The row`s cells will not be at the same position depending on the amount of content, if the content is a list of a multi enum field. The table in this case does not behave like an HTML table, where the complete row height is determined by the field with the largest height. How can I resolve this?

[shalmaxb]

Using this as a base I enhanced for my purpose the transposed table: https://forum.espocrm.com/forum/gene...iew#post101166

[telecastg]

How to create and use a custom button at the top left of the list display, where the kanban button is displayed.

Following item suggestion to use this type of button, to switch between a normal list display and a list where the columns and rows are transposed, as explained in the previous post, these are the steps to create and implement a custom button in this area:





1) Create a custom view class to display the list of records with the columns and rows transposed.
client/custom/src/views/service-tech/record/list.js

define('custom:views/service-tech/record/list', 'views/record/list', function (Dep) {

    return Dep.extend({

        setup: function () {
            Dep.prototype.setup.call(this);
            this.listenTo(this,'after:render', function() {
                $('tr.list-row').css("display","block");
                $('tr.list-row').css("float","left");
                $('td.cell').css("display","block");
                $('th').css("display","none");
            });
        }
    });

});

2) Create a custom view class to render the header area above the list display
client/custom/src/views/service-tech/header/list.js

define('custom:views/service-tech/header/list', 'views/list', function (Dep) {

    return Dep.extend({

        // incorporate the search view that includes the additional icon
        searchView: 'custom:views/service-tech/record/search',

        // fetch the name of the view class to be used to render the collection
        getRecordViewName: function () {
            if (this.viewMode === 'list') {
                return this.getMetadata().get(['clientDefs', this.scope, 'recordViews', 'list']) || this.recordView;
            } else if(this.viewMode === 'transpose') {
                return this.getMetadata().get(['clientDefs', this.scope, 'recordViews', 'transpose']);
            }
            return this.getMetadata().get(['clientDefs', this.scope, 'recordViews', this.viewMode]);
        }

    });

});

3) Create custom view class to render the "search" area in the header section above the list display
client/custom/src/views/service-tech/record/search.js

define('custom:views/service-tech/record/search', 'views/record/search', function (Dep) {

    return Dep.extend({

        // adds the new transpose icon at the top right corner of the list display
        viewModeIconClassMap: {
            list: 'fas fa-align-justify',
            transpose: 'fas fa-align-justify fa-rotate-90'
        }

    });
});

4) Create a custom clientDefs file for the target entity ("ServiceTech" in this example) to invoke the custom view classes created above
custom/Espo/Custom/Resources/metadata/clientDefs/ServiceTech.json

{
    "views": {
        "list": "custom:views/service-tech/header/list"
    },
    "recordViews": {
        "transpose": "custom:views/service-tech/record/list"
    },
    "listViewModeList" : ["list","transpose"]
}

5) Clear cache and rebuild.

[rem4332]

telecastg you are the madness, many dear thanks. Nice feature

[telecastg]

You're welcome rem4332

[telecastg]

How to filter the options available at a multi-enum field based on the options selected at another multi-enum field in the same entity. TESTED with Espo 7.0.9

This post describes how to accomplish the above functionality that we implemented for an Espo 7.0.9 compatible project.

Please note that that the example does use raw SQL which is discouraged by Espo's developers, however, for our purposes, investing time trying to "translate" the universal SQL to the custom Espo ORM "language" did not provide any advantage.

For those preferring to keep their code strictly adhered to Espo's developers standards, it will be necessary to implement the backend query using Espo's custom ORM.

In our project we have a Pinboard entity that is linked to one or more teams.

Within those teams there will be participants, having one or more specific roles, that will be allowed to read and write on the Pinboard, while all other participants having different roles will have read only rights by default.

We want the ability to choose one or many teams (Participant Teams) that will be able to view the Pinboard and then choose roles (Read Write Roles) that are linked one or more of the selected Participant Teams, and which will be able to read and write on the Pinboard. All other roles will have read only access.

So every time that a Participant Team is added or deleted, the options available for the Read Write Roles selector should adjust accordingly.

Step 1 - Define the multi-enum fields in the Pinboard entity entityDefs file:
custom\Espo\Custom\Resources\metadata\entityDefs\P inboard.json (partial)

{
    "fields": {
        "participantTeams": {
            "type": "multiEnum",
            "view": "custom:views/settings/fields/teams-list",
            "isCustom" : true
        },
        "readWriteRoles": {
            "type": "multiEnum",
            "view: "custom:views/settings/fields/roles-list,
            "isCustom": true            
        }
    }
}

Step 2 - Define the front-end views to render the fields as specified in the entityDefs file:
client/custom/src/views/setings/fields/teams-list.js

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

    return Dep.extend({

        setupOptions: function () {
            Espo.Ajax.getRequest('Team/action/list').then(
                function (data) {
                    data.list.sort((a, b) => (a.name > b.name) ? 1 : -1);
                    this.params.options = [];
                    data.list.forEach(function(optionObj){
                        this.params.options.push(optionObj.name);
                    },this);
                    // update the field display after downloading the options from the database
                    this.reRender();
                }.bind(this)
            );
        }

    });
});

client/custom/src/views/settings/fields/roles-list.js

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

    return Dep.extend({

        setup: function () {
            Dep.prototype.setup.call(this);
            // update the list of otions availabe every time that the participantTeams field is updated
            this.listenTo(this.model,'change: participantTeams', function() {
                this.setupOptions();
            });
        },

        setupOptions: function () {
            const teamsNames = this.model.get('participantTeams') || [];
            if(teamsNames.length < 1) {
                // do not display any options unless the "participantTeams" field contains at least one selection
                this.params.options = [];
                this.reRender();
                return;
            }
            Espo.Ajax.getRequest('Pinboard/action/fetchTeamsLinkedRoles', {
                teams: teamsNames
            }).then(
                function (fetchedData) {
                    fetchedData.list.sort((a, b) => (a.name > b.name) ? 1 : -1);
                    this.params.options = [];
                    fetchedData.list.forEach(function(optionObj){
                        // this function can be used to further filter the list of roles available for selecting. In this case we don't use any additional filter
                        this.params.options.push(optionObj.name);
                    },this);
                    // update the field display after downloading the options from the Database
                    this.reRender();
                }.bind(this)
            );
        }

    });
});

Step 3 - Implement the back end classes to retrieve the list of roles from the database
custom\Espo\Custom\Controllers\Pinboard.php

namespace Espo\Custom\Controllers;

use Espo\Core\Api\Request;
use StdClass;

class Pinboard extends \Espo\Core\Templates\Controllers\Base
{

    public function getActionFetchTeamsLinkedRoles(Request $request): StdClass
    {
        $teams = $request->getQueryParam('teams');
        $queryResult = $this->getRecordService()->getTeamsRelatedRoles($teams);

        return (object) [
            'list' => $queryResult
        ];
    }
} 

custom\Espo\Custom\Services\Pinboard.php

namespace Espo\Custom\Services;

use PDO;

class Pinboard extends \Espo\Core\Templates\Services\Base
{

    public function getTeamsRelatedRoles($teams): Array
    {
        $sqlString = 'SELECT role.name As `name` 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.name IN (';
        for ($i = 0; $i < count($teams); $i++){
            if($i > 0) {
                $sqlString.= ',"'.$teams[$i].'"';
            } else {
                $sqlString.= '"'.$teams[$i].'"';
            }
        }
        $sqlString.= ') GROUP BY role.name';

        $data = $this->entityManager
            ->getSqlExecutor()
            ->execute($sqlString)
            ->fetchAll(PDO::FETCH_ASSOC);

        return $data;
    }

} 

Step 4 - Clear cache and rebuild the application

[yuri]

Needs sanitizing parameters before using in raw SQL. E.g. using $entityManager->getPDO()->quote($value).

Recommend using ORM to prevent possible security issues. Building such SQL with ORM is easy, especially if IDE's autocompletion.

[telecastg]

Thank you, I appreciate the suggestions:

I don't believe that sanitizing is necessary In this case, because the only parameter being fed to the query is the list of values from the multi-enum field, which are pre-defined and not modifiable by the user.

However I completely agree that whenever you have user input feeding a query, is very important to "sanitize" the input to avoid malicious SQL injection

Could you please elaborate on how to build an ORM style query, based on a regular SQL query using IDE's autocompletion ?

I couldn't figure out how to use the ORM "language" to construct a query that uses the "IN" statement, is there an example in the code base that i could check ? .

Thanks !

[LuckyLouie]

telecastg Thank you for all tutorials.
Is there any easy way to move the dashlist tabs to the menu bar?

[telecastg]

Hi, I am sorry don't know of any "easy" way to do this and we haven't developed anything similar so I can't provide any code examples