Laravel 6 Join multiple tables Eloquent Examples

by
Laravel Redirect To Another URL / Web Page - HTTP Laravel Redirects

Today, We want to share with you Laravel 6 Join multiple tables Eloquent.In this post we will show you Laravel 6 JOINS Eloquent Tutorial Example From Scratch, hear for ffffffffffffffff we will give you demo and example for implement.In this post, we will learn about Laravel query multiple eloquent relationships with an example.

Laravel’s Eloquent tutorials and tips & tricks

There are the Following The simple About Laravel 6 Eloquent Relationships Tutorial Example From Scratch Full Information With Example and source code.

As I will cover this Post with live Working example to develop Laravel 6 JOINS Tutorial INNER OUTER LEFT RIGHT CROSS, so the how to join 3 tables using laravel 6 eloquent is used for this example is following below.

Example 1: Join or Left Join only

Join or Left Join only with where condition in laravel

$studentId = 1050;
$query = Students::SELECT('students.id','students.name','teachers.name')
->leftJoin('teachers', function($query) use($studentId) {
$query->on('students.teacher_id','=','teachers.id')
->where('students.teacher_id', '=', $studentId);
})->get();

Example 2: Left join add where clause

Laravel Left join add where clause to right table

$students = \DB::table('students')
->select(\DB::raw("students.stid , teachers.local_path as title_image" ))
->leftJoin('teachers',function ($join) {
    $join->on('teachers.owner_stid', '=' , 'students.stid') ;
    $join->where('teachers.relation','=','student_name') ;
});

Example 3: Inner join with multiple conditions

Laravel 6 – inner join with multiple conditions example using Query Builder

$student = Student::select("students.*","teachers.id as itemId","teams.id as teamId")
            ->join("teachers","teachers.student_id","=","students.id")
            ->join("teams",function($join){
                $join->on("teams.student_id","=","students.id")
                    ->on("teams.teacher_id","=","teachers.id");
            })
            ->get();
print_r($student);

Example 4: Join with subquery

Laravel 6 Join with subquery in Query Builder Example

$data = DB::table("students")
  ->select("students.*","students_count.year_group","students_count.age_group")
  ->join(DB::raw("(SELECT 
      students_count.id_stud,
      GROUP_CONCAT(students_count.year) as year_group,
      GROUP_CONCAT(students_count.age) as age_group
      FROM students_count
      GROUP BY students_count.id_stud
      ) as students_count"),function($join){
        $join->on("students_count.id_stud","=","students.id");
  })
  ->groupBy("students.id")
  ->get();
print_r($data);

Example 5: subquery in select statement

Laravel 6 How to make subquery in select statement?

$data = DB::table("items")
  ->select("items.*",
            DB::raw("(SELECT SUM(items_stock.stock) FROM items_stock
                        WHERE items_stock.item_id = items.id
                        GROUP BY items_stock.item_id) as item_stock"),
            DB::raw("(SELECT SUM(items_sell.sell) FROM items_sell
                        WHERE items_sell.item_id = items.id
                        GROUP BY items_sell.item_id) as item_sell"))
  ->get();

Example 6: groupby having with DB::raw

How to use groupby having with DB::raw in Laravel 6 Query Builder?

$students = DB::table("students")
            ->select("students.id","students.title"
                ,"students.min_age"
                ,DB::raw('SUM(students_count.age) as total_age'))
            ->join("students_count","students_count.id_stud","=","students.id")
            ->groupBy("students.id")
            ->having("total_age","<",DB::raw("students.min_age"))
            ->get();
print_r($students);

Example 7: Join or Left Join where no foreign key

Join or Left Join where no foreign key – using where condition in Laravel 6

$clientId = 9889;
$query = Students::SELECT('students.id','students.name','teachers.name')
->leftjoin('teachers', function($query) use($clientId){
$query->on(DB::raw('(teachers.id '), DB::raw('"'.$clientId.'")'));
})
->get();

Example 8: Left join with last record

Left join with last record of right table in Laravel & Mysql

$query = Students::select('students.id', 'students.user_name','teachers.created_at as last_activity_date')
->leftJoin('teachers', function($query) {
                    $query->on('students.id','=','teachers.client_id')
                        ->whereRaw('teachers.id IN (select MAX(teaca2.id) from teachers as teaca2 join students as us2 on us2.id = teaca2.client_id group by us2.id)');
})
->where('students.role_type_id', Students::STUDENT_ROLE_TYPE)->get();

Example 9: if else and case when

if else and case when in Laravel 6 query

using If and Else:

Students::SELECT(DB::raw('IF(MIN(students.join_at) IS NULL, students.started_at, MIN(students.join_at)) as join_at'))->get();

using Case When:

Students::SELECT(DB::raw('CASE WHEN COUNT(students.client_id) != 0 THEN COUNT(students.client_id) ELSE 1 END as client_id'))->get();

Example 10: Joins in Laravel Eloquent

Using Joins in Laravel Eloquent Queries

$studentsData = Students::where('id', $studentId)
    ->leftJoin('teachers', 'student.teachers', '=', 'teachers.id')
    ->select('student.id','teachers.name')->first();
Web Programming Tutorials Example with Demo

Read :

Summary

You can also read about AngularJS, ASP.NET, VueJs, PHP.

I hope you get an idea about Laravel 5.8 Eloquent ORM where join table.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.

No related posts.

Leave a Comment