Find orphan variants

Fredrik Tjörnfelt

Vote:

Hello,

Im in situation where I need to find all the varaiants in the catalog that has no relation to a product and then delete them.

I was thinking to make a search in the catalog for all variants and then iterate trough them.

Then using the linkrepository to see if there is a relation to a product and if not delete it.

This was a good idea in my dev enviorment where i have 300-400 products but in the prod env. there is about 25k, this can be costly.

Is there a more efficient way to do this?

//Fredrik

#116175

Jan 26, 2015 10:34

Quan Mai

Vote:

Hi,

You can use a simple SQL to get the CatalogEntryId to delete, for example:

select catalogentryId FROM [dbo].[CatalogEntry]
where ClassTypeId = 'Variation' and
catalogentryid not in (Select ChildEntryId from [dbo].CatalogEntryRelation where RelationTypeId = 'ProductVariation')

Then delete the content by the catalog entry ids you got.

It's always recommended to back up your database first.

Regards.

#116177

Jan 26, 2015 10:49

Fredrik Tjörnfelt

Vote:

Thanks for your reply, I wanted to use the api but this is more simple, I will have a try and mark your answer if sucessful.

#116178

Jan 26, 2015 11:30

Fredrik Tjörnfelt

Vote:

Here is the final solution, that is a shedualTask.

var listOfOrphans = VariantCleanupQueries.GetOrphans().ToList();
if (listOfOrphans.Any())
{
CatalogContext.Current.DeleteCatalogEntries(listOfOrphans);
return string.Format("Deleted {0} orphans.", listOfOrphans.Count);
}

GetOrphans uses your SQL.

Thanx

#116184

Jan 26, 2015 14:21

Try our conversational search powered by Generative AI!

Find orphan variants