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

Created: 8 Oct '16

Last Reply: 9 Oct '16

Replies: 1

Views: 965

Votes: 0

Welcome to Aero Coding!

Aero Coding is a CakePHP-focused Q&A community for professional and enthusiast cake bakers. It's built and run by you as part of the community.


Join Now Tour

Download Cakephp

Start baking your own CakePHP application!


Cakephp All Versions