Laravel 7 Eloquent Join Example Tutorial

Laravel 7 eloquent join example tutorial. In this tutorial, you will learn how many types of join and subquery join in laravel. And as well as how to use this laravel join with eloquent queries.

As well as learn using laravel eloquent join, you can join 2 or multiple tables in laravel with the multiple conditions.

Sometimes, you may want to get data more than two or n-tables in laravel. At that time, you can use laravel eloquent join (join, left join, right join, cross join, and advance join) and subquery join.

Laravel Eloquent Join Tutorial

Laravel provides the following types of eloquent join, they are:

1. Laravel Join Eloquent
2. Laravel Left Join / Right Join Eloquent
3. Laravel Cross Join Eloquent
4. Laravel Advanced Join Eloquent
5. Sub-Query Joins

1: Laravel Join Eloquent

Laravel Join eloquent selects records if the given column values matching in both tables. It’s also known as laravel inner join eloquent.

When joining two tables in Laravel, you should be specific about what you are choosing.

Let’s say you have two tables, users and posts. Both have ID columns, and there is a user_id in the post, which is a foreign key for the users’ table.

$user = User::join('posts', 'posts.user_id', '=', 'users.id')
       ->select('users.*')
       ->get();

2: Laravel Left Join / Right Join Eloquent

Left Join Eloquent

The Laravel LEFT JOIN eloquent returns all rows from the left table, even if there are no matches in the right table, The result is NULL from the right side.

 User::leftJoin('posts', 'posts.user_id', '=', 'users.id')
       ->select('users.*')
       ->get();

Right Join Eloquent

The Laravel Right JOIN eloquent returns all rows from the right table, even if there are no matches in the left table, The result is NULL from the left side.

 User::rightJoin('posts', 'posts.user_id', '=', 'users.id')
       ->select('users.*')
       ->get();

3: Laravel Cross Join Eloquent

The CROSS JOIN joined every row from the first table (T1) with every row from the second table (T2).

Size::crossJoin('colours')
     ->get();

4: Laravel Advanced Join Eloquent

If you would like to use a “where” style clause on your joins, you may use the where and orWhere methods on a join. Instead of comparing two columns, these methods will compare the column against a value:

DB::table('users')
        ->join('contacts', function ($join) {
            $join->on('users.id', '=', 'contacts.user_id')
                 ->where('contacts.user_id', '>', 5);
        })
        ->get();

5: Laravel Sub-Query Eloquent

Consider the following example of sub query joins:

DB::table('posts')
->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
->where('is_published', true)->groupBy('user_id');

Conclusion

Laravel eloquent join tutorial, you have learned following laravel joins, inner join, left join, right join, advanced join, cross join and sub-query join.