Import Laravel Excel to two models with relationship-CodePudding

I am trying to import Excel file [using Laravel Excel library] into two different models that has relationship between them.

I tried the below solution by returning two models import, and the second one is getting the inserted ID from the first model => Item::where('barcode', $row['barcode'])->pluck('id')->first(), but always getting NULL item_id in the second model.

<?php

namespace App\Imports;

use App\Models\Admin\Item;
use App\Models\Admin\Brand;
use App\Models\Admin\Style;
use App\Models\Admin\Gender;
use App\Models\Admin\Category;
use App\Models\Admin\Section;
use App\Models\Admin\Season;
use App\Models\Admin\Vendor;
use App\Models\Admin\Size;
use App\Models\Admin\Color;
use App\Models\Admin\Grade;
use App\Models\User\Cprices1;
use Illuminate\Support\Collection;
use Illuminate\Validation\Rule;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithUpserts;
use Illuminate\Support\Facades\Validator;

class CodingImport implements ToModel, WithValidation, WithHeadingRow, WithChunkReading, WithBatchInserts, WithUpserts
{
    use Importable;

    public function model(array $row)
    {
        return [
            new Item([
                'barcode'     => $row['barcode'],
                'name'        => $row['name'],
                'description' => $row['description'],
                'brand_id'    => Brand::where('name', $row['brand_id'])->pluck('id')->first(),
                'style_id'    => Style::where('name', $row['style_id'])->pluck('id')->first(),
                'gender_id'   => Gender::where('name', $row['gender_id'])->pluck('id')->first(),
                'category_id' => Category::where('name', $row['category_id'])->pluck('id')->first(),
                'section_id'  => Section::where('name', $row['section_id'])->pluck('id')->first(),
                'season_id'   => Season::where('name', $row['season_id'])->pluck('id')->first(),
                'vendor_id'   => Vendor::where('name', $row['vendor_id'])->pluck('id')->first(),
                'color_id'    => Color::where('name', $row['color_id'])->pluck('id')->first(),
                'size_id'     => Size::where('name', $row['size_id'])->pluck('id')->first(),
                'grade_id'    => Grade::where('name', $row['grade_id'])->pluck('id')->first()
            ]),
            new Cprices1([
                'item_id'    => Item::where('barcode', $row['barcode'])->pluck('id')->first(),
                'msrp'       => $row['msrp'],
                'rtp'        => $row['rtp'],
                'item_cost'  => $row['cost']
            ])
        ];
    }

    public function rules(): array
    {
        return [
            'barcode'     => Rule::unique('items', 'barcode'),
            'brand_id'    => Rule::exists('brands', 'name'),
            'style_id'    => Rule::exists('styles', 'name'),
            'gender_id'   => Rule::exists('genders', 'name'),
            'category_id' => Rule::exists('categories', 'name'),
            'section_id'  => Rule::exists('sections', 'name'),
            'season_id'   => Rule::exists('seasons', 'name'),
            'vendor_id'   => Rule::exists('vendors', 'name'),
            'color_id'    => Rule::exists('colors', 'name'),
            'size_id'     => Rule::exists('sizes', 'name'),
            'grade_id'    => Rule::exists('grades', 'name')
        ];
    }

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

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

    public function uniqueBy()
    {
        return 'barcode';
    }
}

Adding updated code based on @Muhammad Dyas Yaskur repl

<?php

namespace App\Imports;

use App\Models\Admin\Item;
use App\Models\Admin\Brand;
use App\Models\Admin\Style;
use App\Models\Admin\Gender;
use App\Models\Admin\Category;
use App\Models\Admin\Section;
use App\Models\Admin\Season;
use App\Models\Admin\Vendor;
use App\Models\Admin\Size;
use App\Models\Admin\Color;
use App\Models\Admin\Grade;
use App\Models\User\Cprices1;
use Illuminate\Support\Collection;
use Illuminate\Validation\Rule;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithUpserts;
use Illuminate\Support\Facades\Validator;
use Maatwebsite\Excel\Row;
use Maatwebsite\Excel\Concerns\OnEachRow;

class CodingImport implements ToModel, OnEachRow, WithValidation, WithHeadingRow, WithChunkReading, WithBatchInserts, WithUpserts
{
    use Importable;

    public function model(array $row)
    {
        return new Item([
            'barcode'     => $row['barcode'],
            'name'        => $row['name'],
            'description' => $row['description'],
            'brand_id'    => Brand::where('name', $row['brand_id'])->pluck('id')->first(),
            'style_id'    => Style::where('name', $row['style_id'])->pluck('id')->first(),
            'gender_id'   => Gender::where('name', $row['gender_id'])->pluck('id')->first(),
            'category_id' => Category::where('name', $row['category_id'])->pluck('id')->first(),
            'section_id'  => Section::where('name', $row['section_id'])->pluck('id')->first(),
            'season_id'   => Season::where('name', $row['season_id'])->pluck('id')->first(),
            'vendor_id'   => Vendor::where('name', $row['vendor_id'])->pluck('id')->first(),
            'color_id'    => Color::where('name', $row['color_id'])->pluck('id')->first(),
            'size_id'     => Size::where('name', $row['size_id'])->pluck('id')->first(),
            'grade_id'    => Grade::where('name', $row['grade_id'])->pluck('id')->first()
        ]);
    }

