Group and List products grouped Magento

Question

I want to get the products in catalog product list grouped.

I have 4 configurable products and i want to group them to a 1 product showing in catalog.

For that i have created an Attribute Text (assoccode) to associate each configurable product.

Config 1: code001 - assoccode: code001
Config 2: code002 - assoccode: code001
Config 3: code003 - assoccode: code001
Config 4: code004 - assoccode: code001

My issue is that when i have a single Configurable product that doesn't have another option it is not showing in the catalog list or if is a simple product to or if i activate the Anchor Category to Yes i got this error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'assoccode' in 'group statement'

Is there another option to group the products by an attribute in the catalog product list?

and i have extended the product collection as below:

class Module_GroupingConfig_Model_Productcollection extends Mage_Catalog_Model_Resource_Product_Collection
{

    protected $_disabled = false;
    protected $_flatEnabled = array();

    public function getFlatHelper()
    {
        return Mage::helper('catalog/product_flat');
    }

    protected function isEnabled()
    {
        return !Mage::app()->getStore()->isAdmin() && !$this->_disabled;
    }

    public function setDisableGroupBy()
    {
        $this->_disabled = true;
    }

    public function isEnabledFlat()
    {
        // Flat Data can be used only on frontend
        if (Mage::app()->getStore()->isAdmin()) {
            return false;
        }
        $storeId = $this->getStoreId();
        if (!isset($this->_flatEnabled[$storeId])) {
            $flatHelper = $this->getFlatHelper();
            $this->_flatEnabled[$storeId] = $flatHelper->isAvailable() && $flatHelper->isBuilt($storeId);
        }
        return $this->_flatEnabled[$storeId];
    }

    protected function _beforeLoad()
    {
        if ($this->isEnabled()) {
            if ($this->isEnabledFlat()) {

                $this->getSelect()->group('assoccode');
                $this->getSelect()->columns(array('assoccode_color_ids' => new Zend_Db_Expr("GROUP_CONCAT(DISTINCT color ORDER BY color_value DESC SEPARATOR ',')")));

            } else {

                $this->joinAttribute('assoccode', 'catalog_product/assoccode', 'entity_id');
                $this->joinAttribute('color', 'catalog_product/color', 'entity_id');

                $this->getSelect()->group('assoccode');
                $this->getSelect()->columns(array('assoccode_color_ids' => new Zend_Db_Expr("GROUP_CONCAT(DISTINCT at_color.value ORDER BY at_color.value DESC SEPARATOR ',')")));

            }

            $this->getSelect()->columns(array('assoccode_count' => new Zend_Db_Expr('COUNT(*)')));
        }
        return parent::_beforeLoad();
    }

    public function getSelectCountSql()
    {
        if ($this->isEnabled()) {
            $this->_renderFilters();

            if ($this->isEnabledFlat()) {
                $countSelect = $this->_getClearSelect()
                    ->columns('COUNT(DISTINCT assoccode) AS cnt')
                    ->resetJoinLeft();
            } else {
                $countSelect = $this->_getClearSelect()
                    ->columns('COUNT(DISTINCT at_assoccode.value) AS cnt')
                    ->resetJoinLeft();
            }

            $countSelect->reset(Zend_Db_Select::GROUP);

            return $countSelect;
        }
        return parent::getSelectCountSql();
    }

}

Any help is very appreciated.

It looks as the bad builded query. Try to log the query (ex.: Mage::log($this->getSelectSql(true)) and then execute it in your SQL admin. It would tell you what is wrong. If you use FLAT then make sure that "assoccode" column does exist, if not, make sure that there is enabled "Used in Product Listing" in the "Manage attributes" and then rebuild indexes. — Jiří Chmiel
Hi, i have passed trough this steps, on log sql i see the group_by assoccode and in the table flat is the assocode to. — Dario
please edit your question to add the full query as logged in the log — OSdave
the mysql query that gives you the error, SELECT ..., you said 2 comments earlier you had it — OSdave

Adrian Romero Adrian Romero · Accepted Answer · 2014-08-13T00:21:22

1 .-You need create a attribute product in admin, catalog -> attributes -> manage attributes after, create a "set attributes" , once made it , associate to configurable product,

2.-Create Custom Module Block View and List , that use attribute render group product as view to select

3.-Into Detail Product View use Custom Collection to Show Group Products, and options

4.-Create Custom Module Add to Cart Block , to add multiples products groups

Group and List products grouped Magento

1 Answers