No announcement yet.

Category Tree Linked Entity - filter all descendant, not only direct child

  • Filter
  • Time
  • Show
Clear All
new posts

  • Category Tree Linked Entity - filter all descendant, not only direct child

    Existing example relationships in system are:
    <CategoryEntity> -> <Subentity>

    - ReportCategory -> Report
    - KnowledgeBaseCategory -> KnowledgeBase
    - DocumentFolder -> Document
    - ProductCategory -> Product

    Standard link is of type "belongsTo" or "hasMany".

    There is a middle DB table representing tree structure {CategoryEntityName}_path representing tree structure of the categories.

    List view of the subentity offers two types of view: "Collapsed" ( = Folder view) and "Expanded" ( = Tree view).
    The first shows only direct childs, when category is selected.
    The latter one shows all descendants of the selected category.

    How can one use these two methods of filtration for subentity inList View Search Filters and mainly in Report Filters?
    (Example usage: report all documents belonging to certain folder recursively )

    The standard filtration searchOptions for link of type belongsTo are: "isEmpty", "isNotEmpty", "equals", "notEquals".
    When using equals, this filters out only direct childs of course.

    The internal tree view logic uses a term "inCategory"
    Best way would be adding some extra searchOptions for link searchOptions: "inCategory", "notInCategory", "inAnyOfCategories", "notInCategories"

    For link of the type "hasMany" some more: "inAllCategories" etc.

    These options would be visible only when linked entity has scope attribute "type": "CategoryTree".

  • #2
    "inCategory" filter already searches in child categories. It's not available from the field filter, but applied when you select a folder in expanded mode.

    If we added additional filters for the "Category" field it would interfere/conflict with the expanded mode which is not desired (bad UIX, more false bug reports to handle).


    • #3
      I understand your points, but how can I use such filter in Reports ?

      At least please tell me, how to create custom additional searchTypes.

      Some docs or example code would help.


      • #4
        I have done some investigation in the code, and if I understand it correctly, the previous intention was about to work the way I asked for.

        At least for search type 'equals'

        It probably stopped working after some updates.

        In file client/src/views/fields/link-category-tree.js:

        if (data.typeFront === 'is') {
                    data.field =;
                    data.type = 'inCategory';
        The variable "typeFront" is obsolete and undefined, therefore the condition never passes.

        After changing it to:
           if ( === 'is') {
                  data.attribute =;​
                  data.type = 'inCategory';

        filter starts working correctly.

        Moreover === 'isOneOf' works as well, just the total sum of results doubles, when the selected categories overlap.

        This can be solved by adding this:
        $this->setDistinct(true, $result);
        to Line 500 in file : application/Espo/Core/Select/SelectManager.php (deprecated)
        Last edited by mixerito; 09-22-2023, 10:07 PM.


        • #5
          There is a middle DB table representing tree structure {CategoryEntityName}_path. What should I do if this table is empty? The tree structure is displayed from DB table {CategoryEntityName}.


          • #6
            The categoryFilterType: 'inCategory' parameter returns empty when selecting tree fields. It displays only the full list (the root of the tree). If categoryFilterType: null, it returns error 500.

            HTML Code:
            Espo.define('custom:views/account/list', ['views/list-with-categories'], function (Dep) {
                return Dep.extend({
                    categoryScope: 'CLocationCategory',
                    categoryField: 'cCLocationCategories',
                    isExpanded: false,
                    categoryFilterType: 'inCategory'