Find orphan variants

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
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.

/Q

#116177
Jan 26, 2015 10:49
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
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
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.