As per the title, I have a single excel file full of data (parent/child) in each row. The Laravel Excel site shows how to import, assuming a single model per row - but how does one import the child data? https://laravel-excel.maatwebsite.nl/3.1/imports/
eg 'pseudo' schema:
Schema::create('students', function (Blueprint $table) {
$table->increments('id')->unsigned()->index();
$table->string('student_code',16);
$table->string('student_name',64);
$table->string('student_surname',64);
});
Schema::create('student_courses', function (Blueprint $table) {
$table->increments('id')->unsigned()->index();
$table->integer('student_id')->unsigned()->index();
$table->string('course', 32);
$table->date('start_date');
$table->date('end_date');
$table->timestamps();
});
Schema::create('student_contacts', function (Blueprint $table) {
$table->increments('id')->unsigned()->index();
$table->integer('student_id')->unsigned()->index();
$table->string('contact_name', 32);
$table->string('contact_number', 32);
$table->timestamps();
});
Eg File: students.xlsx
Student Code | Student Name | Student Surname | Course | Course Start Date | Course End Date | Contact Person | Contact Numbers
ABC1 | Arnold | Clarp | C++ | 2019-01-01 | 2019-12-01 | Boogle | 555-111-222
DEF2 | Delta | Flork | English | 2019-01-02 | 2019-12-02 | Google | 555-111-333
GHI3 | Goblin | Clark | Science | 2019-01-03 | 2019-12-03 | Foogle | 555-111-444
Assuming my import code:
class StudentsImport implements ToModel, WithStartRow
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
return new Student([
'student_code' => $row[1],
'student_name' => $row[2],
'student_surname' => $row[3],
]);
}
/**
* @return int
*/
public function startRow(): int
{
return 2;
}
}
Where would I actually go about plugging in the import of the "child" course/contact data? I'm guessing that instead of 'returning a new student' - I would actually first assign it to a variable, then import it then? Is this the correct way? eg:
public function model(array $row)
{
$student = new Student([
'student_code'=> $row[1],
'student_name'=> $row[2],
'student_surname'=> $row[3],
])
$student->courses()->create([
'course'=>$row[4],
'start_date'=>$row[5],
'end_date'=>$row[6]
]);
$student->contacts()->create([
'contact_name'=>$row[7],
'contact_number'=>$row[8]
]);
return $student;
}
//actual code as it stands, no longer 'pseudo':
$student = new Student([
'bursary_provider_id' => 1,
'bursary_provider_reference' => 'xxx',
'student_name' => $row[1],
'student_initials' => $row[3],
'student_surname' => $row[2],
'passport_number' => $row[7],
'passport_expiration' => \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row[9]),
'country_id' => 7,
'id_number' => $row[6],
'status' => $status,
'notes' => $row[5]
]);
if (isset($row[10])) {
$student->visas()->create([
'country_id' => 201,
'visa_reference_number' => $row[10],
'visa_expiration_date' => \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row[11])
]);
Error (its not passing across the parent id - from my understanding is that it hasnt yet created the parent at this stage of the code so there is no id to pass across)
SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'student_id' cannot be null (SQL: insert into student_visas (country_id, visa_reference_number, visa_expiration_date, student_id, updated_at, created_at) values (201, ABCHFV4, 2019-12-31 00:00:00, , 2019-01-11 08:03:06, 2019-01-11 08:03:06))
