0

We use the following code, to get the category name of a specific product.

This works fine, but we face the issue with the store views. It only works well if the specific store view does have a unique value for the category name.

See this part $cond[] = $items->getSelect()->getConnection()->quoteInto("category_name.store_id=?", $this->_storeManager->getStore()->getId());

But when the "Use Default Value" checkbox is checked, then there is no value for the store view and it returns an empty value.

How can we fix this, by editing our code that it only fetch the store view value if it exist and otherwise it should use store id 0 (global)?

CODE:

<?php

namespace Module\ProductRelated\Model;

use Magento\Store\Model\Store;

class CategoryInfoFinder
{
    /**
     * @var \Magento\Eav\Model\Config
     */
    private $eavConfig;
    
    protected $_storeManager;

    public function __construct(
        \Magento\Eav\Model\Config $eavConfig,
        \Magento\Store\Model\StoreManagerInterface $storeManager
    ) {
        $this->eavConfig = $eavConfig;
        $this->_storeManager = $storeManager;
    }

    public function addCategoryInfo(\Magento\Catalog\Model\ResourceModel\Product\Collection $items)
    {
        $productIds = [];
        foreach ($items as $product) {
            $productIds[] = $product->getId();
        }

        $select = $items->getSelect()->getConnection()->select()->from(['category_product' => 'catalog_category_product'])
            ->where('product_id in (?)', $productIds)
            ->reset(\Magento\Framework\DB\Select::COLUMNS)
            ->columns(['product_id' => 'category_product.product_id']);

        $categoryNameAttribute = $this->eavConfig->getAttribute(\Magento\Catalog\Model\Category::ENTITY, 'name');

        $cond[] = "category_name.entity_id=category_product.category_id";
        $cond[] = $items->getSelect()->getConnection()->quoteInto("category_name.attribute_id=?", $categoryNameAttribute->getId());
        $cond[] = $items->getSelect()->getConnection()->quoteInto("category_name.store_id=?", $this->_storeManager->getStore()->getId());           

        $select = $select->joinLeft(
            ['category_name' => 'catalog_category_entity_' . $categoryNameAttribute->getBackendType()],
            implode(' AND ', $cond),
            ['category_name' => 'value', 'category_id' => 'category_name.entity_id']
        );

        $select->order('category_product.category_id ' . \Magento\Framework\Data\Collection::SORT_ORDER_DESC);

        $categoryInfoList = $items->getSelect()->getConnection()->fetchAll($select);

        foreach ($items as $product) {
            $categoryInfo = $this->findCategoryInfoForProduct($product, $categoryInfoList);

            if ($categoryInfo) {
                $product->setCategoryName($categoryInfo['category_name']);
                $product->setCategorySlug('category-' . $categoryInfo['category_id']);
            }
        }

        return $items;
    }

    /**
     * @param $product
     * @param array $categoryInfoList
     * @return bool|mixed
     */
    private function findCategoryInfoForProduct($product, array $categoryInfoList)
    {
        foreach ($categoryInfoList as $categoryInfo) {
            if ($categoryInfo['product_id'] == $product->getId()) {
                return $categoryInfo;
            }
        }

        return false;
    }
}

1 Answer 1

1
+50

You will need to add several modifications to you function.

  1. For this part of the query you mentioned
$cond[] = $items->getSelect()->getConnection()->quoteInto("category_name.store_id=?", $this->_storeManager->getStore()->getId());

you are trying to join with the catalog_category_entity_varchar table using the appropriate store id. And as expected if the category only has the default name saved, this join will not return any row. You can amend this with an OR condition as follows (make sure to add the brackets)

$cond[] = $items->getSelect()->getConnection()->quoteInto("(category_name.store_id=? OR category_name.store_id = 0)", $this->_storeManager->getStore()->getId());

This will make sure to retrieve both the default and the store view specific name if any.

  1. To correctly set the appropriate category name on the product you need another change; you need to add a sort by the store id after the sort by category id and also in descending order. This will ensure that if the category has a store view value that overrides the default, it will be put first in the result set. And since the function findCategoryInfoForProduct returns the first match it finds, this will automatically be the store view name if it exists and the default name otherwise.

The full modified code is below:

<?php

namespace Module\ProductRelated\Model;

use Magento\Store\Model\Store;

class CategoryInfoFinder
{
    /**
     * @var \Magento\Eav\Model\Config
     */
    private $eavConfig;
    
    protected $_storeManager;

    public function __construct(
        \Magento\Eav\Model\Config $eavConfig,
        \Magento\Store\Model\StoreManagerInterface $storeManager
    ) {
        $this->eavConfig = $eavConfig;
        $this->_storeManager = $storeManager;
    }

    public function addCategoryInfo(\Magento\Catalog\Model\ResourceModel\Product\Collection $items)
    {
        $productIds = [];
        foreach ($items as $product) {
            $productIds[] = $product->getId();
        }

        $select = $items->getSelect()->getConnection()->select()->from(['category_product' => 'catalog_category_product'])
            ->where('product_id in (?)', $productIds)
            ->reset(\Magento\Framework\DB\Select::COLUMNS)
            ->columns(['product_id' => 'category_product.product_id']);

        $categoryNameAttribute = $this->eavConfig->getAttribute(\Magento\Catalog\Model\Category::ENTITY, 'name');

        $cond[] = "category_name.entity_id=category_product.category_id";
        $cond[] = $items->getSelect()->getConnection()->quoteInto("category_name.attribute_id=?", $categoryNameAttribute->getId());
        $cond[] = $items->getSelect()->getConnection()->quoteInto("(category_name.store_id=? OR category_name.store_id = 0)", $this->_storeManager->getStore()->getId());           

        $select = $select->joinLeft(
            ['category_name' => 'catalog_category_entity_' . $categoryNameAttribute->getBackendType()],
            implode(' AND ', $cond),
            ['category_name' => 'value', 'category_id' => 'category_name.entity_id']
        );

        $select->order('category_product.category_id ' . \Magento\Framework\Data\Collection::SORT_ORDER_DESC);
        $select->order('category_name.store_id ' . \Magento\Framework\Data\Collection::SORT_ORDER_DESC);

        $categoryInfoList = $items->getSelect()->getConnection()->fetchAll($select);

        foreach ($items as $product) {
            $categoryInfo = $this->findCategoryInfoForProduct($product, $categoryInfoList);

            if ($categoryInfo) {
                $product->setCategoryName($categoryInfo['category_name']);
                $product->setCategorySlug('category-' . $categoryInfo['category_id']);
            }
        }

        return $items;
    }

    /**
     * @param $product
     * @param array $categoryInfoList
     * @return bool|mixed
     */
    private function findCategoryInfoForProduct($product, array $categoryInfoList)
    {
        foreach ($categoryInfoList as $categoryInfo) {
            if ($categoryInfo['product_id'] == $product->getId()) {
                return $categoryInfo;
            }
        }

        return false;
    }
}
1
  • 1
    Thanks a lot! This works perfect
    – JGeer
    Mar 14, 2023 at 11:37

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.