2

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.

1
  • @ liyakat: You get the proper solution of your issue, if yes please share with me I also facing same issue in M2 Feb 10, 2020 at 7:05

2 Answers 2

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.

3
  • sander, thanks for your valuable ans,but i didnt get actually where to see actual query effect and why its not working. i have already override Mage_Catalog_Block_Product_List_Toolbar and print above sql from this file. will you please tell me what to do to solve yes/no attribute sort option whether by asc or desc
    – liyakat
    Sep 8, 2015 at 7:38
  • If it's not working in the frontend that could be a lot of things. Custom module that is causing it, template, etc. Try to do sme debugging / backtracing. I wouldn't know where to start Sep 8, 2015 at 8:34
  • thanks a lot sandar, no worries. got solution from magento core. will sure add ans if its working for me,:)
    – liyakat
    Sep 8, 2015 at 8:35
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.