2

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.

4

1 Answer 1

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.

6
  • Did you see this file ? vendor/magento/module-catalog/Block/Adminhtml/Product/Grid.php. Is it not possible to do something in that code instead of query? Your answer is tricky :D Mar 13, 2019 at 10:23
  • The second part of the answer is using a collection that gives you sku and categories. Use that with your collection and adapt it. You will also need to add a column to the grid and so on. The answer shows you how to use the join for your situation and is not the complete code for your requirement... you have to do that by yourself :-)
    – HelgeB
    Mar 13, 2019 at 10:29
  • Can you please tell me what is attribute_code = \'name\' ? Mar 13, 2019 at 11:01
  • The attribute code for the category name. And you want the value of that attribute
    – HelgeB
    Mar 13, 2019 at 11:04
  • I created this query. It returns A Joined field with this alias is already declared. I edit question. Wherer I do mistake? Mar 13, 2019 at 11:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.