custom attribute yes/no add to sort by in product list

Question

i have one attribute called most popular with defatul Yes/no configuration by magento i followed

1) To add using Magento Backend you need to follow below steps

a) Goto Catalog->Attributes->Manage Attributes

b) Select the attribute you want to add in Sort By option. Suppose you need to add a Sort By popular Option.

c) Goto Brand Attribute and check for "Used for Sorting in Product Listing".

d) Make it YES

but still i didn't get it work.

i have trace magento's collection with this sort but its not adding any order in collection as below for popular sort,

SELECT `e`.*, `search_result`.`relevance`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position` FROM `catalog_product_entity` AS `e` INNER JOIN `catalogsearch_result` AS `search_result` ON search_result.product_id=e.entity_id AND search_result.query_id='2' INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(3, 4) AND cat_index.category_id = '2' LIMIT 12

Price sorting is correct as below

SELECT `e`.*, `search_result`.`relevance`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position` FROM `catalog_product_entity` AS `e` INNER JOIN `catalogsearch_result` AS `search_result` ON search_result.product_id=e.entity_id AND search_result.query_id='2' INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(3, 4) AND cat_index.category_id = '2' ORDER BY `price_index`.`min_price` asc LIMIT 12

is there any solution to make this work ?

your help will be very appreciated.

Answer 1

Printing the query that actually retrieves the values for for the Product Listing Sort By field we can see what criteria an attribute has to match

SELECT `main_table`.*,
   IF(al.value IS NULL, main_table.frontend_label, al.value) as `store_label` 
FROM 
   `eav_attribute` AS `main_table` 
INNER JOIN 
   `catalog_eav_attribute` AS `additional_table` 
ON 
   main_table.attribute_id = additional_table.attribute_id 
LEFT JOIN 
   `eav_attribute_label` AS `al` 
ON 
   al.attribute_id = main_table.attribute_id
AND 
  al.store_id = 0 WHERE ( 
   main_table.entity_type_id = 4 
  ) 
AND 
( 
  additional_table.used_for_sort_by = 1
)

It checks for the type_id which should be for products, the store ID which should be global and the used_for_sort_by should be 1.

Just tried it out and my attribute does show up in the dropdown.

Since sorting is done in Ascending order on the frontend please be aware the products with this attribute set to no will come first.

Answer 2

After debugging and back trace find way to add sort in boolean attribute to override Mage_Eav_Model_Entity_Attribute_Source_Boolean

config.xml

<config>   
    <global>
       <models>                 
           <eav>
              <rewrite> 
                <entity_attribute_source_boolean>Yournamespace_Eav_Model_Entity_Attribute_Source_Boolean</entity_attribute_source_boolean> 
              </rewrite>
            </eav>
        </models>        
     </global>
</config>

Model Boolean.php

class Yournamespace_Eav_Model_Entity_Attribute_Source_Boolean extends Mage_Eav_Model_Entity_Attribute_Source_Boolean
{
    /**
     * Add Value Sort To Collection Select
     *
     * @param Mage_Eav_Model_Entity_Collection_Abstract $collection
     * @param string $dir
     *
     * @return Mage_Eav_Model_Entity_Attribute_Source_Table
     */
    public function addValueSortToCollection($collection, $dir = Varien_Db_Select::SQL_ASC)
    {
        $valueTable1 = $this->getAttribute()->getAttributeCode() . '_t1';
        $valueTable2 = $this->getAttribute()->getAttributeCode() . '_t2';
        $collection->getSelect()
                ->joinLeft(
                        array($valueTable1 => $this->getAttribute()->getBackend()->getTable()), "e.entity_id={$valueTable1}.entity_id"
                        . " AND {$valueTable1}.attribute_id='{$this->getAttribute()->getId()}'"
                        . " AND {$valueTable1}.store_id=0", array())
                ->joinLeft(
                        array($valueTable2 => $this->getAttribute()->getBackend()->getTable()), "e.entity_id={$valueTable2}.entity_id"
                        . " AND {$valueTable2}.attribute_id='{$this->getAttribute()->getId()}'"
                        . " AND {$valueTable2}.store_id='{$collection->getStoreId()}'", array()
        );
        $valueExpr = $collection->getSelect()->getAdapter()
                ->getCheckSql("{$valueTable2}.value_id > 0", "{$valueTable2}.value", "{$valueTable1}.value");

        Mage::getResourceModel('eav/entity_attribute_option')
                ->addOptionValueToCollection($collection, $this->getAttribute(), $valueExpr);

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

        return $this;
    }

}

hope this will sure help to someone.