CE 1.9 Sitemap Generation Issue

[kieljohn]

Forgive me if this is not the right place, we're having issues generating sitemaps in the Google Sitemap function of Magento CE 1.9.0.1

exception.log Exception message: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.entity_id' in 'on clause'

After some debugging it looks like it is being caused by the GroupsCatalog plugin, the raw SQL being generated is:

SELECT `main_table`.`entity_id`, 
       `url_rewrite`.`request_path` 
FROM   `catalog_category_entity` AS `main_table` 
       LEFT JOIN `core_url_rewrite` AS `url_rewrite` 
              ON url_rewrite.category_id = main_table.entity_id 
                 AND url_rewrite.is_system = 1 
                 AND url_rewrite.store_id = 1 
                 AND url_rewrite.id_path LIKE 'category/%' 
       INNER JOIN `catalog_category_entity_int` AS `t1_is_active` 
               ON main_table.entity_id = t1_is_active.entity_id 
                  AND t1_is_active.store_id = 0 
       LEFT JOIN `catalog_category_entity_int` AS `t2_is_active` 
              ON t1_is_active.entity_id = t2_is_active.entity_id 
                 AND t1_is_active.attribute_id = t2_is_active.attribute_id 
                 AND t2_is_active.store_id = 1 
       INNER JOIN `groupscatalog_category_idx` 
               ON groupscatalog_category_idx.catalog_entity_id = e.entity_id 
                  AND groupscatalog_category_idx.group_id = 0 
                  AND groupscatalog_category_idx.store_id = '1' 
WHERE  ( main_table.path LIKE '1/2/%' ) 
       AND ( t1_is_active.attribute_id = '42' ) 
       AND ( ( IF(t2_is_active.value_id > 0, t2_is_active.value, 
           t1_is_active.value) ) = 1 ) 

Which refers to e.entity_id in the groupscatalog_category_idx table

[sepanyol]

We have the same Issue here:

SELECT
  `main_table`.`entity_id`, 
  `url_rewrite`.`request_path` 
FROM   `catalog_category_entity` AS `main_table` 
  LEFT JOIN    `core_url_rewrite` AS `url_rewrite` 
    ON       url_rewrite.category_id=main_table.entity_id 
      AND       url_rewrite.is_system=1 
      AND       url_rewrite.store_id = 1 
      AND       url_rewrite.id_path LIKE 'category/%' 
  INNER JOIN     `catalog_category_entity_int` AS `t1_is_active` 
    ON       main_table.entity_id=t1_is_active.entity_id 
      AND       t1_is_active.store_id=0 
  LEFT JOIN     `catalog_category_entity_int` AS `t2_is_active` 
    ON       t1_is_active.entity_id = t2_is_active.entity_id 
      AND       t1_is_active.attribute_id = t2_is_active.attribute_id 
      AND       t2_is_active.store_id = 1 
  INNER JOIN     `groupscatalog_category_idx` ON groupscatalog_category_idx.catalog_entity_id=e.entity_id 
    AND     groupscatalog_category_idx.group_id=0 
    AND     groupscatalog_category_idx.store_id='1' 
WHERE   (main_table.path LIKE '1/2/%') 
  AND   (t1_is_active.attribute_id='42') 
  AND   ((IF(t2_is_active.value_id > 0, t2_is_active.value, t1_is_active.value))=1)

[sepanyol]

How to avoid this Part

    protected function _addGroupsCatalogFilterToSelect(
        Zend_Db_Select $select, $table, $groupId, $storeId, $entityField = null
    ) {
        if (! $entityField) {
            $entityField = 'e.entity_id';
        }

in File: app/code/community/Netzarbeiter/GroupsCatalog2/Model/Resource/Filter.php on line 284?

just did a dirty solution and renamed e into main_table

    protected function _addGroupsCatalogFilterToSelect(
        Zend_Db_Select $select, $table, $groupId, $storeId, $entityField = null
    ) {
        if (! $entityField) {
            $entityField = 'main_table.entity_id';
        }

seems to work

[Vinai]

Thanks for the report, I can confirm this issue. The aliasing of the table has changed somewhere between version 1.7.0.2 and 1.8.0.1. I hope to provide a fix soon.

References: https://github.com/OpenMage/magento-mirror/blob/1.7.0.2/app/code/core/Mage/Sitemap/Model/Resource/Catalog/Category.php#L93 https://github.com/OpenMage/magento-mirror/blob/1.8.1.0/app/code/core/Mage/Sitemap/Model/Resource/Catalog/Category.php#L69

[Vinai]

If you could confirm the fix in version 0.3.6 that I just pushed works and comment here that would be great.

[Vinai]

I believe the issue has been fixed. There has been no feedback for about a month, so I'll just close the issue now.