Magento 2 : Join category name in product collection

Question

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.

Answer 1

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.