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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
# 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 = '<strong><COLOR></strong>' 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 = '<strong><COLOR></strong>' Order By ps_attribute_lang.name; DROP TABLE IF EXISTS tmp_Attribute_Color_Sorted; DROP TABLE IF EXISTS tmp_Attribute_Color_Sorted_With_Position; |
0 commentaire