How do we keep the products with NULL value at the end of listing

Question

On product listing page if i apply sort by color ,i need the products which have the color value as NULL ,should be displayed at the bottom of the listing page

Answer 1

I have achieved this, by rewriting Mage_Eav_Model_Entity_Attribute_Source_Table class. Override addValueSortToCollection function, simply replace the following statement,

$collection->getSelect()
            ->order("{$this->getAttribute()->getAttributeCode()} {$dir}");

With,

// Attribute Code here
if($this->getAttribute()->getAttributeCode() != 'color'){
        $collection->getSelect()->order("{$this->getAttribute()->getAttributeCode()} {$dir}");
    }else{
        $table = Mage::getSingleton('core/resource')->getTableName('eav_attribute_option');
        $collection->getSelect()
                   ->joinLeft(array('so' => $table), 'IF('.$valueTable2.'.value_id > 0, '.$valueTable2.'.value, '.$valueTable1.'.value) = so.option_id')
                   ->order(array('ISNULL(so.sort_order),so.sort_order '.$dir)); //ISNULL(field) to keep the null value rows at the end
    }