Create Category grid with category children count column in UI component form

In this blog, we are going to learn how we can create a category grid in the UI component form with the “children_count” column value in the admin section.

You can check our Previous Blog in which we have mentioned that what error we can face when we fetch children_count column in category collection.
In prev blog, we have created a category grid without the children_count column.

In this blog, we will also display the children_count of category in the category grid.

To do this, you have to need to follow the below steps:

1. Get the all categories using following code:

$collection = $this->categoryFactory->create()
            ->getCollection()
            ->addAttributeToSelect('*');

Here, the query will be as following:

SELECT `e`.* FROM `catalog_category_entity` AS `e`

If you will execute this query in the database, the result will be as following:

2. Now reset columns of collection, and set columns which you want. Here, I have reset all columns and set entity_id and children_count as childrenCount columns.

$collection->getSelect()
            ->reset(\Zend_Db_Select::COLUMNS)
            ->columns(['entity_id', 'e.children_count as childrenCount']);

Now, the query will be as following:

SELECT `e`.`entity_id`, `e`.`children_count` AS `childrenCount` FROM `catalog_category_entity` AS `e`

If you will execute this query in the database, the result will be as following:

3. As I have to display category names in the grid, so I have added the following statement in the collection query.

$collection->addAttributeToSelect('*')
            ->addFieldToFilter('name', ["neq"=>null])
            ->addFieldToFilter('is_active', 1);

Now, the query will be as following:

SELECT `e`.`entity_id`, `e`.`children_count` AS `childrenCount`, IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name`, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_active` FROM `catalog_category_entity` AS `e` LEFT JOIN `catalog_category_entity_varchar` AS `at_name_default` ON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND (`at_name_default`.`attribute_id` = '45') AND `at_name_default`.`store_id` = 0 LEFT JOIN `catalog_category_entity_varchar` AS `at_name` ON (`at_name`.`entity_id` = `e`.`entity_id`) AND (`at_name`.`attribute_id` = '45') AND (`at_name`.`store_id` = 1) LEFT JOIN `catalog_category_entity_int` AS `at_is_active_default` ON (`at_is_active_default`.`entity_id` = `e`.`entity_id`) AND (`at_is_active_default`.`attribute_id` = '46') AND `at_is_active_default`.`store_id` = 0 LEFT JOIN `catalog_category_entity_int` AS `at_is_active` ON (`at_is_active`.`entity_id` = `e`.`entity_id`) AND (`at_is_active`.`attribute_id` = '46') AND (`at_is_active`.`store_id` = 1) WHERE (IF(at_name.value_id > 0, at_name.value, at_name_default.value) != '') AND (IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) = '1')

If you will execute this query in the database, the result will be as following:

4. Now, check the complete code in CategoryGrid.php file inside app/code/Vendor/CustomModule/Block/Adminhtml/Rule/Edit/Tab/ directory.

<?php
/**
 * Webkul Software.
 *
 * @category  Webkul
 * @package   Vendor_CustomModule
 * @author    Webkul
 * @copyright Webkul Software Private Limited (https://webkul.com)
 * @license   https://store.webkul.com/license.html
 */

namespace Vendor\CustomModule\Block\Adminhtml\Rule\Edit\Tab;

use Magento\Customer\Controller\RegistryConstants;

/**
 * class for the category grid tab in the rule form.
 */
class CategoryGrid extends \Magento\Backend\Block\Widget\Grid\Extended
{
    /**
     * @var \Magento\Catalog\Model\CategoryFactory
     */
    protected $categoryFactory;

    /**
     * @var \Vendor\CustomModule\Model\ResourceModel\RuleCategory\CollectionFactory
     */
    protected $ruleCategoryCollection;

