Inconsistent Category Path Export in RO CSVI for VirtueMart #83510

Hi Tharuna,

Thank you for your response.

I have checked the #__virtuemart_category_categories table, and for id = 60, the category_parent_id is still set to 0.

However, in the #__virtuemart_categories table, the values are correct:

virtuemart_category_id = 60
category_parent_id = 53

I tried resaving this category via the VirtueMart backend, but it didn’t update the category_parent_id in the virtuemart_category_categories table — it remained 0.

Could you please confirm:
Is it safe and recommended to manually update the category_parent_id in virtuemart_category_categories using the values from the virtuemart_categories table?

To synchronise the category_parent_id between the two tables, I would run an SQL query like this:

UPDATE invict4_virtuemart_category_categories AS ccc
JOIN invict4_virtuemart_categories AS cat
  ON ccc.id = cat.virtuemart_category_id
SET ccc.category_parent_id = cat.category_parent_id
WHERE ccc.category_parent_id != cat.category_parent_id;

Thanks again for your help!

Hi Tharuna,

Interestingly, it doesn't allow me to change it.

UPDATE `invict4_virtuemart_category_categories` SET `category_parent_id` = '53' WHERE `invict4_virtuemart_category_categories`.`id` = 60

#1062 - Duplicate entry '53-60' for key 'invict4_virtuemart_category_categories.category_parent_id'

Hi Tharuna,

It is basically the same, and I got the same error:

Error
SQL query: Copy

UPDATE `invict4_virtuemart_category_categories` SET `category_parent_id` = '53' WHERE `invict4_virtuemart_category_categories`.`category_child_id` = 60;
MySQL said: Documentation

#1062 - Duplicate entry '53-60' for key 'invict4_virtuemart_category_categories.category_parent_id'

If you check the screenshot I attached, you can see that in the invict4_virtuemart_category_categories table, the ID and the category_child_id are always the same.

Hi Tharuna,

Thank you very much for pointing me in the right direction and ultimately to the solution.

Here are the steps taken so that all parent and child categories are now correctly stored in the DB, therefore exporting correctly:

Select the duplicated category child IDs

SELECT *
FROM invict4_virtuemart_category_categories
WHERE category_child_id IN (
    SELECT category_child_id
    FROM invict4_virtuemart_category_categories
    GROUP BY category_child_id
    HAVING COUNT(*) > 1
)
ORDER BY category_child_id;

From these duplicates select only where category_parent_id = 0

SELECT *
FROM invict4_virtuemart_category_categories
WHERE category_child_id IN (
    SELECT category_child_id
    FROM invict4_virtuemart_category_categories
    GROUP BY category_child_id
    HAVING COUNT(*) > 1
)
AND category_parent_id = 0
ORDER BY category_child_id, id;

DELETE cat_cat
FROM invict4_virtuemart_category_categories AS cat_cat
JOIN (
    SELECT category_child_id
    FROM invict4_virtuemart_category_categories
    GROUP BY category_child_id
    HAVING COUNT(*) > 1
) AS dupes
ON cat_cat.category_child_id = dupes.category_child_id
WHERE cat_cat.category_parent_id = 0;

**To update category_parent_id in the invict4_virtuemart_category_categories table by matching category_child_id with virtuemart_category_id from the invict4_virtuemart_categories table
**

SELECT
  cat_cat.id,
  cat_cat.category_child_id,
  cat_cat.category_parent_id AS current_parent_id,
  cat.category_parent_id AS new_parent_id
FROM invict4_virtuemart_category_categories AS cat_cat
JOIN invict4_virtuemart_categories AS cat
  ON cat_cat.category_child_id = cat.virtuemart_category_id
WHERE cat_cat.category_parent_id = 0;

UPDATE invict4_virtuemart_category_categories AS cat_cat
JOIN invict4_virtuemart_categories AS cat
  ON cat_cat.category_child_id = cat.virtuemart_category_id
SET cat_cat.category_parent_id = cat.category_parent_id
WHERE cat_cat.category_parent_id = 0;

Thank you again.
Awesome Support.