mysqlcodeigniter-datamapper

Find duplicate records with advanced query in CodeIgniter Datamapper

I'm trying to get all duplicate rows from a DB. Lets say I have table entries with primary key id ant VARCHAR column called name. I already have a working SQL query:

SELECT id, entries.name FROM entries 
INNER JOIN 
    (SELECT name FROM entries 
     GROUP BY name 
     HAVING count(id) > 1) 
duplicate ON entries.name = duplicate.name

I'd like to construct similar query with CodeIgniter Datamapper lib for my personal interest as well as consistency with rest of the code.

I've build the subquery part and it is working as expected:

$e = new Entry();
$e->group_by('name')->having('COUNT(id) > 1')->get();

But I can't figure it out how can I integrate the subquery into parent query (the INNER JOIN ... ON ... part).

Solution

  • Firstly you need not to take inner join.

    SELECT id, entries.name FROM entries GROUP BY name HAVING count(id) > 1

    This will give you duplicate records. So you will not need to make data mapper for joined query.

    You will get result by this only.

    $e = new Entry();

    $e->group_by('name')->having('COUNT(id) > 1')->get();

    Update:

    SELECT id, entries.name FROM entries where id NOT IN (SELECT id FROM entries GROUP BY name HAVING count(id) = 1)