    public function onRow(Row $row)
    {
        $row = $row->toArray();

        Cprices1::create([
            'item_id' => Item::where('barcode', $row['barcode'])->pluck('id')->first(),
            'msrp' => $row['msrp'],
            'rtp' => $row['rtp'],
            'item_cost' => $row['cost']
        ]);
    }

    public function rules(): array
    {
        return [
            'barcode'     => Rule::unique('items', 'barcode'),
            'brand_id'    => Rule::exists('brands', 'name'),
            'style_id'    => Rule::exists('styles', 'name'),
            'gender_id'   => Rule::exists('genders', 'name'),
            'category_id' => Rule::exists('categories', 'name'),
            'section_id'  => Rule::exists('sections', 'name'),
            'season_id'   => Rule::exists('seasons', 'name'),
            'vendor_id'   => Rule::exists('vendors', 'name'),
            'color_id'    => Rule::exists('colors', 'name'),
            'size_id'     => Rule::exists('sizes', 'name'),
            'grade_id'    => Rule::exists('grades', 'name')
        ];
    }

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

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

    public function uniqueBy()
    {
        return 'barcode';
    }
}

On the controller:

Excel::import(new CodingImport, $request->file);

Any idea how to do this?

CodePudding user response：

Laravel Excel import don't commit or do batch insert until it is complete. If you want to use relation you should use OnEachRow.

Here is the example

public function onRow(Row $row)
{
    $row = $row->toArray();
    Cprices1()::create([
            'item_id'    => Item::where('barcode', $row['barcode'])->pluck('id')->first(),
            'msrp'       => $row['msrp'],
            'rtp'        => $row['rtp'],
            'item_cost'  => $row['cost']
        ]);
}

or full code:

<?php

namespace App\Imports;

use App\Models\Admin\Item;
use App\Models\Admin\Brand;
use App\Models\Admin\Style;
use App\Models\Admin\Gender;
use App\Models\Admin\Category;
use App\Models\Admin\Section;
use App\Models\Admin\Season;
use App\Models\Admin\Vendor;
use App\Models\Admin\Size;
use App\Models\Admin\Color;
use App\Models\Admin\Grade;
use App\Models\User\Cprices1;
use Illuminate\Validation\Rule;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Row;
use Maatwebsite\Excel\Concerns\OnEachRow;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithUpserts;

class CodingImport implements ToModel, OnEachRow, WithValidation, WithHeadingRow, WithChunkReading, WithBatchInserts
{
    use Importable;

    public function model(array $row)
    {
        return new Item([
            'barcode' => $row['barcode'],
            'name' => $row['name'],
            'description' => $row['description'],
            'brand_id' => Brand::where('name', $row['brand_id'])->pluck('id')->first(),
            'style_id' => Style::where('name', $row['style_id'])->pluck('id')->first(),
            'gender_id' => Gender::where('name', $row['gender_id'])->pluck('id')->first(),
            'category_id' => Category::where('name', $row['category_id'])->pluck('id')->first(),
            'section_id' => Section::where('name', $row['section_id'])->pluck('id')->first(),
            'season_id' => Season::where('name', $row['season_id'])->pluck('id')->first(),
            'vendor_id' => Vendor::where('name', $row['vendor_id'])->pluck('id')->first(),
            'color_id' => Color::where('name', $row['color_id'])->pluck('id')->first(),
            'size_id' => Size::where('name', $row['size_id'])->pluck('id')->first(),
            'grade_id' => Grade::where('name', $row['grade_id'])->pluck('id')->first()
        ]);
    }

    public function onRow(Row $row)
    {
        $row = $row->toArray();

        Cprices1::create([
            'item_id' => Item::where('barcode', $row['barcode'])->pluck('id')->first(),
            'msrp' => $row['msrp'],
            'rtp' => $row['rtp'],
            'item_cost' => $row['cost']
        ]);
    }

    public function rules(): array
    {
        return [
            // 'barcode' => Rule::unique('items', 'barcode'),
            'brand_id' => Rule::exists('brands', 'name'),
            'style_id' => Rule::exists('styles', 'name'),
            'gender_id' => Rule::exists('genders', 'name'),
            'category_id' => Rule::exists('categories', 'name'),
            'section_id' => Rule::exists('sections', 'name'),
            'season_id' => Rule::exists('seasons', 'name'),
            'vendor_id' => Rule::exists('vendors', 'name'),
            'color_id' => Rule::exists('colors', 'name'),
            'size_id' => Rule::exists('sizes', 'name'),
            'grade_id' => Rule::exists('grades', 'name')
        ];
    }

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

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

    public function uniqueBy()
    {
        return 'barcode';
    }
}

Just note:

When using OnEachRow you cannot use batch inserts, as the model is already persisted in the onRow method.