I'm using Yajra DataTable now, and I was able to display the data from StudentApplicants with relationship with users and course to my table, but I can't search the data from users relationship. I can only search column of StudentApplicants but not with users column. I already tried this but it's not working EAGER LOADING RELATIONSHIP. For now, I'm using the manual search for my first model.
Controller
if ($request->ajax()) {
$data = StudentApplicants::with('users')->with('courses')->where('student_applicants.course_id', $courses->id)->select('student_applicants.*');
return DataTables::of($data)
->addIndexColumn()
->addColumn('image', function ($status) {
$url = asset('uploads/' . $status->image);
return '<img src="' . $url . '" class="img-thumbnail img-circle"
width="50" alt="Image">';
})
->addColumn('status', function ($status) {
if ($status->status == '1') {
return '<span class="badge badge-success">Approved</span>';
} else if ($status->status == '2') {
return '<span class="badge badge-danger">Rejected</span>';
} else {
return '<a href="/admin/achievers-award/' . $status->courses->course_code . '/approve/' . $status->id . '" class="btn btn-success btn-sm btn-icon-split">
<span class="icon text-white-50">
<i class="fas fa-check"></i>
</span>
<span class="text">Approve</span>
</a>
<a href="/admin/achievers-award/' . $status->courses->course_code . '/reject/' . $status->id . '" class="btn btn-danger btn-sm btn-icon-split" >
<span class="icon text-white-50">
<i class="fa-sharp fa-solid fa-xmark"></i>
</span>
<span class="text">Reject</span>
</a>';
}
})
->addColumn('action', function ($status) {
$btn = '';
$btn .= '<a href="/admin/achievers-award/' . $status->courses->course_code . '/' . $status->id . '" class="btn btn-sm btn-secondary"><i class="fa-regular fa-eye"></i> </a> ';
$btn .= '<button type="button" class="btn btn-sm btn-danger deleteUserbtn"><i class="fa fa-trash"></i> </button>';
return $btn;
})
->filter(function ($instance) use ($request) {
if ($request->get('status') == '0' || $request->get('status') == '1' || $request->get('status') == '2') {
$instance->where('status', $request->get('status'));
}
if (!empty($request->get('search'))) {
$instance->where(function ($w) use ($request) {
$search = $request->get('search');
$w->orWhere('gwa_1st', 'LIKE', "%$search%")
->orWhere('gwa_2nd', 'LIKE', "%$search%");
});
}
})
->rawColumns(['image', 'status', 'action'])
->make(true);
Student Applicants Model
public function courses()
{
return $this->belongsTo(Courses::class, 'course_id', 'id');
}
public function users()
{
return $this->belongsTo(User::class, 'user_id','id');
}
Javascript
columns: [
{
data: "users.stud_num",
name: "users.stud_num",
className: "font-weight-bold",
},
{ data: "users.first_name", name: "users.first_name" },
{ data: "users.last_name", name: "users.last_name" },
{ data: "courses.course_code", name: "courses.course_code" },
{ data: "gwa_1st" },
{ data: "gwa_2nd" },
{
data: "image",
className: "text-center",
},
{ data: "status", className: "text-center" },
{
data: "action",
orderable: false,
searchable: false,
},
],
Answer
Instead of using the filter, create if condition that will filter the data. It's very difficult to use the search in nested relationship in Yajra DataTable so creating your own will be the best possible solution.
$courses = Courses::where('course_code', $course_code)->first();
$model = StudentApplicants::with('users', 'courses')->where('student_applicants.course_id', $courses->id)->where('award_applied', '1')->select('student_applicants.*');
if ($request->get('status') == '0' || $request->get('status') == '1' || $request->get('status') == '2') {
$model->where('status', $request->get('status'))->get();
}
if ($request->ajax()) {
return DataTables::eloquent($model)
->addColumn('studno', function (StudentApplicants $stud) {
return $stud->users->stud_num;
})
->addColumn('fname', function (StudentApplicants $stud) {
return $stud->users->first_name;
})
->addColumn('lname', function (StudentApplicants $stud) {
return $stud->users->last_name;
})
->addColumn('course', function (StudentApplicants $stud) {
return $stud->courses->course_code;
})
->addColumn('image', function ($data) {
$url = asset('uploads/' . $data->image);
return '<img src="' . $url . '" class="img-thumbnail img-circle"
width="50" alt="Image">';
})
->addColumn('status', function (StudentApplicants $data) {
if ($data->status == '1') {
return '<span class="badge badge-success">Approved</span>';
} else if ($data->status == '2') {
return '<span class="badge badge-danger">Rejected</span>';
} else {
return '<a href="/admin/achievers-award/' . $data->courses->course_code . '/approve/' . $data->id . '" class="btn btn-success btn-sm btn-icon-split">
<span class="icon text-white-50">
<i class="fas fa-check"></i>
</span>
<span class="text">Approve</span>
</a>
<a href="/admin/achievers-award/' . $data->courses->course_code . '/reject/' . $data->id . '" class="btn btn-danger btn-sm btn-icon-split" >
<span class="icon text-white-50">
<i class="fa-sharp fa-solid fa-xmark"></i>
</span>
<span class="text">Reject</span>
</a>';
}
})
->addColumn('action', function ($data) {
$btn = '';
$btn .= '<a href="/admin/achievers-award/' . $data->courses->course_code . '/' . $data->id . '" class="btn btn-sm btn-secondary"><i class="fa-regular fa-eye"></i> </a> ';
$btn .= '<a href="javascript:void(0)" data-toggle="tooltip" class="btn btn-sm btn-danger deleteFormbtn" data-id="' . $data->id . '"><i class="fa fa-trash"></i> </button>';
return $btn;
})
->rawColumns(['image', 'status', 'action'])
->make(true);
In Javascript
columns: [
{
data: 'studno',
name: 'users.stud_num',
className: "font-weight-bold",
},
{
data: 'fname',
name: 'users.first_name'
},
{
data: 'lname',
name: 'users.last_name'
},
{
data: "course",
name: "courses.course_code"
},
{ data: "gwa_1st" },
{ data: "gwa_2nd" },
{
data: "image",
searchable: false,
className: "text-center",
},
{ data: "status", className: "text-center" },
{
data: "action",
orderable: false,
searchable: false,
},
],