laravel excel with upserts not updating data

Question

I have a csv file with data inside. I want import it to my database. If there's repeated data in csv file, I don't want it to create new repeated records, what I want is to update it.

But the problem is, it's creating new repeated records.

Controller (abbreviated):

Excel::import(new FruitImport, $request->file('fruits_file'), \Maatwebsite\Excel\Excel::XLSX);

Import File (abbreviated):

class FruitImport implements ToModel, WithUpserts, ... {
    public function model(array $row) {
        return new Fruit([
            'color' => $row[0],
            'taste' => $row[1],
            'smell' => $row[2],
            'name' => $row[3],
            'price' => $row[4],
            'supplier_id' => $row[5],
            ...
         ]);
    }

    public function uniqueBy() {
        return ['color', 'taste', 'smell', 'name'];
    }

    ...
}

So basically, if I import a csv file containing rows with same color, taste, smell, name to my database, I want it to be updated. Same thing if any record in my database has same color, taste, smell and name data.

My table:

Field    | Type        | Key  | ...
id       | big int un..| Pri  |
color    | varchar(12) |      |
taste    | varchar(12) |      |
smell    | varchar(12) |      |
name     | char(20)    |      |
...

I'm using Laravel 8, Php 8.0.2 and Mysql 8. Laravel excel is version 3.1

My references:

https://laravel.com/docs/8.x/eloquent#upserts

https://docs.laravel-excel.com/3.1/imports/model.html#upserting-models

Any ideas?

Answer 1

I'm not sure Upserts in Laravel Excel are what you want. Specifically, there is a condition listed in the documentation that is (in my opinion) overly restrictive.

All databases except SQL Server require the uniqueBy columns to have a "primary" or "unique" index.

To import spreadsheet data in a custom way, you can always use the ToCollection concern. This will give you full control over how the entries are saved.
https://docs.laravel-excel.com/3.1/imports/collection.html

Use the updateOrCreate() method to look for existing entries with the options you have specified by passing them in the first array, with the remaining data in the second array. https://laravel.com/docs/8.x/eloquent#upserts

class FruitImport implements ToCollection ... 
{    
    public function collection(Collection $rows)
    {
        foreach ($rows as $row) 
        {
            Fruit::updateOrCreate(
                [
                    'color' => $row[0],
                    'taste' => $row[1],
                    'smell' => $row[2],
                    'name' => $row[3]
                ],
                [
                    'price' => $row[4],
                    'supplier_id' => $row[5],
                    ...
                ]
            );
        }
    }
}