eggplantSword's avatar

Get full list of validation errors

I'm trying to validate some data that comes from an excel file, my goal is:

  • get excel file from user
  • read data and pass through validator
  • return list of errors or "successful" message

Now I've tried two ways but they aren't working like I want. The first way is using Laravel Excel validation, but this stops every time a row has something wrong making the validating super tedious as I have to keep uploading a modified file for every time it finds an error. I want a full list of errors.

        try {
            Excel::import(new SalePointValidate, $request->file('excel'));

            return response()->json(['success' => true]);
        } catch (\Maatwebsite\Excel\Validators\ValidationException $e) {
            $failures = $e->failures();
            $errors = [];

            foreach ($failures as $failure) {
                $errors[] = [
                    'row' => $failure->row(), // row that went wrong
                    'attribute' => $failure->attribute(), // either heading key (if using heading row concern) or column index
                    'errors' => $failure->errors(), // Actual error messages from Laravel validator
                    'values' => $failure->values() // The values of the row that has failed.
                ];
            }

            return response()->json(['success' => false, 'errors' => $errors], 200);
        } catch (\Exception $e) {
            Log::error($e);

            return response()->json(['success' => false], 500);
        }

Here is the import file

class SalePointValidate implements ToModel, WithValidation, WithHeadingRow, WithChunkReading
{
    private $errors = [];
    
    public function model(array $row)
    {
        return null;
    }

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

    public function rules(): array
    {
        return [
            '*.nombre' => ['required', Rule::unique(SalePoint::class, 'name')],
            '*.nombre_comercial' => ['required', Rule::unique(SalePoint::class, 'business_name')],
            '*.latitud' => ['required', 'numeric'],
            '*.longitud' => ['required', 'numeric'],
            '*.canal' => ['required', Rule::exists(Channel::class, 'name')],
            '*.cadena' => ['required', Rule::exists(Chain::class, 'name')],
            '*.formato' => ['required', Rule::exists(Format::class, 'name')],
            '*.pais' => ['required', Rule::exists(Country::class, 'name')],
            '*.provincia' => ['required', Rule::exists(Province::class, 'name')],
            '*.canton' => ['required', Rule::exists(Canton::class, 'name')],
            '*.distrito' => ['required', Rule::exists(District::class, 'name')],
        ];
    }

    public function onFailure(Failure ...$failures)
    {
        foreach ($failures as $failure) {
            $this->errors[] = $failure;
        }
    }

    public function getErrors()
    {
        return $this->errors;
    }
}

I also tried this but this I'm not sure what's up

        $excel = Excel::toArray(new SalePointValidate, $request->file('excel'));

        Validator::make($excel, [
            '*.nombre' => ['required', Rule::unique(SalePoint::class, 'name')],
            '*.nombre_comercial' => ['required', Rule::unique(SalePoint::class, 'business_name')],
            '*.latitud' => ['required', 'numeric'],
            '*.longitud' => ['required', 'numeric'],
            '*.canal' => ['required', Rule::exists(Channel::class, 'name')],
            '*.cadena' => ['required', Rule::exists(Chain::class, 'name')],
            '*.formato' => ['required', Rule::exists(GpsFormat::class, 'name')],
            '*.pais' => ['required', Rule::exists(Country::class, 'name')],
            '*.provincia' => ['required', Rule::exists(Province::class, 'name')],
            '*.canton' => ['required', Rule::exists(Canton::class, 'name')],
            '*.distrito' => ['required', Rule::exists(District::class, 'name')],
        ])->validateWithBag('test');

This returns this which is incorrect cause the file does have stuff in it, the same file fails with the first method but for the actual validation errors, this just says it's required when it is there.

["El campo "0.cadena" es obligatorio."]
["El campo "0.canal" es obligatorio."]
["El campo "0.canton" es obligatorio."]
["El campo "0.distrito" es obligatorio."]
["El campo "0.formato" es obligatorio."]
["El campo "0.latitud" es obligatorio."]
["El campo "0.longitud" es obligatorio."]
["El campo "0.nombre" es obligatorio."]
["El campo "0.nombre_comercial" es obligatorio."]
["El campo "0.pais" es obligatorio."]
["El campo "0.provincia" es obligatorio."]
"El campo \"0.nombre\" es obligatorio. (and 10 more errors)"

How can I get that full list of errors using either method ?

0 likes
11 replies
MikhArt's avatar

Try to add SkipsFailures and SkipsOnFailure

