How to select only duplicates from the database in YII2?

Question

Yii

Good health to all.
How to select only duplicate records in two fields using YII2?
distinct() outputs unique records, but how to display only duplicates for further work with them?
Thanks for any hint and kick in the direction of the right decision.

Answer 1

With the first pass you collect uniques, with the second pass you look to see if there is something left comparing with the base of uniques, if something is found, you check with uniques and add them somewhere, like washing gold in a river)
1. Count the total number of records
2. Pass DISTINCT, count the count -number of uniques
3. If item 2 is less than the total number, then there is a "sediment" - not uniques, duplicates
4. Select all records in the array and compare with the array of uniques from item 2.
5. If there is a match in the values ​​of the arrays, add these "pairs" into a third array.
Or vice versa - using the array_diff() function, we isolate the uniques and subtract them from the total array of entries = duplicates.

Answer 2

Good evening.
By means of yii, I think it will be tricky.
But sql should help you.
Something like this.

Yii::$app->db->createCommand('
    SELECT 
        `users`.`name`, COUNT(`users`.`name`),
        `users`.`patronymic`, COUNT(`users`.`patronymic`),
        `users`.`surname`, COUNT(`users`.`surname`),
        `users`.`id`, COUNT(`users`.`id`)
    FROM
        `users`, `users` AS `tmp`
    WHERE `users`.`id` > `tmp`.`id`    
    GROUP BY 
        `users`.`name`, 
        `users`.`patronymic`, `users`.`surname`, `users`.`id`
    HAVING 
           (COUNT(`users`.`name`) > 1) AND 
           (COUNT(`users`.`patronymic`) > 1) AND 
           (COUNT(`users`.`surname`) > 1)
')->execute();

Duplicate entries are selected. The request was made for your project, you need to change it to fit your needs.