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:
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:
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:
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;
}
}
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
Be the first to comment.