How to order/sort attribute combination names alphabetically in Prestashop 1.6

Introduction

This post was a result of a modification of the code of « wayne_f » which can be found here : https://www.prestashop.com/forums/topic/310945-how-to-ordersort-attribute-combination-names-alphabetically/

If you have a lot of attributes (color, style, pattern, size…) in your Prestashop website, you would like to sort them in the layered filter.

We will describe a process to do it automatically with SQL import. Please note you can sort attributes only in one language!

Process

• Open your database (for example by phpMyAdmin)
• Create backup of your database for safety (export data and structure)
• Open SQL tab and perform queries from this file (change ‘ps_’ to your prefix)

Change the string <COLOR> by the « ps_attribute_group_lang.public_name » in the script

# 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;
DROP TABLE IF EXISTS tmp_Attribute_Color_Sorted;
DROP TABLE IF EXISTS tmp_Attribute_Color_Sorted_With_Position;