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();
$joinCondition = new \Zend_Db_Expr("cat.{$linkField} = default_value.{$linkField}");
$select->from(['cat' => $this->getEntityMetaData(CategoryInterface::class)->getEntityTable()], [$entityIdField])->joinInner(['default_value' => $nameAttr->getBackendTable()], $joinCondition, [])->where("cat.{$entityIdField} != ?", $rootCategoryId)->where('default_value.store_id = ?', 0)->where('default_value.attribute_id = ?', (int) $nameAttr->getAttributeId())->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("default_value.{$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("default_value.{$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;
}