I need to search Magento 2 DB for duplicate SKUs and remove duplicate products using a script, i found a very useful script here: Remove duplicated products from catalog.

but it's not compatible with Magento 2 as Mag 2 is not using Mage class or Mage.php

can someone please convert following code to a Magento 2 version:

include 'app/Mage.php';

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

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

// retrieve a list of all products that have more than 1 occurrence of the same name
$select = $adapter->select()->from(
        '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->having('count > 1');
$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);
        $row['entity_ids'] = $entityIds;

        return $row;

// 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) {

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.