How to order/sort attribute combination names alphabetically

[lobogc]

Hello everyone :-) A-Z order is supposedly the default order for combination names. However, I am running PS 1.5.6.1 and thay are currently ordered by ID instead of name, which is a mess for me. I know I can use "position" field to force a specific combination order, but it's not possible for me to use that feature because of other technical reasons. I just need to alter the SQL query in classes/product.php for attribute combinations to be sorted alphabetically. Could anyone give a hand, please? ;-)

 

I have tried thing like this with no luck, at line 1869 inside public function "getAttributeCombinations", and line 1913 inside public function "getAttributeCombinationsById":

 

From this: 

ORDER BY pa.`id_product_attribute`';

 

To this: 

ORDER BY agl.`public_name`, agl.`name`, al.`name`';

 

There's no change at all (not even an error, hehehe). Attribute combinations keep on being sorted by ID. Am I looking at the wrong function? Thank youuuuu!! :-))

 

 

 

[musicmaster]

Switching "PS_DEBUG_PROFILING" on in defines.inc.php gave as result that the following mysql queries are run that address the product_attribute_combination table (I am using 1.5.6.1 too):

SELECT SQL_NO_CACHE DISTINCT la.`id_attribute`, la.`url_name` as `attribute` FROM `ps_attribute` a LEFT JOIN `ps_product_attribute_combination` pac ON (a.`id_attribute` = pac.`id_attribute`) LEFT JOIN `ps_product_attribute` pa ON (pac.`id_product_attribute` = pa.`id_product_attribute`) INNER JOIN ps_product_attribute_shop product_attribute_shop ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1) LEFT JOIN `ps_layered_indexable_attribute_lang_value` la ON (la.`id_attribute` = a.`id_attribute` AND la.`id_lang` = 6) WHERE la.`url_name` IS NOT NULL AND la.`url_name` != '' AND pa.`id_product` = 1881
in C:\xampp\htdocs\_snoepje\classes\Product.php:5008

SELECT SQL_NO_CACHE ag.`id_attribute_group`, ag.`is_color_group`, agl.`name` AS group_name, agl.`public_name` AS public_group_name, a.`id_attribute`, al.`name` AS attribute_name, a.`color` AS attribute_color, product_attribute_shop.`id_product_attribute`, IFNULL(stock.quantity, 0) as quantity, product_attribute_shop.`price`, product_attribute_shop.`ecotax`, product_attribute_shop.`weight`, product_attribute_shop.`default_on`, pa.`reference`, product_attribute_shop.`unit_price_impact`, product_attribute_shop.`minimal_quantity`, product_attribute_shop.`available_date`, ag.`group_type` FROM `ps_product_attribute` pa INNER JOIN ps_product_attribute_shop product_attribute_shop ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1) LEFT JOIN ps_stock_available stock ON (stock.id_product = pa.id_product AND stock.id_product_attribute = IFNULL(`pa`.id_product_attribute, 0) AND stock.id_shop = 1 ) LEFT JOIN `ps_product_attribute_combination` pac ON (pac.`id_product_attribute` = pa.`id_product_attribute`) LEFT JOIN `ps_attribute` a ON (a.`id_attribute` = pac.`id_attribute`) LEFT JOIN `ps_attribute_group` ag ON (ag.`id_attribute_group` = a.`id_attribute_group`) LEFT JOIN `ps_attribute_lang` al ON (a.`id_attribute` = al.`id_attribute`) LEFT JOIN `ps_attribute_group_lang` agl ON (ag.`id_attribute_group` = agl.`id_attribute_group`) INNER JOIN ps_attribute_shop attribute_shop ON (attribute_shop.id_attribute = a.id_attribute AND attribute_shop.id_shop = 1) WHERE pa.`id_product` = 1881 AND al.`id_lang` = 6 AND agl.`id_lang` = 6 GROUP BY id_attribute_group, id_product_attribute ORDER BY ag.`position` ASC, a.`position` ASC, agl.`name` ASC
in C:\xampp\htdocs\_snoepje\classes\Product.php:3103

SELECT SQL_NO_CACHE pa.id_product_attribute,l.name,cp.quantity from ps_product_attribute pa LEFT JOIN ps_product_attribute_combination c on pa.id_product_attribute=c.id_product_attribute LEFT JOIN ps_attribute a on a.id_attribute=c.id_attribute LEFT JOIN ps_attribute_lang l on l.id_attribute=c.id_attribute AND l.id_lang='6' LEFT JOIN ps_cart_product cp on cp.id_product=pa.id_product AND cp.id_product_attribute=c.id_product_attribute AND id_cart='4837' WHERE pa.id_product='1881' ORDER BY l.name
in C:\xampp\htdocs\_snoepje\controllers\front\ProductController.php:288