    /**
     * Construct
     *
     * @param \Magento\Backend\Block\Template\Context $context
     * @param \Magento\Backend\Helper\Data $backendHelper
     * @param \Magento\Catalog\Model\CategoryFactory $categoryFactory
     * @param \Vendor\CustomModule\Model\ResourceModel\RuleCategory\CollectionFactory $ruleCategoryCollection
     * @param array $data
     */
    public function __construct(
        \Magento\Backend\Block\Template\Context $context,
        \Magento\Backend\Helper\Data $backendHelper,
        \Magento\Catalog\Model\CategoryFactory $categoryFactory,
        \Vendor\CustomModule\Model\ResourceModel\RuleCategory\CollectionFactory $ruleCategoryCollection,
        array $data = []
    ) {
        $this->categoryFactory = $categoryFactory;
        $this->ruleCategoryCollection = $ruleCategoryCollection;
        parent::__construct($context, $backendHelper, $data);
    }

    /**
     * @inheritdoc
     */
    protected function _construct()
    {
        parent::_construct();
        $this->setId('loyalty_rule_categorygrid');
        $this->setDefaultSort('entity_id', 'asc');
        $this->setUseAjax(true);
    }

    /**
     * @param Column $column
     * @return $this
     */
    protected function _addColumnFilterToCollection($column)
    {
        // Set custom filter for in category flag
        if ($column->getId() == 'in_categories') {
            $categoryIds = $this->_getSelectedCategories();
            if (empty($categoryIds)) {
                $categoryIds = 0;
            }
            if ($column->getFilter()->getValue()) {
                $this->getCollection()->addFieldToFilter('entity_id', ['in' => $categoryIds]);
            } elseif (!empty($categoryIds)) {
                $this->getCollection()->addFieldToFilter('entity_id', ['nin' => $categoryIds]);
            }
        } else {
            parent::_addColumnFilterToCollection($column);
        }
        return $this;
    }

    /**
     * Apply various selection filters to prepare the category grid collection.
     *
     * @return $this
     */
    protected function _prepareCollection()
    {
        $collection = $this->categoryFactory->create()
            ->getCollection()
            ->addAttributeToSelect('*');
           
        $collection->getSelect()
            ->reset(\Zend_Db_Select::COLUMNS)
            ->columns(['entity_id', 'e.children_count as childrenCount']);
            
        $collection->addAttributeToSelect('*')
            ->addFieldToFilter('name', ["neq"=>null])
            ->addFieldToFilter('is_active', 1);
            
        $this->setCollection($collection);
        return parent::_prepareCollection();
    }

    /**
     * @inheritdoc
     */
    protected function _prepareColumns()
    {
        $this->addColumn(
            "entity_id",
            [
                "type"     => "number",
                "align"    => "center",
                "width"    => "30px",
                "index"    => "entity_id",
                "header"   => __("ID")
            ]
        );

        $this->addColumn(
            "name",
            [
                "index"    => "name",
                "align"    => "left",
                "header"   => __("Category Name")
            ]
        );

        $this->addColumn(
            "childrenCount",
            [
                "index"    => "childrenCount",
                "align"    => "left",
                "header"   => __("Children Count")
            ]
        );

        $this->addColumn(
            "in_categories",
            [
                "type"     => "checkbox",
                "name"     => "in_categories",
                "align"    => "center",
                "width"    => "100px",
                "index"    => "entity_id",
                "values"   => $this->_getSelectedCategories(),
                "header"   => __("Select"),
                "sortable" => false
            ]
        );

        $this->addColumn(
            "inv",
            [
                "type"     => "input",
                "class"    => "number_check loyalty_points",
                "width"    => "150px",
                "align"    => "center",
                "index"    => "inv",
                "filter"   => false,
                "header"   => __("Loyalty Points"),
                "sortable" => false,
                "renderer" => \Vendor\CustomModule\Block\Adminhtml\Points::class
            ]
        );
        
        return parent::_prepareColumns();
    }

    /**
     * @inheritdoc
     */
    public function getGridUrl()
    {
        return $this->getUrl("*/*/categoryGridData", ["_current"=>true]);
    }

    /**
     * @return array
     */
    protected function _getSelectedCategories()
    {
        $categoryIds = [];
        $ruleId      = $this->getRequest()->getParam("id");
        $pointsCollection = $this->ruleCategoryCollection->create()
            ->addFieldToFilter("loyalty_rule_id", $ruleId);
        
        foreach ($pointsCollection as $each) {
            $categoryIds[] = $each->getCategoryId();
        }
        return $categoryIds;
    }
}