how to mark duplicates value in table view?
Posted: 2022-02-01 05:25
how to mark duplicates or more than 2 times repeating value in table view for certain field, and filter it as same values ?
For avoiding duplicates you should set unique-flag.zkarwinkar wrote: ↑2022-02-01 05:25how to mark duplicates or more than 2 times repeating value in table view for certain field, and filter it as same values ?
Anyway, if there is duplicate data already in your database table, personally, I would create an additional field
is_duplicate on that table, then do the checks on serverside (SQL+PHP) and update that new field if you detect a duplicate. Having this additional field and having records tagged as "duplicates", in the second step there are many ways for filtering, sorting, searching and highlighting.Given a table named
customers having a column named email, this is the SQL command for finding all duplicate emails:Code: Select all
SELECT `email`
FROM `customers`
WHERE `email` IS NOT NULL
group by `email`
having count(*)>1
is_duplicate = 1 (and all the others = 0).(2) As an alternative, turn
is_duplicate into a calculated field which automatically updates it's status.This is the SQL command* for the calculated field in the given example
* this is an example for good readability. There are more efficient ways in SQL. Subselects are not very performant but easy to use and understand here.
Code: Select all
SELECT
(SELECT count(*)>0 FROM customers WHERE A.email = email AND A.id != id)
FROM `customers` as A
WHERE A.id='%ID%'
- chrome_BNxessqSPD.png (5.29 KiB) Viewed 1841 times
Personally I prefer (1) over (2) because I'm very flexible when doing this for example in TABLENAME_init hook. There I can change my SQL conditions and update records according to my business requirements without changing and re-generating the project. But this is only my personal strategy. Especially I can re-evaluate all records and not only the displayed records. Calculated fields only work on displayed records unless you trigger the re-calculation manually.