Muhammad Arslan Muhammad Arslan - 6 months ago 20
MySQL Question

Joins in laravel

I create 2 tables courses and students and insert data, when return data from those 2 table (foreign key relation in those tables) it gives me all data but name column is same in students and courses tables, when i display name it shows only course name but i want both student and course name

here is my code for controller

public function index()
{
$data = DB::table('students')
->join('courses', function($join)
{
$join->on('students.course_id', '=', 'courses.course_id')
->where('courses.course_id', '=', 101);
})
->get();
// dd($data);
return view('student.home', compact('data'));
}


display code

@foreach($data as $d)
{{ $d->roll_no }}
{{ $d->name }}
<br>
@endforeach


courses table migration

public function up()
{
Schema::create('courses', function (Blueprint $table) {
// $table->increments('id');
$table->string('course_id');
$table->string('name');
$table->string('credit_hour');
$table->timestamps();

$table->primary('course_id');
});
}


students table migration

public function up()
{
Schema::create('students', function (Blueprint $table) {
// $table->increments('id');
$table->string('roll_no');
$table->string('name');
$table->string('email');
$table->string('address');
$table->string('course_id');

$table->timestamps();

$table->primary('roll_no');
$table->foreign('course_id')->references('course_id')->on('courses')->onDelete('cascade');
});
}


how i display both course name and student name

Answer

You can add a custom select to your query builder and rename existing columns to prevent columns being excluded in the results.

Example:

DB::table('students')

    ->select(['*', DB::raw('students.name as student_name')])

    ->join(....

You can access this property like:

@foreach ($data as $d)

    {{ $d->student_name }}

@endforeach

Use dd($d) in your foreach and see what attributes you can access now.