Reading list Switch to dark mode

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

    Updated 18 September 2021

    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:

    Searching for an experienced
    Magento 2 Company ?
    Find out More
    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

    . . .

    Leave a Comment

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


    Be the first to comment.

    Back to Top

    Message Sent!

    If you have more details or questions, you can reply to the received confirmation email.

    Back to Home