How to import excel file with relationship id?

Question

I trying to import the lines of the job request by using an Excel file but I don't know how to store the id of the job request in the lines of it.

I have 2 models that are

Job Request

 protected $table = "jobs";
    protected $fillable=['job_id', 'customer_id', 'note', 'created_by', 'updated_by'];

    public function products(){
        return $this->hasMany(Product::class);
    }

    public function users(){
        return $this->belongsTo('App\User', 'created_by');
    }

    public function lines(){
        return $this->hasMany(Line::class);
    }

Line

    protected $table = 'lines';
    protected $fillable=['job_id', 'product_id', 'user_id', 'reason_id', 'created_by', 'updated_by', 'created_at', 'updated_at' ];

    public function users(){
        return $this->belongsTo('App\User', 'user_id');
    }

    public function jobs(){
        return $this->belongsTo(Job::class, 'job_id');
    }

    public function products(){
        return $this->belongsTo(Product::class, 'product_id');
    }

When I add the line I will open the job request and add the line. The line will save with the product_id and job_id.

                                                <form action="{{ route('file-import') }}" method="POST" class="form-default" enctype="multipart/form-data">


                                                    @csrf

                                                    <div class="col-lg-12">
                                                        <div class="card">
                                                            <!--div class="card-header">
                                                                <h4 class="card-title">Upload Excel File</h4>
                                                            </div--><!--end card-header-->
                                                            <div class="card-body">
                                                                <div class="mt-3">
                                                                    <div class="col-lg-12 mb-2 mb-lg-0">
                                                                        <label class="form-label" for="pro-end-date">Products</label>

                                                                        <label for="file">File:</label>
                                                                        <input id="file" type="file" name="file" class="form-control">
                                                                        <input type="hidden" name="job_id" value="{{ $jobs->id }}" />

                                                                    </div><!--end col-->
                                                                </div>
                                                            </div> <!-- end card-body -->
                                                        </div> <!-- end card -->
                                                    </div> <!-- end col -->

                                                    <div class="modal-footer">
                                                        <button type="submit" class="btn btn-soft-primary btn-sm">Save</button>
                                                        <button type="button" class="btn btn-soft-secondary btn-sm" data-bs-dismiss="modal">Cancel</button>
                                                    </div><!--end modal-footer-->


                                                </form>

This is the LineController

    public function fileImport(Request $request)
    {

        $file = $request->file;

        Excel::import(new LineImport, $file);

        return back();
    }

This is the LineImport model

    public function model(array $row)
    {
        $job = Job::find(1);

        return new Line([
            'product_id' => $row['product_id'],
            'reason_id' => $row['reason_id'],
            'updated_by' => Auth::id(),
            'job_id' => $job->id,
        ]);
    }

I don't know how to store the job_id (this job_id is the record that I opening before uploading the Excel file).

with the product_id in the Excel file.

to the line.

This is the thing that I need the solution to solve.

Thank you in advance. I’m looking forward to your reply.

Answer 1

I'm slightly confused by your wording, but I think this is what you're getting at?

You have a CustomJob model (let's call it that to avoid confusion with Laravel 'jobs'), which corresponds to a custom_jobs table in your database, with the columns 'id', 'customer_id', 'note', 'created_by', 'updated_by'. (I'm not sure why you have an 'id' field, presumably, and a 'job_id' field, so the latter is probably not relevant)

Each CustomJob has one or more Lines, which is associated with a CustomJob (through lines.custom_job_id = custom_jobs.id), a User (through lines.user_id = users.id) and a Product (through lines.product_id = products.id).

It looks like you also need an association with Reasons (through lines.reason_id = reasons.id).

The user submits the upload form on the front of the site, which contains a file, and a custom_job_id. In your LineController you're using Maatweb/Excel, by the look of it, so you pass the file off to that, which basically splits down the rows. You want it to be returning a new Line() but you can't, because a Line() requires a custom_job_id and you've not given it that?

If that's the case, then (a) you need to get the custom_job_id from the submitted form, and (b) pass that to the Import so that it can use it :

Get the custom_job_id from the submitted form

This is easy enough :

$file = $request->file;
$custom_job_id = $request->job_id;

Pass the custom_job_id to the Import

First you need to edit your LineController to send the custom_job_id :

Excel::import(new LineImport($custom_job_id), $file);

Then you need to add a Constructor to the LineImport so that it knows to expect a custom_job_id coming in :

public function  __construct(string $custom_job_id)) {
    $this->custom_job_id= $custom_job_id;
}

You can then access that in your model when populating the Line

public function model(array $row)
{
    return new Line([
        'product_id' => $row['product_id'],
        'reason_id' => $row['reason_id'],
        'updated_by' => Auth::id(),
        'custom_job_id' => $this->custom_job_id,
    ]);
}