Laravel : How to exclude duplicate records when inserting a group of data into mysql?

Question

I am using Laravel 5.5,I want to exclude duplicate records when inserting a group of data into mysql.

For example,there is a table students,it has these fields:

id
name
gender

Now I will insert a group of data into students,if not mind duplicate records，I can do it like this:

public function insert()
{
    $newStudents=[
        ['name'=>'Jim','gender'=>'boy'],
        ['name'=>'Lucy','gender'=>'girl'],
        ['name'=>'Jack','gender'=>'boy'],
        ['name'=>'Alice','gender'=>'girl']
    ];

    DB::table('students')->insert($newStudents);
}

Now I don't want to insert duplicate records.(The duplicate is : both name and gender have the same values,not one field has the same value).

what should I do?

https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql — alireza shokri, Sep 06 '17 at 19:06

score 2 · Answer 1 · answered Sep 06 '17 at 19:12

You could use the collection helper unique. See code below:

 $newStudents=collect([
    ['name'=>'Jim','gender'=>'boy'],
    ['name'=>'Lucy','gender'=>'girl'],
    ['name'=>'Jack','gender'=>'boy'],
    ['name'=>'Alice','gender'=>'girl'],
     ['name'=>'Alice','gender'=>'girl']
    ])->unique(function ($student) {
        return $student['name'].$student['gender'];
    })->toArray();

    DB::table('students')->insert($newStudents);

The above code will only insert unique records, even though there is a duplicate record there.

For more information, see here:https://laravel.com/docs/5.4/collections#method-unique

if table `students` already has the same record,how to do it ? for example, assuming table `students` has a record `['name'=>'Jack','gender'=>'boy']` already . now in `$newStudents` ,there is also a `['name'=>'Jack','gender'=>'boy']` ,what should I do? — zwl1619, Sep 06 '17 at 19:38

score 0 · Answer 2 · answered Sep 06 '17 at 20:09

You could create an unique index for the name and gender in the database. However, then when you try to save them you will get a MySQL error I'm guessing. So you could use the unique validation with where.

Validator::make($student, [
    'gender' => [
        'required',
        Rule::in(['boy', 'girl']),
    ],
    'name' => Rule::unique('students')->where(function ($query) use ($student) {
        $query->where('gender', $student['gender']);
    })
]);

Then you can run your collection through and filter out the ones that aren't valid like in @pseudoanime's answer.

Laravel : How to exclude duplicate records when inserting a group of data into mysql?

2 Answers2