Import Laravel Excel to two models with relationship

Question

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?

Answer 1

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.