0

I mass import products into my site on a nightly basis (affiliate products). Occasionally the products are duplicated so I want to write a quick SQL Statement to remove the duplicated products.

What I will then do is put the SQL into a PHP script and call it each night after the import so it will remove the duplicated products before I reindex.

I've done some research and found come up with the following script to get all of the product names from the database:

SELECT `value` AS product_nameFROM catalog_product_entity_varchar WHERE entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product') AND attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'))

I have also found this example script to identify duplicates in SQL:

`SELECT name, email, COUNT(*) FROM users GROUP BY name, email HAVING COUNT(*) >` 1

I wondered if someone could help me put this together?

Thanks in advance! Chris

4
  • I've managed to get the following code to identify the problem value_ids: SELECT value_id FROM catalog_product_entity_varchar WHERE entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product') AND attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'))GROUP BY value HAVING COUNT(*) > 1 but can't figure out how to delete them? May 15, 2016 at 17:22
  • Please do not use that statement that you have started forming above. What is the criteria by which you distinguish a "duplicate"? Is it the sku, the name, or something else? I will write a safer way to do this. May 15, 2016 at 19:17
  • Hi Shawn, purely the product name, nothing else, I can get the list but obviously can't use the "DELETE FROM" with the above May 15, 2016 at 20:58
  • Hi Walshie, thanks for the info. Im going to see what I can come up with. By the way, its not recommended to use pure sql to delete products because there are usually other events that take place before and after delete and those events are only triggered by using native Magento functionality. Ill be back with you shortly. May 16, 2016 at 2:29

1 Answer 1

0

I have tested this and it works but since it involves the deletion of products, I strongly recommend that you backup your database first.

include 'app/Mage.php';
Mage::app();

Mage::register('isSecureArea', 1);

$limit    = 100;
$model    = Mage::getModel('catalog/product');
$resource = $model->getResource();
$adapter  = $resource->getReadConnection();
$name     = $resource->getAttribute('name');

// retrieve a list of all products that have more than 1 occurrence of the same name
$select = $adapter->select()->from($name->getBackendTable(),array($resource->getIdFieldName(),'value','count' => 'COUNT(*)','entity_ids' => new Zend_Db_Expr("GROUP_CONCAT({$resource->getIdFieldName()})")));
$select->where('attribute_id = ?',$name->getAttributeId());
$select->where('entity_type_id = ?',$name->getEntityTypeId());
$select->group('value');
$select->having('count > 1');
$select->limit($limit);
$result = $adapter->fetchAll($select);

// here, we remove the lowest product id from each set of duplicate-named products. 
// the theory is that the product which was created first, hence the lowest 
// product id, is the one you want to keep
$result = array_map(function($row){
    $entityIds = explode(',',$row['entity_ids']);
    $min = min($entityIds);
    $key = array_search($min, $entityIds);
    unset($entityIds[$key]);
    $row['entity_ids'] = $entityIds;
    return $row;
},$result);

// now that we have narrowed down to the ids of the products we want to delete, 
// lets get all product ids to delete into a single array
$entityIds = array();
foreach($result as $row){
    $entityIds = array_merge($entityIds,$row['entity_ids']);
}

// lets delete each one individually
$collection = $model->getCollection();
$collection->addFieldToFilter($resource->getIdFieldName(),array('in' => $entityIds));
foreach($collection as $product){
    $product->delete();
}
8
  • Hi Shawn, I've followed the logic and it seems to be able to do exactly what I need! The only problem I have at the minute is that it keeps timing out as I've identified there are 38,000 duplicates! This is because they have built up over a number of weeks / months. Usually there will be less then 200. Would it be possible to put a limit in the above code so I can test it? say just take 100 products at a time? Really appreciate your help with this! May 16, 2016 at 11:02
  • Its my pleasure. I have just updated it with a limit. Ideally, so that you don't have to be there to keep running it, you may want to increase the timeout in php.ini and also your main htaccess. Then you would likely also want to run through the 38000 in batches using a cron job so you don't have to keep running it manually. After the 38000 are taken care of, you can set up a daily cron that runs this automatically. May 16, 2016 at 13:00
  • Hi again, I've just had chance to test this, the initial gathering of the data works perfectly and produces this SQL: SELECT catalog_product_entity_varchar.entity_id, catalog_product_entity_varchar.value, COUNT(*) AS count, GROUP_CONCAT(entity_id) AS entity_ids FROM catalog_product_entity_varchar WHERE (attribute_id = 71) AND (entity_type_id = 4) GROUP BY value HAVING (count > 1) LIMIT 100 and this does correctly identify the products. But after that it doesn't appear to delete them. From my limited knowledge of PHP the bottom loop doesn't seem to have any entries? May 16, 2016 at 16:33
  • If there are products in the collection, the $product->delete(); should do the trick. I did test it out, I had a site set up with dupe named products too so I tested it there. Here is how you see if there are any products remaining to delete. Just before the comment that says // lets delete each one individually, type this: echo count($entityIds);die; and whatever number is displayed there is the number of products to be deleted. See what that says and let me know. May 16, 2016 at 17:26
  • That gives me 119 May 16, 2016 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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