Posted inTechnology / Laravel / php / Programming

Laravel 6 Join multiple tables Eloquent Examples

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.

I am Jaydeep Gondaliya , a software engineer, the founder and the person running Pakainfo. I'm a full-stack developer, entrepreneur and owner of Pakainfo.com. I live in India and I love to write tutorials and tips that can help to other artisan, a Passionate Blogger, who love to share the informative content on PHP, JavaScript, jQuery, Laravel, CodeIgniter, VueJS, AngularJS and Bootstrap from the early stage.

Leave a Reply

Your email address will not be published. Required fields are marked *

We accept paid guest Posting on our Site : Guest Post Chat with Us On Skype