Attributes by price, two-part sort

[FoodAssets]

We need to change our product attributes to display by price (lowest first).

 

Product attributes do not have sort order positions assigned. We want to change product.php instead to handle this.

 

We need a two-part sort - sort by attribute name first (descending), then by product price (ascending).

 

Example of attributes:

 

Select Product
30 lb box
12 oz, #2.5 can
12 oz, (6) #2.5 cans
46 oz, #10 can
46 oz, (6) #10 cans
Select Product
Rumford 30 oz, #2.5 can

 

Select Product is always the first attribute we want displayed.

 

All the other attributes can be found in any order (how they were loaded into the database). Since we have almost a thousand attributes, we don't want to have to assign a sort order to each group. We want the REST of the attributes to sort by price (lowest to highest).

 

In product.php -

 

        $sql = 'SELECT pa.*, product_attribute_shop.*, ag.`id_attribute_group`, ag.`is_color_group`, agl.`name` AS group_name, al.`name` AS attribute_name,
                    a.`id_attribute`
                FROM `'._DB_PREFIX_.'product_attribute` pa
                '.Shop::addSqlAssociation('product_attribute', 'pa').'
                LEFT JOIN `'._DB_PREFIX_.'product_attribute_combination` pac ON pac.`id_product_attribute` = pa.`id_product_attribute`
                LEFT JOIN `'._DB_PREFIX_.'attribute` a ON a.`id_attribute` = pac.`id_attribute`
                LEFT JOIN `'._DB_PREFIX_.'attribute_group` ag ON ag.`id_attribute_group` = a.`id_attribute_group`
                LEFT JOIN `'._DB_PREFIX_.'attribute_lang` al ON (a.`id_attribute` = al.`id_attribute` AND al.`id_lang` = '.(int)$id_lang.')
                LEFT JOIN `'._DB_PREFIX_.'attribute_group_lang` agl ON (ag.`id_attribute_group` = agl.`id_attribute_group` AND agl.`id_lang` = '.(int)$id_lang.')
                WHERE pa.`id_product` = '.(int)$this->id.'
                GROUP BY pa.`id_product_attribute`, ag.`id_attribute_group`
                ORDER BY pa.`id_product_attribute`';
 

Changing ORDER BY to something like -

 

pa.'name' desc, pa.'price' asc;

 

should work, but it doesn't. We're probably missing something else.

 

Any suggestions would be much appreciated.