Level 51
@erikrobles have you watched the laravel 8 from scratch series? It will help you. You’re over complicating things.
Nov 1, 2021
8
Level 3
Unable to display data from two tables Laravel 8
Hello. I am working on an exam system in Laravel and I can't seem to get some data to appear in my view. Truth being, I am not sure how to go about it. I have a students table and in it, among other things, I have the following columns:
id, name, email, exam
exam is an integer that matches up with the id of a table called oex_exam_masters where I have the following columns:
id, title, category etc.
I am trying to get the oex_exam_masters title from the students exam (int) into the same view. I've tried with joins but I am missing something. I am not sure how to incorporate this into my view or set up the query correctly. Here is what I have tried in my controller:
public function StudentView() {
$data['exams'] = Oex_exam_master::where('status', '1')->get()->toArray();
// $columns = Oex_exam_master::select('oex_exam_masters.title as exam_name')->get();
// dd($columns);
$data['examData'] = Oex_exam_master::where('oex_exam_masters.id', 'students.exam')->get();
$data['allData'] = Student::with('getTeacherRelation', 'getLevelRelation', 'getCompanyRelation',)->orderBy('company_id', 'DESC')->get();
$data['students'] = Student::select('name','email','oex_exam_masters.title as exam_name','result','oex_exam_masters.status as status')
->from('students')
->join('oex_results', function($join) {
$join->on('students.id', '=', 'oex_results.user_id');
})
->join('oex_exam_masters', function($join) {
$join->on('oex_exam_masters.id', '=', 'oex_results.exam_id');
})
->get();
$data['student_list'] = Student::select('id','name','email', 'role_as')->get();
return view('admin.pages.students.students_view', $data);
}
And my table body in the view (and I know it is not correct yet, that is why I am asking for help):
<tbody>
@foreach($allData as $student)
<tr>
<td>{{ $student->id }}</td>
<td>{{ $student->name }}</td>
<td>{{ $student['getCompanyRelation']['name']}}</td>
<td>{{ $student['getLevelRelation']['name'] }}</td>
<td>{{ $student['getTeacherRelation']['name'] }}</td>
<td>{{ $student->email }}</td>
<td>{{ $student['exam_name'] }}</td>
@if($student['status']== 1)
<td><input data-id="{{ $student['id'] }}" class="student_status" type="checkbox" name="status" checked></td>
@else
<td><input data-id="{{ $student['id'] }}" class="student_status" type="checkbox" name="status"></td>
@endif
{{-- <td>{{ date('m-d-Y', strtotime($student->created_at)) }}</td> --}}
<td>
<a href="{{ route('admin.pages.students.edit', $student->id) }}" class="btn btn-primary">Edit</a>
<a href="{{ route('admin.pages.students.delete', $student->id) }}" class="btn btn-danger">Delete</a>
</td>
</tr>
@endforeach
</tbody>
I have been fighting this for a couple of days so any help would be greatly appreciated. Thank you in advance.
Level 3
@webrobert Yes, but I haven't finished it. I was thinking it might be better to handle this with relationships but not quite clear on how to do it. Every time I try, I get, exam_name or title not on this collection or Relationship undefined error. Any thoughts? Thank you.
Level 3
@webrobert I just changed my code to the following and it worked after manually updating the phpmyadmin. my exam title line to this:
<td>{{ $student['studentRelation']['title'] }}</td>
I created a relation in my Student.php model to this:
public function studentRelation() {
return $this->belongsTo(Oex_exam_master::class, 'exam', 'id');
}
and in my Controller, I simplified it to :
$data['allData'] = Student::with('getTeacherRelation', 'getLevelRelation', 'getCompanyRelation', 'studentRelation')->orderBy('company_id', 'DESC')->get();
However the question remains, how to handle the null in my database exam column. if it is null I will get an error. Thank you @webrobert
Level 51
If you can't get the Oex_exam_master easily it seems there is a relationship problem.
As a simple example, if you have properly defined and created your relationships (and related migrations) you could do something like this...
$students = Student::with('teachers', 'company')
->with('exams', fn($q) => $q->where('something', 'latest exam'))
->orderBy('company_id', 'DESC')
->get();
// DONE. Send it to the view!
return view('admin.pages.students.students_view', compact('students'));
then in the view you have everything...
@foreach($students as $student)
$student->company->name
$student->exams
$student->teachers
...
I hope this helps.
Level 51
@ErikRobles ha we replied together. let me review.
Level 51
// change these names this is completely vauge, not elequent.
public function studentRelation() {
return $this->belongsTo(Oex_exam_master::class, 'exam', 'id');
}
//idk... what it is but more over
public function oexExams() { // or oexExam if its one.
return $this->belongsTo(Oex_exam_master::class, 'exam', 'id');
}
then it makes more sense in your code...
$students = Student::with('oexExams')
In any case...
how to handle the null in my database exam column. if it is null I will get an error.
Student::whereNotNull('exam')
->with('getTeacherRelation', 'getLevelRelation', 'getCompanyRelation', 'studentRelation')
->orderBy('company_id', 'DESC')
->get();
1 like
Level 3
@webrobert Thank you for your response. If I simply output $student->exam, I will get it's id so that only kind of works. However, good news, I fixed it in the following way after going over your suggestions. I created the following in my view data table:
@if( $student->exam !=Null)
<td>{{ $student['oexExams']['title'] }}</td>
@else
<td>Not yet Assigned</td>
@endif
I did take your advice and change my relationship to oexExams. Yes, much more easy to understand that way. Thank you.
Level 51
@ErikRobles, great work.
And you can do this in one line...
<td>{{ $student['oexExams']['title'] ?? 'Not yet assigned' }}</td>
1 like
Please or to participate in this conversation.