I am working in magento 2 project.

I need to load product collection by url_key and specific store id. I tried but I am getting issue in store id. I search in stack but didn't get solution.

My Query :

$url = $store->getStore()->getCurrentUrl();
$url_path = parse_url($url, PHP_URL_PATH);
$url_path_parts = array_filter(explode('/', $url_path));
$currenturl = $url_path_parts[1];
$oRewrite = $objectManager->create("Magento\Catalog\Model\Product")->getCollection();
$oRewrite->addAttributeToFilter('url_key', $currenturl, 'left');
$oRewrite->addStoreFilter(0); // I tried to set store id but not worked

If I print query of above query I am getting following query:

SELECT `e`.*, IF(at_url_key.value_id > 0, at_url_key.value, at_url_key_default.value) AS `url_key` FROM `catalog_product_entity` AS `e` LEFT JOIN `catalog_product_entity_varchar` AS `at_url_key_default` ON (`at_url_key_default`.`entity_id` = `e`.`entity_id`) AND (`at_url_key_default`.`attribute_id` = '119') AND `at_url_key_default`.`store_id` = 0 LEFT JOIN `catalog_product_entity_varchar` AS `at_url_key` ON (`at_url_key`.`entity_id` = `e`.`entity_id`) AND (`at_url_key`.`attribute_id` = '119') AND (`at_url_key`.`store_id` = 2) WHERE (IF(at_url_key.value_id > 0, at_url_key.value, at_url_key_default.value) = 'holle-pouchy-apfel-mit-karotte-pastinake-90g')

If I add addAttributeToFilter then in query I am getting Inner Join with store id 0 and Left join with store id 2.

I want to get result from store 0. Is it possible to set store id 0 in all join.

2 Answers 2


Use below code to load any product by filter url key and store id.

    $storeid = 1;
    $objectManager     = \Magento\Framework\App\ObjectManager::getInstance();

    $collection        = $objectManager->create('Magento\Catalog\Model\ResourceModel\Product\Collection');

    $productcollection = $collection->addAttributeToSelect('*')
                ['attribute' => 'url_key', 'eq' => 'test112'], // Color filter

   $productInfo = $productcollection->getData();
  • Hi, I have tried but not working, As I need to fetch from store id 0. If i write store id 1 then it is working but I need to fetch record from store id 0. Oct 1, 2018 at 12:06
  • Please check my mysql query, in inner join store id is already 0, but in left join the store id is 2 that what i want to change. I want to change 2 to 0 in left join Oct 1, 2018 at 12:07
  • @DavidCoder if you want to get product from store id 0 then I think no need to mentioned store id there. magento by default will get all records from store id 0(default store) if you don't specify it, please have a look magento.stackexchange.com/a/135481/55928
    – Hitesh
    Oct 1, 2018 at 12:14
  • Yes but still it print the same query LEFT JOIN catalog_product_entity_varchar AS at_url_key ON (at_url_key.entity_id = e.entity_id) AND (at_url_key.attribute_id = '119') AND (at_url_key.store_id = 2) here store id is 2. Oct 1, 2018 at 12:22
  • store id is coming from $oRewrite->addAttributeToFilter('url_key', $currenturl); default Oct 1, 2018 at 12:22
addAttributeToFilter('url_key', $currenturl);   
  • I have already tried but not working. Oct 1, 2018 at 12:05

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.