CODEWITHSUNDEEP
phpmysqljsonlaravel-5laravel-query-builder
Mohd Riaz
Mohd Riaz

Reputation: 23

Laravel Join 2 tables , one data from first table and multiple row from second table

FirstTable

id | name |

1 | student1 |

2 | student2 |

3 | student3 |

4 | student4 |

==============================

SecondTable

Id | stud_id | subject | mark

1 | 1 | maths | 50

2 | 1 | english | 45

3 | 2 | maths | 20

4 | 3 | maths | 40

How would i query in laravel with the above table structure.. i need outpus as

{

    "id": 1,

    "name":"student1"

    "marks":[

        {

            "subject":"maths",

            "marks":50,

        },

        {

            "subject":"emglish",

            "marks":45,

        }

    ]

}

Upvotes: 0

Views: 5559

Answers (3)

Nitin
Nitin

Reputation: 528

I have done it in Laravel Query Builder. Please see below.

$stud = DB::table('FirstTable')
               ->join('SecondTable','FirstTable.id','=','SecondTable.stud_id')
               ->where('FirstTable.id','=','1')
               ->get();
dd($stud);

Upvotes: 1

user7986752
user7986752

Reputation:

You can create a relationships with these tables. In here you need that one to many relationships.

You need students , lessons tables

Students

Schema::create('students', function (Blueprint $table) {
$table->increments('id');
$table->string('student_name')
});

Lessons

Schema::create('lessons', function (Blueprint $table) {
$table->increments('id');
$table->integer('student_id');
$table->integer('exam_number'); // there will be many exams right ?
$table->integer('lesson1')
$table->integer('lesson2');
$table->integer('lesson-etc');
$table->timestamps();

$table->foreign('student_id')->references('id')->on('students')
});

Then edit your models as below

Student model;

public function lessons(){

       return $this->hasMany('App\Lesson','student_id','id');
    }

and in your lessons model;

 public function students(){

      return $this->belongsTo('App\Student');
    }

Then in your controller,

$students = Student::whereHas('lessons', function ($query) {

            $query->where("exam_number", 2 (or which exam));
        })->get();

Finally, in your blade;

 <table >
  <tr>
    <th>Student Name</th>
    <th>Lesson 1</th>
    <th>Lesson 2</th>
  </tr>
@foreach($students as $student)
  <tr>
    <td>{{$student->student_name}}</td>
    <td>{{$student->lessons->lesson1}}</td>
    <td>{{$student->lessons->lesson2}}</td>
  </tr>
@endforeach
</table>

This should be work

Upvotes: 0

Arun jai
Arun jai

Reputation: 170

I'm doing it in laravel eloquent way. In your Student model create relationship method. It is a student can have many subject relationship 

public function subject(){
return $this->hasMany('App\"the second table model name",'stud_id');

}

then you in controller you can access it like

public function index(){
$student = Student::find("Student_id")->subject;
dd($student);
}

please read the documentation for better understanding https://laravel.com/docs/5.5/eloquent-relationships

Upvotes: 0

Related Questions