Menu Close
    Searching for an experienced Magento 2 Development Company ?

    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:

    SQLResult1-1

    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:

    SQLResult4

    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:

    SQLResult5

    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;
        }
    }
    
    categoryGridWithChildrenCount

    5. Download the complete code.

    Hope this will be helpful. Thanks 🙂

    Previous Blog: Fix category grid issue due to children_count column in custom UI component Form

    . . .
    Discuss on Helpdesk

    Leave a Comment

    Your email address will not be published. Required fields are marked*


    Be the first to comment.

    Back to Top