How to get only duplicates entries in collection?

Question

I want to get only those rows having same sku two or more times in collection using magento2? how can i perform this in magento2 collection way

Answer 1

below is the code for getting duplicate name products with a little tweak you can use this piece of code for your solution below is the code snippet fill free to drop any query if you have

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();                
}