Get all duplicate records in table

Slow variant:

SELECT *
FROM table t1
WHERE t1.domain_id = 1569
AND t1.is_active = 1
AND (t1.field IN (SELECT t2.field
                 FROM table t2
                 WHERE t2.is_active = 1
                 GROUP BY t2.field
                 HAVING COUNT(t2.field) > 1))

Faster variant:

SELECT l0_.*
FROM table l0_
inner join table n2 on n2.field = l0_.field
where l0_.id <> n2.id
AND l0_.is_active = 1
Group By l0_.id

QueryBuilder:

$queryBuilder
    ->innerJoin(Table::class,'table', Query\Expr\Join::WITH, 'entity.field = table.field')
    ->andWhere('entity.id <> table.id')
    ->andWhere('table.isActive = 1')
    ->groupBy('entity.id')
;

Leave a Reply

Your email address will not be published. Required fields are marked *