I try to compile a list of all product URLs for a given store. Given $productCollection prepared with store, status and visibility filter, this is how it works in Magento CE:

$catalogUrl = Mage::getResourceModel('catalog/url');

$select = $catalogUrl->getReadConnection()->select();
$select->from($catalogUrl->getMainTable(), array('request_path'));
$select->where('store_id = ?', (int)$storeId);
$select->where('product_id IN (?)', $productCollection->getAllIds());
$select->where('options IS NULL');
if (!Mage::getStoreConfig('catalog/seo/product_use_categories', $storeId)) {
    $select->where('category_id IS NULL');

foreach ($catalogUrl->getReadConnection()->fetchCol($select) as $requestPath) {
    $url = Mage::getBaseUrl('web') . $requestPath;
    // do something with $url

But how would you achieve the same in Magento EE, where the standard URL rewrite table is not used for products?

Is there a unified solution that works independent of the URL model in CE and EE?


Checking Magento's sitemap module this is the same as CE so I'd probably work from there.

This part in Mage_Sitemap_Model_Sitemap::generateXml does the actual collection output on products

$collection = Mage::getResourceModel('sitemap/catalog_product')->getCollection($storeId); $products = new Varien_Object(); $products->setItems($collection);

foreach ($products->getItems() as $item) {
    $xml = sprintf(
        htmlspecialchars($baseUrl . $item->getUrl()),

Mage_Sitemap_Model_Resource_Catalog_Product::getCollection returns a query in the catalog/product table so adding your WHERE conditions shouldn't be an issue either. The collection is already filtered on visibility, store and status.

  • Clever idea, but unfortunately it returns only one URL per product, without the category path. I need all of them, if "Use Categories Path for Product URLs" is configured. – Fabian Schmengler Sep 15 '15 at 10:03
  • @fschmengler hmm, okay, that's a challenge. I'll take another look during lunch :) – Sander Mangel Sep 15 '15 at 10:07
  • 1
    Thanks! I'm currently trying out what I can do starting with Mage::getSingleton('catalog/factory')->getProductUrlRewriteHelper()->getTableSelect() which I found in the sitemap resource model – Fabian Schmengler Sep 15 '15 at 10:10
  • It's harder than expected. The index tables only contain the product URL key. During request matching the URL is split at the last / and two separate lookups are made for category request path and product request path. – Fabian Schmengler Sep 15 '15 at 11:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.