Magento2 Product Collection – Join Category Name in Product Collection

gridmagento2product

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:

select main_table.sku, group_concat(catalog_category_entity_varchar.value) from catalog_product_entity main_table
join catalog_category_product on catalog_category_product.product_id = main_table.entity_id
join catalog_category_entity_varchar on 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)
group by main_table.sku;

Translated into a Magento collection way it can like this (it can be done nicer of course, especially the varchar table join :-)):

$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$collection = $objectManager->get('Magento\Catalog\Model\ResourceModel\Product\CollectionFactory')->create();
$collection->getSelect()->join(
    'catalog_category_product',
    'e.entity_id=`catalog_category_product`.product_id')
->join(
    'catalog_category_entity_varchar',
    new \Zend_Db_Expr('`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)'),
    array(
        'categories'  => new \Zend_Db_Expr('group_concat(`catalog_category_entity_varchar`.value SEPARATOR ",")'),)
    )->group('sku');
$collection->load();

echo $collection->count() . "\n";

foreach($collection as $product){
    echo $product->getSku() . " -- " . $product->getCategories() . "\n";
}

So you need to "translate" that into your prepare collection.

Related Topic