How to Import Excel with Table Relation in Laravel 7?

Question

I'd like to ask.

I want to make excel import feature using laravel-excel package. Which, the data I imported has relational values ​​to another table.

so, when i import excel it will save the data in two tables.

the first table, stores data such as name and file path. And the second table, saves the details of the imported excel file and adds the relationship to the first table.

below are the two tables that I have

booked_vouchers:

• id
• name
• path

booked_voucher_details:

• id
• booked_voucher_id
• voucher_code
• course_code
• user_name

and below are the codes that I have made in the controller, view and import files.

Form

<form action="{{ route('booked.import')}}" method="POST" enctype="multipart/form-data">
    @csrf
    <div class="form-group">
        <label for="name">Name</label>
        <input name="name" type="text" class="form-control" required>
    </div>
    <div class="form-group">
        <label for="file">File</label>
        <input name="file" type="file" class="form-control" required>
    </div>
    <button type="submit" class="btn btn-primary">Import</button>
</form>

Controller

    public function import(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'file' => 'required|mimes:csv,xlx,xls,xlsx'
        ]);

        if ($validator->fails()) {
            return back()->with('toast_error', $validator->messages()->all()[0])->withInput();
        }

        $data = new BookedVoucher();

        $data->name = $request->name;
        $fileName = time().'_'.$request->file->getClientOriginalName();
        $filePath = $request->file('file')->storeAs('reports', $fileName, 'public');

        $data->file = $filePath;
        $data->created_by = \Auth::user()->id;

        if ($data->save()) {
            Excel::import(new BookedVoucherDetailImport, $request->file('file'));
        }

        return redirect()->back()->with('success','Data have been imported');
    }

BookedVoucherDetailImport.php

<?php

namespace App\Imports;

use App\Model\BookedVoucher;
use App\Model\BookedVoucherDetail;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithStartRow;

class BookedVoucherDetailImport implements ToModel, WithStartRow
{
    /**
     * @return int
     */
    public function startRow(): int
    {
        return 2;
    }
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        $data = BookedVoucher::first();
        return new BookedVoucherDetail([
            'booked_voucher_id'     => $data->id,
            'course_code'           => $row[0], 
            'voucher_code'          => $row[1],
            'user_name'         => $row[2],
            'status'                => 'OK'
        ]);
    }
}

How do I set the booked_voucher_id value to be the same as the id value from the booked_voucher table that has been saved before the excel import process?

*for now, if I use code like in the BookedVoucherDetailImport.php file, the result of the booked_voucher_id value in the booked_voucher_details table is always incorrect.

Answer 1

You could pass the BookedVoucher to the Import class:

Excel::import(new BookedVoucherDetailImport($data), $request->file('file'));

Then update your BookedVoucherDetailImport to be:

<?php

namespace App\Imports;

use App\Model\BookedVoucher;
use App\Model\BookedVoucherDetail;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithStartRow;

class BookedVoucherDetailImport implements ToModel, WithStartRow
{
    /**
     * @var BookedVoucher 
     */
    protected $bookedVoucher;

    /**
     * @param BookedVoucher $bookedVoucher
     */
    public function __construct(BookedVoucher $bookedVoucher)
    {
        $this->bookedVoucher = $bookedVoucher;
    }
    
    /**
     * @return int
     */
    public function startRow(): int
    {
        return 2;
    }

    /**
     * @param array $row
     *
     * @return \Illuminate\Database\Eloquent\Model|null
     */
    public function model(array $row)
    {
        return new BookedVoucherDetail([
            'booked_voucher_id' => $this->bookedVoucher->id,
            'course_code'       => $row[0],
            'voucher_code'      => $row[1],
            'user_name'         => $row[2],
            'status'            => 'OK',
        ]);
    }
}