In prev blog, we have created a category grid without the children_count column.<\/p>\n\n\n\n
In this blog, we will also display the children_count <\/strong>of category in the category grid.<\/p>\n\n\n\n To do this, you have to need to follow the below steps: Here, the query will be as following:<\/p>\n\n\n\n If you will execute this query in the database, the result will be as following:<\/p>\n\n\n\n 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<\/strong> columns.<\/p>\n\n\n\n Now, the query will be as following:<\/p>\n\n\n\n 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. Now, the query will be as following:<\/p>\n\n\n\n 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.<\/p>\n\n\n\n 5. Download the complete code<\/a>.<\/p>\n\n\n\n Hope this will be helpful. Thanks \ud83d\ude42
1. Get the all categories using following code:<\/p>\n\n\n\n$collection = $this->categoryFactory->create()\n ->getCollection()\n ->addAttributeToSelect('*');<\/pre>\n\n\n\nSELECT `e`.* FROM `catalog_category_entity` AS `e`<\/pre>\n\n\n\n
![\"SQLResult1-1\"](\"https:\/\/cdnblog.webkul.com\/blog\/wp-content\/uploads\/2021\/09\/SQLResult1-1-1200x413.png\")
<\/figure>\n\n\n\n$collection->getSelect()\n ->reset(\\Zend_Db_Select::COLUMNS)\n ->columns(['entity_id', 'e.children_count as childrenCount']);<\/pre>\n\n\n\n
SELECT `e`.`entity_id`, `e`.`children_count` AS `childrenCount` FROM `catalog_category_entity` AS `e`<\/pre>\n\n\n\n
<\/p>\n\n\n\n![\"SQLResult4\"](\"https:\/\/cdnblog.webkul.com\/blog\/wp-content\/uploads\/2021\/09\/SQLResult4-1200x490.png\")
<\/figure>\n\n\n\n
<\/p>\n\n\n\n$collection->addAttributeToSelect('*')\n ->addFieldToFilter('name', ["neq"=>null])\n ->addFieldToFilter('is_active', 1);<\/pre>\n\n\n\nSELECT `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')<\/pre>\n\n\n\n
<\/p>\n\n\n\n![\"SQLResult5\"](\"https:\/\/cdnblog.webkul.com\/blog\/wp-content\/uploads\/2021\/09\/SQLResult5-1200x642.png\")
<\/figure>\n\n\n\n<?php\n\/**\n * Webkul Software.\n *\n * @category Webkul\n * @package Vendor_CustomModule\n * @author Webkul\n * @copyright Webkul Software Private Limited (https:\/\/webkul.com)\n * @license https:\/\/store.webkul.com\/license.html\n *\/\n\nnamespace Vendor\\CustomModule\\Block\\Adminhtml\\Rule\\Edit\\Tab;\n\nuse Magento\\Customer\\Controller\\RegistryConstants;\n\n\/**\n * class for the category grid tab in the rule form.\n *\/\nclass CategoryGrid extends \\Magento\\Backend\\Block\\Widget\\Grid\\Extended\n{\n \/**\n * @var \\Magento\\Catalog\\Model\\CategoryFactory\n *\/\n protected $categoryFactory;\n\n \/**\n * @var \\Vendor\\CustomModule\\Model\\ResourceModel\\RuleCategory\\CollectionFactory\n *\/\n protected $ruleCategoryCollection;\n\n \/**\n * Construct\n *\n * @param \\Magento\\Backend\\Block\\Template\\Context $context\n * @param \\Magento\\Backend\\Helper\\Data $backendHelper\n * @param \\Magento\\Catalog\\Model\\CategoryFactory $categoryFactory\n * @param \\Vendor\\CustomModule\\Model\\ResourceModel\\RuleCategory\\CollectionFactory $ruleCategoryCollection\n * @param array $data\n *\/\n public function __construct(\n \\Magento\\Backend\\Block\\Template\\Context $context,\n \\Magento\\Backend\\Helper\\Data $backendHelper,\n \\Magento\\Catalog\\Model\\CategoryFactory $categoryFactory,\n \\Vendor\\CustomModule\\Model\\ResourceModel\\RuleCategory\\CollectionFactory $ruleCategoryCollection,\n array $data = []\n ) {\n $this->categoryFactory = $categoryFactory;\n $this->ruleCategoryCollection = $ruleCategoryCollection;\n parent::__construct($context, $backendHelper, $data);\n }\n\n \/**\n * @inheritdoc\n *\/\n protected function _construct()\n {\n parent::_construct();\n $this->setId('loyalty_rule_categorygrid');\n $this->setDefaultSort('entity_id', 'asc');\n $this->setUseAjax(true);\n }\n\n \/**\n * @param Column $column\n * @return $this\n *\/\n protected function _addColumnFilterToCollection($column)\n {\n \/\/ Set custom filter for in category flag\n if ($column->getId() == 'in_categories') {\n $categoryIds = $this->_getSelectedCategories();\n if (empty($categoryIds)) {\n $categoryIds = 0;\n }\n if ($column->getFilter()->getValue()) {\n $this->getCollection()->addFieldToFilter('entity_id', ['in' => $categoryIds]);\n } elseif (!empty($categoryIds)) {\n $this->getCollection()->addFieldToFilter('entity_id', ['nin' => $categoryIds]);\n }\n } else {\n parent::_addColumnFilterToCollection($column);\n }\n return $this;\n }\n\n \/**\n * Apply various selection filters to prepare the category grid collection.\n *\n * @return $this\n *\/\n protected function _prepareCollection()\n {\n $collection = $this->categoryFactory->create()\n ->getCollection()\n ->addAttributeToSelect('*');\n \n $collection->getSelect()\n ->reset(\\Zend_Db_Select::COLUMNS)\n ->columns(['entity_id', 'e.children_count as childrenCount']);\n \n $collection->addAttributeToSelect('*')\n ->addFieldToFilter('name', ["neq"=>null])\n ->addFieldToFilter('is_active', 1);\n \n $this->setCollection($collection);\n return parent::_prepareCollection();\n }\n\n \/**\n * @inheritdoc\n *\/\n protected function _prepareColumns()\n {\n $this->addColumn(\n "entity_id",\n [\n "type" => "number",\n "align" => "center",\n "width" => "30px",\n "index" => "entity_id",\n "header" => __("ID")\n ]\n );\n\n $this->addColumn(\n "name",\n [\n "index" => "name",\n "align" => "left",\n "header" => __("Category Name")\n ]\n );\n\n $this->addColumn(\n "childrenCount",\n [\n "index" => "childrenCount",\n "align" => "left",\n "header" => __("Children Count")\n ]\n );\n\n $this->addColumn(\n "in_categories",\n [\n "type" => "checkbox",\n "name" => "in_categories",\n "align" => "center",\n "width" => "100px",\n "index" => "entity_id",\n "values" => $this->_getSelectedCategories(),\n "header" => __("Select"),\n "sortable" => false\n ]\n );\n\n $this->addColumn(\n "inv",\n [\n "type" => "input",\n "class" => "number_check loyalty_points",\n "width" => "150px",\n "align" => "center",\n "index" => "inv",\n "filter" => false,\n "header" => __("Loyalty Points"),\n "sortable" => false,\n "renderer" => \\Vendor\\CustomModule\\Block\\Adminhtml\\Points::class\n ]\n );\n \n return parent::_prepareColumns();\n }\n\n \/**\n * @inheritdoc\n *\/\n public function getGridUrl()\n {\n return $this->getUrl("*\/*\/categoryGridData", ["_current"=>true]);\n }\n\n \/**\n * @return array\n *\/\n protected function _getSelectedCategories()\n {\n $categoryIds = [];\n $ruleId = $this->getRequest()->getParam("id");\n $pointsCollection = $this->ruleCategoryCollection->create()\n ->addFieldToFilter("loyalty_rule_id", $ruleId);\n \n foreach ($pointsCollection as $each) {\n $categoryIds[] = $each->getCategoryId();\n }\n return $categoryIds;\n }\n}<\/pre>\n\n\n\n![\"categoryGridWithChildrenCount\"](\"https:\/\/cdnblog.webkul.com\/blog\/wp-content\/uploads\/2021\/09\/categoryGridWithChildrenCount-1200x598.png\")
<\/figure>\n\n\n\n
Previous Blog: Fix category grid issue due to children_count column in custom UI component Form<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"