List Product URLs in Magento Enterprise

Question

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?

Answer 1

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(
        '<url><loc>%s</loc><lastmod>%s</lastmod><changefreq>%s</changefreq><priority>%.1f</priority></url>',
        htmlspecialchars($baseUrl . $item->getUrl()),
        $date,
        $changefreq,
        $priority
    );
    $io->streamWrite($xml);
}

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.