1

I found this SQL query (Magento Sort Categories Alphabetically using SQL Query) and I've been successfully using it to re-sort all of our 30000+ categories but the problem I have is that it re-sorts all categories. I only have one category that needs re-sorting every now and then so my question is, how do I modify below query so that it only sorts all subcategories for a specific category (ID: 38744)?

SET @i=0;
SET @j=0;
DROP TABLE IF EXISTS AAA_NEW_POSITION;
CREATE TABLE AAA_NEW_POSITION SELECT e.entity_id AS 'entity_id',
    vn.value AS 'name',
    e.position AS 'old_position',
    @i:=@i + 1 AS 'new_position' FROM
    catalog_category_entity e
        LEFT JOIN
    catalog_category_entity_varchar vn ON e.entity_id = vn.entity_id
        AND vn.attribute_id = 41
ORDER BY vn.value;

ALTER TABLE AAA_NEW_POSITION ORDER BY name;
UPDATE AAA_NEW_POSITION 
SET 
    new_position = @j:=@j + 1
ORDER BY name;

UPDATE catalog_category_entity e
        LEFT JOIN
    AAA_NEW_POSITION np ON e.entity_id = np.entity_id 
SET 
    e.position = np.new_position;

DROP TABLE IF EXISTS AAA_NEW_POSITION;
0

When you are creating the table with the join, you can add a condition to the catalog_category_entity table that specifies what the path looks like. The first one you create would be 1/2/%, after that, it depends on the order which you created the categories, so you will need to look at the path value for the root category that you are looking for:

select path from catalog_category_entity where entity_id = [the id of your category];

In the case that you want to filter by the first root category, it would look like this (note the where condition in the second to last line):

CREATE TABLE AAA_NEW_POSITION SELECT e.entity_id AS 'entity_id',
    vn.value AS 'name',
    e.position AS 'old_position',
    @i:=@i + 1 AS 'new_position' FROM
    catalog_category_entity e
        LEFT JOIN
    catalog_category_entity_varchar vn ON e.entity_id = vn.entity_id
        AND vn.attribute_id = 41
WHERE e.path like '1/2/%'
ORDER BY vn.value;

To resolve the null value issue, try the where condition below. Note that the position column is on the aaa_new_position table

UPDATE catalog_category_entity e
        LEFT JOIN
    AAA_NEW_POSITION np ON e.entity_id = np.entity_id 
SET 
    e.position = np.new_position
WHERE np.new_position IS NOT NULL;
  • Thank you for your answer @mtr.web, I appreciate it a lot. What I did (being an absolute amateur when it comes to MySQL) is run the first script you gave me which gave me ''1/38743/38744'' and then ran the query with the extra line and that worked! – Dennis Gaasbeek Oct 27 '17 at 10:34
  • I spoke a bit too soon as I just ran this again on our test database and it gives the following error; 23022 row(s) affected, 64 warning(s): 1048 Column 'position' cannot be null (and this is repeated 64 times). Do you have any idea what this means? – Dennis Gaasbeek Oct 27 '17 at 14:16
  • I would try adding a WHERE position IS NOT NULL to the last update statement. You may also be able to use it when you're populating the AAA_NEW_POSITION table, but it is perhaps better for debugging this way. Let me know if it works! – mtr.web Oct 28 '17 at 13:59
  • Thanks again @mtr.web. I altered the last update statement to UPDATE catalog_category_entity e LEFT JOIN AAA_NEW_POSITION np ON e.entity_id = np.entity_id SET e.position = np.new_position WHERE position IS NOT NULL And this gave me the exact same error as before so I'm probably doing something wrong here. – Dennis Gaasbeek Nov 1 '17 at 10:00
  • @DennisGaasbeek, I edited the answer to show the full example. My previous comment was incorrect, you will want to make sure the np.new_position value is not null. – mtr.web Nov 1 '17 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.