class SalePointValidate implements ToModel, WithValidation, WithHeadingRow, SkipsOnFailure, WithChunkReading
{
    use SkipsFailures;
...

And then something like this

$import = new SalePointValidate();
$import->import($request->file('excel'));

foreach ($import->failures() as $failure) {
     // Do whatever you want with validation errors occured
     $failure->row();
     $failure->attribute();
     $failure->errors();
     $failure->values();
}
2 likes
eggplantSword's avatar

@MikhArt thanks, my teammate ended up helping me before I saw your comment but this is super close to what we ended up with!

2 likes
MikhArt's avatar

@msslgomez glad that you solved your problem. Can you please mark this answer as best for future comers to be able to see the solution. And your question will be shown as Solved then. Thanks

Tray2's avatar

Is this something that is to be done one time, or is it once in a while, or is it frequently?

If it is a one time thing I would just use the first method from this post.

https://nanonets.com/blog/import-excel-into-mysql/

If it is once in a while, I would still use the first method from the post above, but I would write a artisan command for it.

As for frequently, I would still use the above mentioned method for the import, but instead of a artisan command, I would use a scheduled job for it.

Of course after the import you need to move the correct data from the import table to the actual table, but that is pretty straight forward, any records left in the table are incorrect. The same goes here, depending on how frequently this needs to be done, I would have that logic manually with scripts, in the artisan command, in the scheduled job.

eggplantSword's avatar
eggplantSword
OP
Best Answer
Level 9

@Tray2 In this case it would happen whenever a user needs to validate a file, also I don't need to import any of the data just validate it and return the errors so I'm not sure that method works for that.

This is the final code

class SalePointValidate implements ToModel, WithValidation, WithHeadingRow, WithChunkReading, SkipsOnFailure
{
    use SkipsFailures, Importable;

    public function model(array $row)
    {
        return null;
    }

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

    public function rules(): array
    {
        return [
            '*.nombre' => ['required', Rule::unique(SalePoint::class, 'name')],
            '*.nombre_comercial' => ['required', Rule::unique(SalePoint::class, 'business_name')],
            '*.latitud' => ['required', 'numeric'],
            '*.longitud' => ['required', 'numeric'],
            '*.canal' => ['required', Rule::exists(Channel::class, 'name')],
            '*.cadena' => ['required', Rule::exists(Chain::class, 'name')],
            '*.formato' => ['required', Rule::exists(Format::class, 'name')],
            '*.pais' => ['required', Rule::exists(Country::class, 'name')],
            '*.provincia' => ['required', Rule::exists(Province::class, 'name')],
            '*.canton' => ['required', Rule::exists(Canton::class, 'name')],
            '*.distrito' => ['required', Rule::exists(District::class, 'name')],
        ];
    }
}
		$salePointValidate = new SalePointValidate;

        $salePointValidate->import($request->file('excel'));
        $toArray = $salePointValidate->toArray($request->file('excel'));

        $failures = $salePointValidate->failures();
        $errors = [];

        foreach ($failures as $failure) {
            $errors[] = [
                'row' => $failure->row(),
                'attribute' => $failure->attribute(),
                'errors' => $failure->errors(), 
                'values' => $failure->values() 
            ];
        }

        return response()->json(['fileData' => $toArray, 'errors' => $errors], 200);
Tray2's avatar

@msslgomez So it's just to validate the information in the file row for row, not storing it anywhere?

Is the data generated somewhere or is it manually entered?

If it is the manually entered I would just create the validation inside the excel file itself.

eggplantSword's avatar

@Tray2 Correct the idea is to NOT store any of the data and just validate it. The file is not generated but it must have certain data and that data must match the data online. The reason for not saving anything is because the data on the excel isn't for the logged in system but for a system that is being used through an api, after the file is validated as correct it would get passed to someone on the dev team to upload it to the other system (api) through something like postman. The idea here is to reduce the workload on the dev team who had to also check the files manually which takes for-ever (6h+) depending on the line count, this way the user validates it and the dev team just has to upload it.

Tray2's avatar

@msslgomez That sounds like a really bad way to go about it in general.

I would just load the data into the database directly like I described, if it should even leave it at all. Excel as a DTO sound really bad.

eggplantSword's avatar

@Tray2 System A is a system that depends on Sale Points, the company has over 15k Sale Points that are stored in System B where System A is a tenant type system and there are a couple tenants all who use different Sale Points.

System B is internal and run only by the dev team, we do this to avoid when happened with our old system: a ton a repeated Sale Points and wrong data from users inputting stuff.

The people logged into System A can search through all the Sale Points in System B in order to add them to System A. The Sale Points have to exist on System B first in order to add them into System A.

So this is a file of Sale Points that aren't on System B that they need in System A. That's why they validate it from System A and send it to us to upload to System B. After it's uploaded they can search again and finally add them.

Most times the data they try to send us is horribly wrong and riddled with mistakes that you basically have to go line by line checking every value. I don't want to spend all day checking a file like that. Maybe its not the best but it saves us 6h+ of work 🤷‍♀️.

Please or to participate in this conversation.