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

In this blog, we are going to learn how we can create a category grid in the UI component form in the admin section.

In Magento 2, when we create a grid in the UI component form, we extend \Magento\Backend\Block\Widget\Grid\Extended Class in our custom grid Class.

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):

Error: Warning: Invalid argument supplied for foreach() in /vendor/magento/module-backend/view/adminhtml/templates/widget/grid/extended.phtml on line 247
Exception in /vendor/magento/framework/App/ErrorHandler.php:61

Searching for an experienced
Magento 2 Company ? Read More

This issue occurs due to a conflict of the “children_count” column, which will get in category collection results.
So, if you don’t need this “children_count” column, then you will have the need to remove this column from the collection.

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:

$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 only the “entity_id” column.

$collection->getSelect()
            ->reset(\Zend_Db_Select::COLUMNS)
            ->columns(['entity_id']);

Now, the query will be as following:

SELECT `e`.`entity_id` FROM `catalog_category_entity` AS `e`

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.

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

Now, the query will be as following:

SELECT `e`.`entity_id`, 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 database, 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']);
            
        $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(
            "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;
    }
}