Category name from store view, only when it exist else default value - Magento2.4

Question

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;
    }
}

Answer 1

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.

2. 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;
    }
}