Validation: merging 2 field of a row and check if the result is unique in db table

[DannyV90]

Hi to everybody,
I'm facing with this problem in import.

Here it is my import file:

<?php

namespace App\Imports;

use App\Models\IncomePartition;
use App\Models\WebProviderCost;
use Carbon\Carbon;
use Illuminate\Support\Facades\Validator;
use Throwable;
use App\Models\Customer;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\SkipsErrors;
use Maatwebsite\Excel\Concerns\SkipsFailures;
use Maatwebsite\Excel\Concerns\SkipsOnError;
use Maatwebsite\Excel\Concerns\SkipsOnFailure;

class CustomersImport implements ToCollection, WithValidation, WithHeadingRow, SkipsOnError,
    SkipsOnFailure, WithBatchInserts, WithChunkReading
{
    use Importable, SkipsErrors, SkipsFailures;

    protected $errors = [];
    protected $inserted = 0;

    protected $insegna = '';
    protected $citta = '';
    protected $provincia = '';
    protected $indirizzo = '';


    public function collection(Collection $rows)
    {
        $rows = $rows->toArray();
            $i = 2;

            foreach ($rows as $row) {

                $this->insegna = trim($row['insegna']);
                $this->citta = trim($row['citta']);
                $this->provincia = trim($row['provincia']);

                if (empty($row['numcivico']) ) {
                    $this->indirizzo = trim($row['indirizzo']);
                }else{
                    $this->indirizzo = trim($row['indirizzo']). ', ' . trim($row['numcivico']);
                }

                $validator = Validator::make($row, $this->rules(), $this->validationMessages());
                

                if ($validator->fails()) {

                    foreach ($validator->errors()->messages() as $messages) {
                        foreach ($messages as $error) {
                            $errorData = $validator->getData();
                            $riga = $i;
                            $errorData[] = $error;
                            $errorData[] = $riga;
                            $this->errors[] = $errorData;
                        }
                    }

                }else{

                $address = $this->indirizzo;

                $customer = Customer::create([
                    'name' => trim($row['insegna']),
                    'town' => trim($row['citta']),
                    'province' => trim($row['provincia']),
                    'customer_code' => trim($row['codlocale']),
                    'address' => $address
                ]);

                $lastId = $customer->id;

                WebProviderCost::create([
                    'valid_from' => Carbon::today()->toDateString(),
                    'customer_provider_cost_type_id' => 1,
                    'customer_provider_cost_value' => 1.5,
                    'operator_provider_cost_type_id' => 1,
                    'operator_provider_cost_value' => 0.6,
                    'web_provider_id' => null,
                    'customer_id' => $lastId,
                    'refer_to_slot_with_specific_pay_out' => null
                ]);

                switch ($row['tipo']):
                    case 'Utile':
                        $tipo = 1;
                        break;
                    case 'Introdotto Locale':
                        $tipo = 3;
                        break;
                    default:
                        $tipo = 1;
                endswitch;

                IncomePartition::create([
                    'valid_from' => Carbon::today()->toDateString(),
                    'customer_id' => $lastId,
                    'web_provider_id' => null,
                    'income_partition_type_id' => $tipo,
                    'customer_partition_value' =>
                        str_replace(',', '.', trim($row['percentualeloc']) )
                ]);

                    $this->inserted++;

            }
        $i ++;}


    }

    public function getNumberOfInserted(): int
    {
        return $this->inserted;
    }

    /**
     * @return Throwable[]
     */
    public function getErrors(): array
    {
        return $this->errors;
    }

    public function rules(): array
    {
        return[
            'codlocale' => 'required|unique:customers,customer_code',
            'percentualeloc' => 'required',
            'tipo' => 'required|In:Utile,Introdotto Locale',
            'citta' => 'required',
            'provincia' => 'required',
            'indirizzo' => 'required',
            'insegna' => 'required|unique:customers,name,null,null,town,'.
                $this->citta.',province,'.$this->provincia.',address,'.$this->indirizzo
        ];
    }

    public function validationMessages(): array
    {
        return[
            'codlocale.unique' => 'esiste già un locale con lo stesso Codice Locale!',
            'percentualeloc.required' => 'manca il valore di Suddivisione Utile!',
            'tipo.required' => 'manca l\'indicazione della tipologia di Suddivisione!',
            'tipo.in' => 'le tipologie di Suddivisione Utile ammesse sono: "Utile" e "Introdotto Locale"!',
            'citta.required' => 'manca l\'indicazione della Città!',
            'provincia.required' => 'manca l\'indicazione della Provincia!',
            'insegna.required' => 'manca l\'indicazione dell\'Insegna!',
            'insegna.unique' => 'non possono esistere 2 locali con: Insegna, Città e Indirizzo uguali!',
        ];
    }


    public function batchSize(): int
    {
        return 200;
    }

    public function chunkSize(): int
    {
        return 200;
    }
}

When validator achieve the rule:

'insegna' => 'required|unique:customers,name,null,null,town,'.
                $this->citta.',province,'.$this->provincia.',address,'.$this->indirizzo

Laravel return an exception:

exception: "ErrorException"
file: "/Users/danielevitiello/Desktop/workspace/slotManagement/vendor/laravel/framework/src/Illuminate/Validation/Concerns/ValidatesAttributes.php"
line: 856
message: "Undefined array key 7"

The problem is $this->indirizzo that is a variable created merging 2 row:

if (empty($row['numcivico']) ) {
                    $this->indirizzo = trim($row['indirizzo']);
                }else{
                    $this->indirizzo = trim($row['indirizzo']). ', ' . trim($row['numcivico']);
                }

If I delete that part of unique rule of insegna, all code works fine, but I can't understand how to solve.

Could somebody help me?

Thanks

[patrickbrouwers]

Try dd() what you send to the validator, it's too much code for me to go through it all and check for errors sorry.

[DannyV90]

Yes it's too much code, sorry.
Well if I try dd($this->indirizzo) the result is a string:

I tried to collect all $this->indirizzo through the loop of import file (it's a test file with only three row) and dd() the array, this is result:

If you check the last part of my message you can see that $this->indirizzo could be conditionally a union of two $row[value], so it could have a comma or not.

But I don't think comma is the problem, don't you?

[patrickbrouwers]

That comma might be the issue yes. But that would be a Laravel validator issue, perhaps best to ask help on Stackoverflow/Laracasts on how to deal with that.

[DannyV90]

Before asking I learnt quickly how to use xdebug with phpstorm and I discovered that comma is the real problem, because: "NAZIONALE" was considered by validator how the value to check on column "address" in the table and ",33" how another value but without a column key. So I only added 2 double quote to "$this->indirizzo" in rule unique and now all works fine.

'insegna' => 'required|unique:customers,name,null,null,town,'.
                $this->citta.',province,'.$this->provincia.',address,"'.$this->indirizzo.'"'

Thank you very much!