Tweakwise feed export failed: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'l.status' in 'on clause'

[luukschakenraad]

Issue Brief

Tweakwise export fails because status attribute is not in flat tables. Only enabled products are present in the flat tables, so status is not necessary.

Environment

• [ ] PHP Version: 5.6
• [ ] Magento Version: 1.14.0.1
• [ ] Magento Enterprise: yes
• [ ] Tweakwise Export Version: 2.2.1

Steps to reproduce

Export configurable products

Actual result

Tweakwise feed export failed: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'l.status' in 'on clause'

Expected result

A valid export.

Cause:

Problem is caused by app/code/community/Emico/TweakwiseExport/Model/Writer/Productiterator.php::joinConfigurableChildren:428:

        $select->join(
            [$flatTableAlias => $entity->getFlatTableName($store->getId())],
            $connection->quoteInto("{$flatTableAlias}.entity_id = {$configurableLinkTableAlias}.product_id AND {$flatTableAlias}.status = ?", Mage_Catalog_Model_Product_Status::STATUS_ENABLED),
            []
        );

At this point a join is made on the product flat tables to the column "status". This column does not exists, because the flat table only holds active products. This join should not check product status:

        $select->join(
            [$flatTableAlias => $entity->getFlatTableName($store->getId())],
            "{$flatTableAlias}.entity_id = {$configurableLinkTableAlias}.product_id",
            []
        );

[bramstroker]

@Luuk,

This extension is used by numerous of production shops and nobody experienced this issue before. I am not 100% sure we can safely remove this join condition, as I have the feeling this has been added with a specific reason.

With a default Magento installation the status column should be available flat tables. Apparently in your installation this is not the case. I would suggest that you set used_in_product_listing flag for the status attribute to true in the backend. Than rerun the flat indexer making the status field available in the flat table. This should resolve your issue.

[eriktimmers]

@bramstroker

The status column is available is flat tables in newer Magento installations only. It seems that Magento has changed the working of the status column somewhere between versions 1.14.1 and 1.14.2. (I found a 1.14.1.0 installations without, and 1.14.2.1 installations with status column) The used_in_product_listing flag doesn't work for status in those older Magento versions. This is an issue because we stil support some customers with versions older than 1.14.2.

Reference: model Mage_Catalog_Model_Product_Status, the couple of functions after comment "Eav Source methods for Flat data", prior and after update mentioned before.

[Luuk]

Please top tagging me hahaha @bramstroker

[edwinljacobs]

I know this has been a very long time ago but in newer Magento1 versions the status model implements a getFlatUpdate select and something in the lines of getFlatColumns. These add the status column to the flat table. I assume that this is no longer an issue or is solved internally. Therefor I am closing this issue.

With kind Regards

[Luuk]

howis, ik hoor hier niet bij xD