Issue with Deleting Records in Laravel Eloquent

Question

Introduction to the project I am building an Online Examination System in Laravel (PHP v8.1.0) where I have two modules, Admin & Student. The admin will create subjects, exams and the QnA's. There are paid exam & packages for students to buy as well and along with that students will be also managed by the admin. The student on the other hand, will be attempting free exams or buy a paid exam and attempt that. They can also view the results of the exam they had attempted.

Introduction to Tables & Models In my MySQL db named "oes", I have multiple tables that maintain data of the system, I am showing the structure of the tables where I am facing the issue,

• Table 1: exams_attempt has id exam_id user_id status marks created_at updated_at

This table is used to store the exam attempts of the student, some exams can have only 1 attempt and some may have more than one attempt.

• Table 2: exams_answers has id attempt_id question_id answer_id created_at updated_at

This table is used to store the answers of the exam the student had attempted. Here, there can be a chance where the attempt_id is similar and different answer_id's. The attempt_id here is coming from the id of the 'exams_attempt' table.

Known Bug I have this bug that if I delete a student from the admin side, These table's data are not getting deleted and to maintain data integrity, these data must be deleted. I also have a payments table where the student's payment details will be stored. So, along with the payments, the exam attempts and the exam answers have to be deleted if the student is deleted.

Existing Methods Before I tell my tried methods, I want you to go through my Models (exams_answer & exams_attempt) and the controller's code to delete other records (i.e. Payments, etc.) to get better view of my code and my issue.

• Model ExamAnswer (exams_answers):

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class ExamAnswer extends Model
{
    use HasFactory;
    public $table = "exams_answers";

    protected $fillable = [
        'attempt_id',
        'question_id',
        'answer_id'
    ];

    public function question()
    {
        return $this->hasOne(Question::class,'id','question_id');
    }

    public function answers()
    {
        return $this->hasOne(Answer::class,'id','answer_id');
    }
}

• Model ExamAttempt (exams_attempt):

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class ExamAttempt extends Model
{
    use HasFactory;
    public $table = "exams_attempt";

    protected $fillable = [
        'exam_id',
        'user_id'
    ];

    public function user()
    {
        return $this->hasOne(User::class,'id','user_id');
    }

    public function exam()
    {
        return $this->hasOne(Exam::class,'id','exam_id');
    }
}

• function deleteStudent from AdminController.php

public function deleteStudent(Request $request)
    {
        try {
            
            User::where('id',$request->id)->delete();
            //Delete Exam Payments
            ExamPayments::where('user_id',$request->id)->delete();
            //Delete Exam Attempt
            ExamAttempt::where('user_id', $request->id)->delete();
            return response()->json(['success'=>true,'msg'=>'Student Deleted successfully!']);

        }catch(\Exception $e){
            return response()->json(['success'=>false,'msg'=>$e->getMessage()]);
        }
    }

The current code will delete data from 'exam_payments' and 'exams_attempt' table when I delete a student.

Tried Methods Since I want to delete data from 'exams_answers' and 'exams_attempt', I tried multiple ways to delete the data and maintain data integrity. Here, are the ways I tried to execute this bug...

A) Modify the 'exams_answers' model and add the following function.

public function attempt()
    {
        return $this->hasOne(ExamAttempt::class, 'id', 'attempt_id');
    }

B) Try ORM Method similar to delete Payments & Attempt (which work) and delete the data from 'exams_answer' ExamAnswer::where('attempt_id', $request->id)->delete();

C) Since the above method did not work, I tried to implement an SQL query directly to my MySQL's SQL executer,

DELETE ea
FROM exams_answers ea
JOIN exams_attempt ea1 ON ea.attempt_id = ea1.id;
WHERE ea1.id = 11;

This query brought me positive results as the data from the 'exams_answer' table which is associated with the 'exams_attempt' table got deleted leaving others that did not matched.

Conclusion Message Since, I am new to Laravel development...building such applications in itself is a great achievement for me and I hope the big brains of this community would help me bring a solution to my issue. I would appreciate insights, suggestions, or code examples to help implement deletion for the 'exams_answers' table when a student is deleted from the users table. If there are any additional considerations or configurations that I may be missing, please share them to ensure data consistency and maintainability.

Thank you for your valuable assistance!