phplaraveleloquentupsert

Can't figure out a query to upsert collection into a tablr


I want to insert or update rows into my models table. But can't figure out the query. SmStudentAttendance This is my model. $students is my collection.

I have put the collection fields in arrays.

foreach ($students as $student) {
        array_push($temp_id, $student->id);
        array_push($temp_lastname, $student->last_name);
        array_push($temp_academic_id, $student->academic_id);
        array_push($temp_attendance, 'P');
        array_push($temp_attendancedate, $date);
        array_push($temp_schoolid, '1');
        array_push($temp_updatedby, '1');
        array_push($temp_createdby, '1');
    }

Now I want to insert them if a row for the student_id and attendance_date is not present in the table else update if it already exists. This is the query:

        SmStudentAttendance::upsert('attendance_type', $temp_attendance, 'attendance_date', $temp_attendancedate, 'student_id', $temp_id, 'created_by', $temp_createdby, 'updated_by', $temp_updatedby, 'school_id', $temp_schoolid, 'academic_id', $temp_academic_id);

Error I am geting:

Argument 1 passed to Illuminate\Database\Eloquent\Builder::upsert() must be of the type array, string given, called in D:\xampp\htdocs\sms\vendor\laravel\framework\src\Illuminate\Support\Traits\ForwardsCalls.php on line 23

Solution

  • You're creating your arrays for columns rather than rows, this will cause problems, consider this code instead:

    $studentRows = [];
    foreach ($students as $student) {
            $studentRows[] = [ 
                  'id' => $student->id,
                  'last_name' => $student->last_name,
                  'academic_id' => $student->academic_id,
                  'attendance_type' => 'P',
                  'attendance_date' => $date,
                   // .... rest of the fields
           ]
    }
    SmStudentAttendance::upsert($studentRows, [ 'id', 'last_name', 'academic_id' ], [ 'attendance_type', 'attendance_date' ]);
    
    
    

    In general the idea is you pass it an array of rows you want to upsert, then an array of fields to match and an array of fields to update. Then Laravel will make queries find all rows that match the fields specified and update those and then insert the rows that did not match the given fields.