Joining 3 tables in Laravel

I am using Laravel 7 and joining 3 tables for student exams. I have it working great except for the exam results column. It is pulling the first result it sees from the database and giving that same score to all the users. I am unsure how to write my joins in this instance. in my students table, I have the id and in my results table, I am trying to associate that id with the user_id. I ran a query in pypmyadmin and it gave the the results I was expecting SELECT * FROM oex_students INNER JOIN oex_results WHERE oex_students.id = oex_results.user_id However, in my controller, I am still unclear how to join in laravel. Here is my index controller:

public function index()
    {
        $data['portal'] = Oex_portal::get()->toArray();

        $data['exams'] = Oex_exam_master::where('status', '1')->get()->toArray();
        $data['result_info'] = Oex_result::select('oex_results.*', 'oex_results.result as result')
        ->get()->first();
        $data['students'] = Oex_students::select(['oex_students.*', 'oex_exam_masters.title as exam_name'])
        ->join('oex_results', 'oex_students.id', '=', 'oex_results.user_id')
        ->join('oex_exam_masters', 'oex_results.exam_id', '=', 'oex_exam_masters.id')
        ->get()
        ->toArray();


        $data['category'] = Oex_category::where('status', '1')->get()->toArray();
        $data['exams'] = Oex_exam_master::select(['oex_exam_masters.*', 'oex_categories.name as cat_name'])->join('oex_categories', 'oex_exam_masters.category', '=', 'oex_categories.id')->get()->toArray();
        return view('admin.dashboard', $data);
    }

and I am calling it in my blade as such:

@foreach($students as $key => $student)
                               <tr>
                                <td>{{ $key+1 }}</td>
                                <td>{{ $student['name'] }}</td>
                                <td>{{ $student['email'] }}</td>
                                <td>{{ $student['mobile_no'] }}</td>
                                <td>{{ $student['exam_name'] }}</td>
                                {{-- <td>{{ $student['result'] }}%</td> --}}
                                <td>{{ $result_info->result }} %</td>
                                {{-- <td>N/A</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

                               </tr>
                            @endforeach

If anybody can help me with my join statement, I would surely appreciate it. Thank you in advance.

Edit: When running dd($data['students']); I get:

array:3 [▼
  0 => array:11 [▼
    "id" => 14
    "name" => "John Stuwart"
    "email" => "[email protected]"
    "mobile_no" => "123456"
    "category" => null
    "exam" => "4"
    "password" => "y$SDsj/k0bizRRSi29ZL3K3erW3ljl2LpLmqBQ4pbTRzj1aDnGD5v8q"
    "status" => "1"
    "created_at" => "2020-11-23T18:06:29.000000Z"
    "updated_at" => "2020-11-23T18:06:29.000000Z"
    "exam_name" => "Business English Placement Test"
  ]
  1 => array:11 [▼
    "id" => 15
    "name" => "Guillermina Ramos Ambriz"
    "email" => "[email protected]"
    "mobile_no" => "+525517043338"
    "category" => null
    "exam" => "4"
    "password" => "y$UNohJhCMJyAU5Wl/MS44mONaopQ/clBsFTyZqZ3A6tGJrgsCkjIIu"
    "status" => "1"
    "created_at" => "2020-11-23T18:12:49.000000Z"
    "updated_at" => "2020-11-23T18:12:49.000000Z"
    "exam_name" => "Business English Placement Test"
  ]
  2 => array:11 [▼
    "id" => 16
    "name" => "Carlos Olivares"
    "email" => "[email protected]"
    "mobile_no" => "3601234567"
    "category" => null
    "exam" => "4"
    "password" => "y$hhLNxeEGIsuCwqxhUv1RL.MUIjSIBmK0pwg6hPEJavY79qiJK2U.S"
    "status" => "1"
    "created_at" => "2020-11-23T18:17:24.000000Z"
    "updated_at" => "2020-11-23T18:17:24.000000Z"
    "exam_name" => "Business English Placement Test"
  ]
]

ErikRobles

5 years ago

Level 3

In answer to my own question, I had to change the query to the following:

$data['students'] = Oex_students::select('name','email','mobile_no','oex_exam_masters.title as exam_name','result','oex_exam_masters.status as status')
		->from('oex_students')
		->join('oex_results', function($join) {
			$join->on('oex_students.id', '=', 'oex_results.user_id');
			})
		->join('oex_exam_masters', function($join) {
			$join->on('oex_exam_masters.id', '=', 'oex_results.exam_id');
			})
		->get();

amitjoshi

5 years ago

Level 1

Hello,

To accomplish your requirement,you need to add some code inside your oex_students model file something like this.

public function result() { return $this->belongsTo('App\Oex_results'); // 'Oex_results' change as per your tables model name. }

now you can get result of respective students like below.

$student = Oex_students::find(1)->result;

with this approche, you don't have to use join query, laravel will manage it from its side.

I am also new to laravel, so pardon me if it not work exactly as you want.

hope it will work.

Regards, Amit Joshi.

sr57

5 years ago

Level 39

Hi @erikrobles & @amitjoshi

New also to Laravel, I really think it's a great tool. but for query I only use Laravel syntax for simple query.

For complex query and for query that works directly in sql I use the raw syntax

DB::select("SELECT * FROM oex_students INNER JOIN oex_results WHERE oex_students.id = oex_results.user_id");

No need to learn new Laravel 'complex' syntax and more valuable to improve knowledge in pure sql that you'll need one day.

2 likes

Please or to participate in this conversation.