I want to join category name in product collection. I want to use that in product grid filter which is used in my custom module.
This is my collection code. It's same as like this _prepareCollection() :
vendor/magento/module-catalog/Block/Adminhtml/Product/Grid.php
protected $productFactory;
public function __construct(
.....
\Magento\Catalog\Model\ProductFactory $productFactory,
.....
) {
.....
$this->productFactory = $productFactory;
.....
}
protected function _prepareCollection() {
$store = $this->_getStore();
$collection = $this->productFactory->create()->getCollection()
->addAttributeToSelect(
'sku'
)->addAttributeToSelect(
'name'
)->addAttributeToSelect(
'attribute_set_id'
)->addAttributeToSelect(
'type_id'
)->setStore(
$store
);
}
EDIT :
$collection->getSelect()->join(
'catalog_category_product',
'e.entity_id = catalog_category_product.product_id'
)->join(
'catalog_category_entity_varchar',
'catalog_category_entity_varchar.entity_id = catalog_category_product.category_id AND catalog_category_entity_varchar.attribute_id = (select attribute_id from eav_attribute where attribute_code = \'name\' and entity_type_id = 3)',
['categories' => new \Zend_Db_Expr('group_concat(`catalog_category_entity_varchar`.value SEPARATOR ",")')])
->group('sku');
How to do it?
Any help would be appriciated.
Thanks.
Best Answer
You will need to join some tables and use group concat to achieve that. For example thsi SQL statement gives you the sku of a product and the categories to which a product is assigned:
Translated into a Magento collection way it can like this (it can be done nicer of course, especially the varchar table join :-)):
So you need to "translate" that into your prepare collection.