private function prepareCategoryNameSelect($loadCategoryIds, $storeId)
    {
        $rootCategoryId = (int) $this->storeManager->getStore($storeId)->getRootCategoryId();
        $this->categoryNameCache[$storeId][$rootCategoryId] = '';

        $nameAttr      = $this->getCategoryNameAttribute();
        $useNameAttr   = $this->getUseNameInSearchAttribute();
        $entityIdField = $this->getEntityMetaData(CategoryInterface::class)->getIdentifierField();
        $linkField     = $this->getEntityMetaData(CategoryInterface::class)->getLinkField();
        $select        = $this->connection->select();

        $conditions    = [
            "cat.{$linkField} = default_value.{$linkField}",
            "default_value.store_id=0",
            "default_value.attribute_id = " . (int) $nameAttr->getAttributeId(),
        ];

        $joinCondition = new \Zend_Db_Expr(implode(" AND ", $conditions));
        $select->from(['cat' => $this->getEntityMetaData(CategoryInterface::class)->getEntityTable()], [$entityIdField])
            ->joinLeft(['default_value' => $nameAttr->getBackendTable()], $joinCondition, [])
            ->where("cat.$entityIdField != ?", $rootCategoryId)
            ->where("cat.$entityIdField IN (?)", $loadCategoryIds);

        // Join to check for use_name_in_product_search.
        $joinUseNameCond = sprintf(
            "default_value.$linkField = use_name_default_value.$linkField" .
            " AND use_name_default_value.attribute_id = %d AND use_name_default_value.store_id = %d",
            (int) $useNameAttr->getAttributeId(),
            0
        );
        $select->joinLeft(['use_name_default_value' => $useNameAttr->getBackendTable()], $joinUseNameCond, []);

        if ($this->storeManager->isSingleStoreMode()) {
            $select->columns(['name' => 'default_value.value']);
            $select->columns(['use_name' => 'COALESCE(use_name_default_value.value,1)']);

            return $select;
        }

        // Multi store additional join to get scoped name value.
        $joinStoreNameCond = sprintf(
            "cat.$linkField = store_value.$linkField" .
            " AND store_value.attribute_id = %d AND store_value.store_id = %d",
            (int) $nameAttr->getAttributeId(),
            (int) $storeId
        );
        $select->joinLeft(['store_value' => $nameAttr->getBackendTable()], $joinStoreNameCond, [])
            ->columns(['name' => 'COALESCE(store_value.value,default_value.value, "")']);

        // Multi store additional join to get scoped "use_name_in_product_search" value.
        $joinUseNameStoreCond = sprintf(
            "cat.$linkField = use_name_store_value.$linkField" .
            " AND use_name_store_value.attribute_id = %d AND use_name_store_value.store_id = %d",
            (int) $useNameAttr->getAttributeId(),
            (int) $storeId
        );
        $select->joinLeft(['use_name_store_value' => $useNameAttr->getBackendTable()], $joinUseNameStoreCond, [])
            ->columns(['use_name' => 'COALESCE(use_name_store_value.value,use_name_default_value.value,1)']);

        return $select;
    }
private function prepareCategoryNameSelect($loadCategoryIds, $storeId)
    {
        $rootCategoryId = (int) $this->storeManager->getStore($storeId)->getRootCategoryId();
        $this->categoryNameCache[$storeId][$rootCategoryId] = '';

        $nameAttr      = $this->getCategoryNameAttribute();
        $useNameAttr   = $this->getUseNameInSearchAttribute();
        $entityIdField = $this->getEntityMetaData(CategoryInterface::class)->getIdentifierField();
        $linkField     = $this->getEntityMetaData(CategoryInterface::class)->getLinkField();
        $select        = $this->connection->select();

        $conditions    = [
            "cat.{$linkField} = default_value.{$linkField}",
            "default_value.store_id=0",
            "default_value.attribute_id = " . (int) $nameAttr->getAttributeId(),
        ];

        $joinCondition = new \Zend_Db_Expr(implode(" AND ", $conditions));
        $select->from(['cat' => $this->getEntityMetaData(CategoryInterface::class)->getEntityTable()], [$entityIdField])
            ->joinLeft(['default_value' => $nameAttr->getBackendTable()], $joinCondition, [])
            ->where("cat.$entityIdField != ?", $rootCategoryId)
            ->where("cat.$entityIdField IN (?)", $loadCategoryIds);

        // Join to check for use_name_in_product_search.
        $joinUseNameCond = sprintf(
            "default_value.$linkField = use_name_default_value.$linkField" .
            " AND use_name_default_value.attribute_id = %d AND use_name_default_value.store_id = %d",
            (int) $useNameAttr->getAttributeId(),
            0
        );
        $select->joinLeft(['use_name_default_value' => $useNameAttr->getBackendTable()], $joinUseNameCond, []);

        if ($this->storeManager->isSingleStoreMode()) {
            $select->columns(['name' => 'default_value.value']);
            $select->columns(['use_name' => 'COALESCE(use_name_default_value.value,1)']);

            return $select;
        }

        // Multi store additional join to get scoped name value.
        $joinStoreNameCond = sprintf(
            "cat.$linkField = store_value.$linkField" .
            " AND store_value.attribute_id = %d AND store_value.store_id = %d",
            (int) $nameAttr->getAttributeId(),
            (int) $storeId
        );
        $select->joinLeft(['store_value' => $nameAttr->getBackendTable()], $joinStoreNameCond, [])
            ->columns(['name' => 'COALESCE(store_value.value,default_value.value, "")']);

        // Multi store additional join to get scoped "use_name_in_product_search" value.
        $joinUseNameStoreCond = sprintf(
            "cat.$linkField = use_name_store_value.$linkField" .
            " AND use_name_store_value.attribute_id = %d AND use_name_store_value.store_id = %d",
            (int) $useNameAttr->getAttributeId(),
            (int) $storeId
        );
        $select->joinLeft(['use_name_store_value' => $useNameAttr->getBackendTable()], $joinUseNameStoreCond, [])
            ->columns(['use_name' => 'COALESCE(use_name_store_value.value,use_name_default_value.value,1)']);

        return $select;
    }
