Category: Models & ORM

Q: In CakePHP 3, identify and remove duplicate records of customers

This is my customers table (example)

idnamephonesexstage
1John123-456789m0
2Mary459-234321f3
3Liza123-123414f1
4John123-456-789m3
5Kate999-000999f0
...



I want to identify and remove duplicate records of customers, for example John, in CakePHP. CRM uses two columns name and phone to identify our customers. I want to keep id=1 and delete the duplicate one id=4. 

How to build a query based on upon?

*Note: My SQL statement

SELECT id, count(*) AS records FROM Customers GROUP BY id HAVING count(*) > 1
sql
1 Answers

Version: 3.3

1. find duplicated rows based on name and phone:

$query = $customers->find()
        ->select(['id', 'records' => 'count(*)'])
        ->group(['name', 'phone'])
        ->having(['count(*) > ' => 1]);

2. delete all (first records' ids)

$customers->deleteAll(['id IN' => $ids]);
Ksmacky
Ksmacky

30