Laravel-excel: [QUESTION] Preventing duplicate records on import using validation

[srk23]

Prerequisites

• [ X] Have read and understood: https://github.com/Maatwebsite/Laravel-Excel/blob/3.1/.github/SUPPORT.md
• X[ ] Checked if your Laravel Excel version is still supported: https://docs.laravel-excel.com/3.1/getting-started/support.html#supported-versions
• [X ] Checked that your question isn't already asked before.
• [X ] Filled in the entire issue template

Versions

• PHP version: 7.2.22
• Laravel version: 6.0
• Package version: 3.1.17

Description

I am working on a project where I get regular excel files to import into a database. Occasionally there is overlap between rows, so I want to check if a record exists before I import.

I am checking to see if a unique field "DONOR_ID" value already exists.

I call my import in a controller:

    public function import() 
    {
        Excel::import(new Import,request()->file('data_file'));

        return back();
    } 

In my Import.php file, I can achieve what I want by checking if the relevant DONOR_ID exists:

public function model(array $row)
    {
            if(!Donor::where('DONOR_ID', '=', $row[0])->exists()) {

            return new Donor([
            'DONOR_ID' => $row[0],
            'AGE_YEARS' => $row[1],
            ...
            ]);    
        }
    }

I would like to return a response for each row, so that I can feed back to the user which rows have and have not been imported.

Looking at the docs, I should use 'WithValidation' and set rules, to allow error messages to be collected and returned. So I add the rules as so:

<?php

namespace App\Imports;

use App\Donor;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithStartRow;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\Importable;
use Illuminate\Validation\Rule;

class Import implements ToModel, WithStartRow, WithValidation
{
    use Importable;

    public function startRow(): int
    {
        return 2;
    }

    public function model(array $row)
    {
            return new Donor([
              'DONOR_ID' => $row[0],
              'AGE_YEARS' => $row[1],
              ...
        ]);
    }

    public function rules(): array
    {
        return [
            '0' => Rule::unique(['donors', 'DONOR_ID'])
        ];
    }

    public function customValidationMessages()
{
    return [
        '0.unique' => 'Duplicate',
    ];
}
}

However, I get an error:

Method Illuminate\Validation\Rules\Unique::__toString() must not throw an exception, caught ErrorException: Array to string conversion

I am not sure what I am doing wrong?

Additional Information

Any additional information, configuration or data that might be necessary to reproduce the issue.

[patrickbrouwers]

I think you gotta turn use an array there (so add the Rule::unique() into an array

return [
            '0' => [Rule::unique(['donors', 'DONOR_ID'])]
        ];

[srk23]

Afraid not - still get the same error.

Is there any other way of getting feedback as to success/failure of each database query? I am not batching, so presumably each query is run as a separate insert?

[patrickbrouwers]

Looking at the exception it’s an issue with how you declared the unique rule, best to double check those params

[srk23]

Very strange. Looking at the docs, you need to feed the table and column name, both of which look fine according to my DB.

[patrickbrouwers]

I think it should be like this: Rule::unique('donors', 'DONOR_ID') not that I'm not passing the params as an array to the unique() method, but as separate params

[srk23]

That seems to have got rid of the validation error! Thanks.

[oroalej]

Hi @patrickbrouwers ,

How to do this with softDeletes()?

tried doing this but doesn't work.

Rule::unique('some_table', 'some_column')
  ->WhereNull('deleted_at')

or

unique:some_table,some_column,null,id,deleted_at,null

[alfarelrizky]

Your Help My Life , LOL :D