private function prepareCategoryNameSelect ( $ loadCategoryIds , $ storeId ) { $ rootCategoryId = ( int ) $ this -> storeManager -> getStore ( $ storeId ) -> getRootCategoryId ( ) ; $ this -> categoryNameCache [ $ storeId ] [ $ rootCategoryId ] = '' ; $ nameAttr = $ this -> getCategoryNameAttribute ( ) ; $ useNameAttr = $ this -> getUseNameInSearchAttribute ( ) ; $ entityIdField = $ this -> getEntityMetaData ( CategoryInterface :: class ) -> getIdentifierField ( ) ; $ linkField = $ this -> getEntityMetaData ( CategoryInterface :: class ) -> getLinkField ( ) ; $ select = $ this -> connection -> select ( ) ; $ conditions = [ "cat.{$linkField} = default_value.{$linkField}" , "default_value.store_id=0" , "default_value.attribute_id = " . ( int ) $ nameAttr -> getAttributeId ( ) , ] ; $ joinCondition = new \ Zend_Db_Expr ( implode ( " AND " , $ conditions ) ) ; $ select -> from ( [ 'cat' => $ this -> getEntityMetaData ( CategoryInterface :: class ) -> getEntityTable ( ) ] , [ $ entityIdField ] ) -> joinLeft ( [ 'default_value' => $ nameAttr -> getBackendTable ( ) ] , $ joinCondition , [ ] ) -> where ( "cat.$entityIdField != ?" , $ rootCategoryId ) -> where ( "cat.$entityIdField IN (?)" , $ loadCategoryIds ) ; // Join to check for use_name_in_product_search. $ joinUseNameCond = sprintf ( "default_value.$linkField = use_name_default_value.$linkField" . " AND use_name_default_value.attribute_id = %d AND use_name_default_value.store_id = %d" , ( int ) $ useNameAttr -> getAttributeId ( ) , 0 ) ; $ select -> joinLeft ( [ 'use_name_default_value' => $ useNameAttr -> getBackendTable ( ) ] , $ joinUseNameCond , [ ] ) ; if ( $ this -> storeManager -> isSingleStoreMode ( ) ) { $ select -> columns ( [ 'name' => 'default_value.value' ] ) ; $ select -> columns ( [ 'use_name' => 'COALESCE(use_name_default_value.value,1)' ] ) ; return $ select ; } // Multi store additional join to get scoped name value. $ joinStoreNameCond = sprintf ( "cat.$linkField = store_value.$linkField" . " AND store_value.attribute_id = %d AND store_value.store_id = %d" , ( int ) $ nameAttr -> getAttributeId ( ) , ( int ) $ storeId ) ; $ select -> joinLeft ( [ 'store_value' => $ nameAttr -> getBackendTable ( ) ] , $ joinStoreNameCond , [ ] ) -> columns ( [ 'name' => 'COALESCE(store_value.value,default_value.value, "")' ] ) ; // Multi store additional join to get scoped "use_name_in_product_search" value. $ joinUseNameStoreCond = sprintf ( "cat.$linkField = use_name_store_value.$linkField" . " AND use_name_store_value.attribute_id = %d AND use_name_store_value.store_id = %d" , ( int ) $ useNameAttr -> getAttributeId ( ) , ( int ) $ storeId ) ; $ select -> joinLeft ( [ 'use_name_store_value' => $ useNameAttr -> getBackendTable ( ) ] , $ joinUseNameStoreCond , [ ] ) -> columns ( [ 'use_name' => 'COALESCE(use_name_store_value.value,use_name_default_value.value,1)' ] ) ; return $ select ; }
Prepare SQL query to retrieve category names

@param array $loadCategoryIds Category ids to load name for
@param int   $storeId         The current store Id

@return \Magento\Framework\DB\Select
Prepare SQL query to retrieve category names