How to Remove Duplicate Rows in Laravel

Removing duplicate entries from database Laravel

One approach to do this Like using DB::delete() running raw query.

$affectedRow = DB::delete('DELETE t1 FROM table t1, table t2 WHERE t1.id > t2.id AND t1.name = t2.name AND t1.amount = t2.amount');

change table with your actual table name.

It return the no of rows deleted.

Another Solution: Not tested!

Here you can add your other custom queries as well!

// find the duplicate ids first.
$duplicateIds = DB::table("table")
                    ->selectRaw("min(id) as id")
                    ->groupBy("amount", "name")
                    ->havingRaw('count(id) > ?', [1])
                    ->pluck("id");



// Now delete and exclude those min ids.
DB::table("table")
    ->whereNotIn("id", $duplicateIds)
    ->havingRaw('count(id) > ?', [1])
    ->delete();

Laravel - Deleting Duplicate Rows in SQL Database

After looking at some other answers I found the one that best works for me:

DELETE FROM satellites WHERE id NOT IN (SELECT * FROM (SELECT MAX(n.id) FROM satellites n GROUP BY n.norad_cat_id) x)

This deletes all rows with the same norad_cat_id but leaves one with the highest id.

Deleting duplicate database records by date in Laravel

You could use EXISTS():

---

select * from meuk;

DELETE FROM meuk d
WHERE EXISTS (
        SELECT * FROM meuk x
        WHERE x.item = d.item                           -- same item
        AND x.updated_at::date = d.updated_at::date     -- same date
        AND x.updated_at > d.updated_at                 -- but: more recent
        );

select * from meuk;

---

Results:

---

DROP TABLE
CREATE TABLE
COPY 7
VACUUM
 id  | item  | value | created_at |     updated_at      
-----+-------+-------+------------+---------------------
 510 | item1 |    12 | 2021-07-02 | 2021-07-02 16:45:17
 500 | item1 |    13 | 2021-07-02 | 2021-07-02 16:45:05
 490 | item1 |    13 | 2021-07-02 | 2021-07-02 16:45:01
 480 | item2 |    12 | 2021-07-02 | 2021-07-02 16:44:59
 470 | item2 |    14 | 2021-07-02 | 2021-07-02 16:44:55
 460 | item2 |    12 | 2021-07-02 | 2021-07-02 16:44:54
 450 | item2 |    11 | 2021-07-02 | 2021-07-02 16:44:53
(7 rows)

DELETE 5
 id  | item  | value | created_at |     updated_at      
-----+-------+-------+------------+---------------------
 510 | item1 |    12 | 2021-07-02 | 2021-07-02 16:45:17
 480 | item2 |    12 | 2021-07-02 | 2021-07-02 16:44:59
(2 rows)

---

A different approach, using window functions. The idea is to number all records on the same {item,day} downward, and preserve only the first:

---

DELETE FROM meuk d
USING (
        SELECT item,updated_at
        , row_number() OVER (PARTITION BY item,updated_at::date 
                             ORDER BY item,updated_at DESC
                             ) rn
        FROM meuk x
        ) xx
WHERE xx.item = d.item
AND xx.updated_at = d.updated_at
AND xx.rn > 1
        ;

---

Do note that this procedure always involves a self-join: the fate of a record depends on the existence of other records in the same table.

Delete duplicates records from large Laravel collection

You can allow the database to do the heaving lifting here. You can query the database using GROUP BY, then remove everything that doesn't match your query.

$ids = Model::groupBy(['relation_id', 'foo', 'bar', 'timestamp'])
            ->get(['id'])
            ->all();

This translates to the following SQL:

SELECT id FROM models GROUP BY relation_id, foo, bar, timestamp;

So now $ids is an array of IDs where the other columns are unique ([1, 4]). So you can execute the following to remove all other rows from the DB:

Model::whereNotIn('id', $ids)->delete();

However, since $ids is probably huge, you are likely to hit some upper limit constraints. In that case, you can try using array_chunk() to add multiple whereNotIn clauses to the query:

$query = Model::query();

foreach(array_chunk($ids, 500) as $chunk) {
  $query->whereNotIn('id', $chunk);
}

$query->delete();

I created an SQL Fiddle where you can test this out.

How to remove duplicates in collection?

$unique = $collection->unique();

How do I remove duplicate rows with same column values in Laravel?

To be able to delete an item, Eloquent needs to know it's id. If you make sure your models' id is queried, you can call delete() without issues.

In your query, however, that won't work because you have a GROUP_BY statement, so SQL doesn't allow you to select the id column (see here).

The easiest solution here is to utilize Eloquent's Collection class to map over the models, something like:

$uniqueAddresses = [];

Venue::all()
    ->filter(function(Venue $venue) use (&$uniqueAddresses) {
        $address = sprintf("%s.%s.%s",
            $venue->street,
            $venue->house_number,
            $venue->house_number_addition);

        if (in_array($address, $uniqueAddresses)) {
            // address is a duplicate
            return $venue;
        }

        $uniqueAddresses[] = $address;
    })->map(function(Venue $venue) {
        $venue->delete();
    });

Or, to make your delete query a little more efficient (depending on how big your dataset is):

$uniqueAddresses = [];
$duplicates = [];

Venue::all()
    ->map(function(Venue $venue) use (&$uniqueAddresses, &$duplicates) {
        $address = sprintf("%s.%s.%s",
            $venue->street,
            $venue->house_number,
            $venue->house_number_addition);

        if (in_array($address, $uniqueAddresses)) {
            // address is a duplicate
            $duplicates[] = $venue->id;
        } else {
            $uniqueAddresses[] = $address;
        }
    });

DB::table('venues')->whereIn('id', $duplicates)->delete();

Note: the last one will permanently delete your models; it doesn't work with Eloquent's SoftDeletes functionality.

You could, of course, also write a raw query to do all this.

---

Related Topics