When you will create a category grid for the UI component form, if you fetch complete data from “catalog_category_entity”, you can face following issue(refer to the following image):<\/p>\n\n\n\n
Error: <\/strong>Warning: Invalid argument supplied for foreach() in \/vendor\/magento\/module-backend\/view\/adminhtml\/templates\/widget\/grid\/extended.phtml on line 247 This issue occurs due to a conflict of the “children_count” column, which will get in category collection results.<\/p>\n\n\n\n So, if you don’t need this “children_count” column, then you will have the need to remove this column from the collection. Here, the query will be as following: 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 only the “entity_id” column. Now, the query will be as following: If you will execute this query in database, 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: If you will execute this query in database, result will be as following:<\/p>\n\n\n\n 4. Now, check the complete code in CategoryGrid.php file inside app\/code\/Vendor\/CustomModule\/Block\/Adminhtml\/Rule\/Edit\/Tab\/ directory. Hope this will be helpful. Thanks \ud83d\ude42
Exception in \/vendor\/magento\/framework\/App\/ErrorHandler.php:61<\/em><\/p>\n\n\n\n
<\/p>\n\n\n\n![\"categorygridError\"](\"https:\/\/cdnblog.webkul.com\/blog\/wp-content\/uploads\/2021\/09\/categorygridError-1200x446.png\")
<\/figure>\n\n\n\n
So, you have to need to follow the below steps to generate the collection according to your need:
1. Get all categories using the following code:
<\/p>\n\n\n\n$collection = $this->categoryFactory->create()\n ->getCollection()\n ->addAttributeToSelect('*');<\/pre>\n\n\n\n
<\/p>\n\n\n\nSELECT `e`.* FROM `catalog_category_entity` AS `e`<\/pre>\n\n\n\n
<\/p>\n\n\n\n![\"SQLResult1\"](\"https:\/\/cdnblog.webkul.com\/blog\/wp-content\/uploads\/2021\/09\/SQLResult1-1200x413.png\")
<\/figure>\n\n\n\n
<\/p>\n\n\n\n$collection->getSelect()\n ->reset(\\Zend_Db_Select::COLUMNS)\n ->columns(['entity_id']);<\/pre>\n\n\n\n
<\/p>\n\n\n\nSELECT `e`.`entity_id` FROM `catalog_category_entity` AS `e`<\/pre>\n\n\n\n
<\/p>\n\n\n\n![\"SQLResult2\"](\"https:\/\/cdnblog.webkul.com\/blog\/wp-content\/uploads\/2021\/09\/SQLResult2.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\n
<\/p>\n\n\n\nSELECT `e`.`entity_id`, IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name`, \nIF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_active` \nFROM `catalog_category_entity` AS `e` LEFT JOIN `catalog_category_entity_varchar` AS `at_name_default` \nON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND (`at_name_default`.`attribute_id` = '45') AND \n`at_name_default`.`store_id` = 0 LEFT JOIN `catalog_category_entity_varchar` AS `at_name` \nON (`at_name`.`entity_id` = `e`.`entity_id`) AND (`at_name`.`attribute_id` = '45') AND (`at_name`.`store_id` = 1) \nLEFT JOIN `catalog_category_entity_int` AS `at_is_active_default` \nON (`at_is_active_default`.`entity_id` = `e`.`entity_id`) AND (`at_is_active_default`.`attribute_id` = '46') \nAND `at_is_active_default`.`store_id` = 0 LEFT JOIN `catalog_category_entity_int` AS `at_is_active` \nON (`at_is_active`.`entity_id` = `e`.`entity_id`) AND (`at_is_active`.`attribute_id` = '46') \nAND (`at_is_active`.`store_id` = 1) WHERE (IF(at_name.value_id > 0, at_name.value, at_name_default.value) != '') \nAND (IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) = '1')<\/pre>\n\n\n\n
![\"SQLResult3\"](\"https:\/\/cdnblog.webkul.com\/blog\/wp-content\/uploads\/2021\/09\/SQLResult3-1200x614.png\")
<\/figure>\n\n\n\n
<\/p>\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']);\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 "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![\"categoryGrid\"](\"https:\/\/cdnblog.webkul.com\/blog\/wp-content\/uploads\/2021\/09\/categoryGrid-1200x473.png\")
<\/figure>\n\n\n\n
Next Blog: Create Category grid with category children count column in UI component form<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"