Find all duplicate product name

Question

Looking to find all duplicate product names,

Tried to find out with group by but it not giving duplicate values,its eliminate duplicates values,

$collection = $this->productFactory->create()->getCollection();
            $collection->getSelect()
                ->reset(\Zend_Db_Select::COLUMNS)
                ->columns(['entity_id'])
                ->columns(['sku']);
            $collection->addAttributeToSelect(['sku', 'name', 'url_key', 'meta_description'], 'left');
$collection->getSelect()->group('name');
$collection->getSelect()->having('count(name) > ?', 1);

Looking result is as follows,

Sku,name
1,abc
2,abc

Any help appriciated,

Thanks & Regards,

Answer 1

Try below code.

protected $productCollection;
public function __construct(
    \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $collectionFactory     
)
{
    $this->productCollection = $collectionFactory;
}

public function getDuplicate()
{

    $dup_namecoll = $this->productCollection->create();
    $dup_namecoll->addAttributeToSelect('name');
    $dup_namecoll->addAttributeToFilter('name',  ['neq' => 'NULL']);  
    $dup_namecoll->getSelect()->group('name')->having('count(name) > ?', 1);        
    $queryfromobject = $dup_namecoll->getSelect()->__toString();       
    $duplicateNames = "select subquery.name from ($queryfromobject) as subquery";       
    $duplicateNames = new \Zend_Db_Expr('( '. $duplicateNames.' )');              
    $maincollection = $this->productCollection->create()->addAttributeToSelect('name')->addAttributeToFilter('name',  ['neq' => 'NULL']);
    $maincollection->getSelect() ->reset(\Zend_Db_Select::COLUMNS)->columns(array('name'=>'IF(at_name.value_id > 0, at_name.value, at_name_default.value) ' , 'sku'))
                   ->where('(IF(at_name.value_id > 0, at_name.value, at_name_default.value) in '. $duplicateNames.')');  

    echo "<pre>";
    print_r($maincollection->getData());
    die();                
}