How to remove duplicate entries from CategoryProductRelation table

[Former Member]

Hi Team,

We are inserting supercategories in append mode through product creation or updation. It creates one entry in CategoryProductRelation table, so if we continuously updating the product then duplicate entries are creating in CategoryProductRelation table with unique sequence number this results appearing same category multiple time under Products -- Category section.

Can anyone help me how to remove all the duplicate entries in CategoryProductRelation table and keep only unique entries.

I tried through ImpEx but i didn't get any solution to remove duplicate entries.

Thanks,

Suresh G.

[geffchang]

Have you tried adding unique=true in the impex header?

Something like this:

 INSERT_UPDATE CategoryProductRelation;source[unique=true];target[unique=true];

To remove duplicate entries, I suppose you can export the duplicates, and remove them by PK:

 REMOVE CategoryProductRelation;pk[unique=true];

[former_member1320010]

you could also directly add the product and category to the productcategory relation table ,but ensure you modify the modifiedTime too so that its considered for the next sync

 #% impex.enableCodeExecution(true);
  
 $productCatalogName=myProductCatalog
 $productCatalogVersionName=Staged
 $productCatalogVersion=catalogversion(catalog(id[default=$productCatalogName]),version[default='$productCatalogVersionName'])[unique=true,default=$productCatalogName:$productCatalogVersionName]
  
 INSERT_UPDATE CategoryProductRelation;source(code,$productCatalogVersion)[unique=true];target(code,$productCatalogVersion)[unique=true];;
 "#% afterEach:
 impex.lastImportedItem.target.modificationTime= new Date();
 ";
 ;category;product1;

[Former Member]

So to avoid duplicate entries, before sending categories you need to check whether that category is already present in super-categories of that product.

If yes then don't replicate that catecory, else replicate the category

This you can do by two ways 1. Write interceptor 2. Create Impex translator to check duplicate categories and pass the impex translator path in your product-target extension.

So each time when you send categories it will check and remove duplicate categories, and then import product impex