SELECT SQL_NO_CACHE DISTINCT a.`id_attribute`, a.`id_attribute_group`, al.`name` as `attribute`, agl.`name` as `group` FROM `ps_attribute` a LEFT JOIN `ps_attribute_lang` al ON (a.`id_attribute` = al.`id_attribute` AND al.`id_lang` = 6) LEFT JOIN `ps_attribute_group_lang` agl ON (a.`id_attribute_group` = agl.`id_attribute_group` AND agl.`id_lang` = 6) LEFT JOIN `ps_product_attribute_combination` pac ON (a.`id_attribute` = pac.`id_attribute`) LEFT JOIN `ps_product_attribute` pa ON (pac.`id_product_attribute` = pa.`id_product_attribute`) INNER JOIN ps_product_attribute_shop product_attribute_shop ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1) INNER JOIN ps_attribute_shop attribute_shop ON (attribute_shop.id_attribute = pac.id_attribute AND attribute_shop.id_shop = 1) WHERE pa.`id_product` = 1881
in C:\xampp\htdocs\_snoepje\classes\Product.php:5070

 I hope this helps you further. If you don't find it with these: there are much more queries that just call the product_attribute table.

 

Please let us know what you find.

[aliaspt]

Anyone could explain how to fix this issue in a little more clear language for those who don't know mysql?

I'm having the same problem with 273 attributes being sorted by ID number rather than name.

[aliaspt]

Anyone please??

[gr_fenix]

Hi, you solve it?

 

Thanks!

[lobogc]

At the end I had to hire a programmer to alphabetically sort combinations. I chose the programmer of my template, which everything quite straightforward.

[wayne_f]

If you have some basic MySQL Query experience the following will sort selected Attribute groups by name and assign a new position Value.

 

Read the comments carefully and as always backup you data

 

**************

# I am using 2 Temporay Tables the next two commands test and drop the tables if they exist in the database
# I have found that making temporary tables allows me to breakup the steps for easier testing of each step.

DROP TABLE IF EXISTS tmp_Attribute_Color_Sorted ;
DROP TABLE IF EXISTS tmp_Attribute_Color_Sorted_With_Position ;

# Here I create a temporay table with the selected attribute group and sort them in name ascending order in this case I am only using one language
# Change the select statment to limit the group or language you want to select

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_Attribute_Color_Sorted AS (Select ps_attribute.id_attribute, ps_attribute_lang.name, ps_attribute.position   From ps_attribute Inner Join ps_attribute_lang On ps_attribute_lang.id_attribute = ps_attribute.id_attribute Inner Join ps_attribute_group_lang On ps_attribute_group_lang.id_attribute_group = ps_attribute.id_attribute_group Where ps_attribute_group_lang.public_name = 'color' Order By ps_attribute_lang.name
) ;

#  Here using the above temp table a new temp table is created with the field "New_Positon" added with the incremented value of @a

SET @a:=0;
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_Attribute_Color_Sorted_With_Position AS ( Select tmp_Attribute_Color_Sorted.id_attribute, tmp_Attribute_Color_Sorted.name,  tmp_Attribute_Color_Sorted.position, @a:=@a+1 New_Positon From tmp_Attribute_Color_Sorted  Order By tmp_Attribute_Color_Sorted.name

) ;

# Now Join the "ps_attribute" table to the tmp table and set the position to the New_Position

UPDATE ps_attribute t1
        INNER JOIN tmp_Attribute_Color_Sorted_With_Position t2
             ON t1.id_attribute = t2.id_attribute
SET t1.position = t2.New_Positon
WHERE t1.id_attribute = t2.id_attribute ;

# This query is only to show me that all the above worked by displaying  the "ps_attribute"

Select ps_attribute.id_attribute, ps_attribute_lang.name, ps_attribute.position   From ps_attribute Inner Join ps_attribute_lang On ps_attribute_lang.id_attribute = ps_attribute.id_attribute Inner Join ps_attribute_group_lang On ps_attribute_group_lang.id_attribute_group = ps_attribute.id_attribute_group Where ps_attribute_group_lang.public_name = 'color' Order By ps_attribute_lang.name