Product collection Sort by Custom attribute with yes/no Option

Question

Hi I have created one attribute called archive whose type is Yes/No. So archive attribute for every product has either Yes/No.

Now I want to sort product collection so that all products whose archive value=YES should come at the end.

I tried:

$collection = Mage::getModel('catalog/product')->getCollection()
             ->addStoreFilter(Mage::app()->getStore()->getId()) 
             ->addAttributeToSelect('*')
             ->addAttributeToFilter('type_id', 'configurable')
             ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left')
             ->addAttributeToFilter('category_id', array(
                 array('finset' => '37'))
               )
             ->addAttributeToSort('archive', 'desc');

But it does not work.

Printing $collection->getSelect() results in:

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `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` FROM `catalog_product_entity` AS `e` 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.category_id = '37' 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 WHERE 
(`e`.`type_id` = 'configurable')

Answer 1

Make sure the archive attribute is added to the flat product data by setting Used in Product Listing to yes. Also it's probably better to load the product collection via the catalog/category model instead of joining another table.

Also, to have yes come as last make the order ascending instead of descending

$category = Mage::getModel('catalog/category')->load(37);
$collection = $category->getProductCollection()
   ->addStoreFilter(Mage::app()->getStore()->getId())
   ->addAttributeToFilter('type_id', 'configurable')
   ->addAttributeToSort('archive', 'asc');