In this tutorial you will learn about the Laravel 8 Joins Example Tutorial and its application with practical example.
In this Laravel 8 eloquent join example tutorial I will show you how many types of join and subquery join in laravel you will also learn to use laravel join with eloquent queries. In this tutorial you will learn about various joins in laravel eloquent model. In this article I’ll demonstrate the use of various types of laravel joins. In this step by step laravel 8 join tutorial I will share example of laravel eloquent join (join, left join, right join, cross join, and advance join) and subquery join.
Laravel 8 Eloquent Join Example Tutorial
In this Laravel 8 Eloquent Join Example Tutorial we will learn about following laravel eloquent join query with examples:
Types of Joins In Laravel 8
- Laravel Inner Join
- Laravel Left Join
- Laravel Right Join
- Laravel Cross Join
- Laravel Advanced Join
- Laravel Sub-Query Joins
1: Laravel Join Eloquent
In laravel if we join two tables it selects records if the given column values matching in both tables. It’s also known as laravel inner join eloquent. 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.’
Example 1: Laravel Eloquent Join() with 2 Tables
|
1 2 |
$users = User::join('posts', 'users.id', '=', 'posts.user_id') ->get(['users.*', 'posts.descrption']); |
When you dump the above laravel join query, you will get the following SQL query:
|
1 2 |
select `users`.*, `posts`.`descrption` from `users` inner join `posts` on `users`.`id` = `posts`.`user_id` |
Example 2: Laravel Eloquent Join() with 3 Tables
|
1 2 3 |
$users = User::join('posts', 'posts.user_id', '=', 'users.id') ->join('comments', 'comments.post_id', '=', 'posts.id') ->get(['users.*', 'posts.descrption']); |
When you dump the above laravel join 3 table query, you will get the following SQL query:
|
1 2 3 |
select `users`.*, `posts`.`descrption` from `users` inner join `posts` on `posts`.`user_id` = `users`.`id` inner join `comments` on `comments`.`post_id` = `posts`.`id` |
Example 3: Laravel Eloquent Join() with Multiple Conditions
|
1 2 3 4 |
$users = User::join('posts', 'posts.user_id', '=', 'users.id') ->where('users.status', 'active') ->where('posts.status','active') ->get(['users.*', 'posts.descrption']); |
When you dump the above laravel join with multiple conditions query, you will get the following SQL query:
|
1 2 3 |
select `users`.*, `posts`.`descrption` from `users` inner join `posts` on `posts`.`user_id` = `users`.`id` where `users`.`status` = active and `posts`.`status` = active |
2 – Laravel Left Join
Laravel left join query returns all rows from the left table, even if there is no matches in the right table. When you are using laravel left join eloquent with a query builder laravel left join fetch data from the left table. Even if there is no match in the right table.
Example 1: Laravel Left Join Query
|
1 2 3 |
Author::leftJoin('posts', 'posts.author_id', '=', 'authors.id') ->select('authors.*') ->get(); |
When you dump the above laravel left join query, you will get the following SQL query:
|
1 2 |
select `authors`.* from `authors` left join `posts` on `posts`.`author_id` = `authors`.`id` |
Example 2: Laravel Left Join with Multiple Condition
|
1 2 3 4 5 |
Author::leftJoin('posts', 'posts.author_id', '=', 'authors.id') ->select('authors.*') ->where('authors.status', 'active') ->where('authors.subscription', 'active') ->get(); |
When you dump the above laravel left join with multiple conditions query, you will get the following SQL query:
|
1 2 3 |
select `authors`.* from `authors` left join `posts` on `posts`.`author_id` = `authors`.`id` where `authors`.`status` = active and `authors`.`subscription` = active |
3 – Laravel Right Join
Laravel right join query returns all rows from the right table, even if there is no matches in the left table. When you are using laravel right join eloquent with a query builder laravel right join fetch data from the right table. Even if there is no match in the left table.
Example 1: Laravel Right Join Query
|
1 2 3 |
User::rightJoin('city','city.user_id','=','users.id') ->select('users.name','city.city_name') ->get(); |
When you dump the above laravel right join query, you will get the following SQL query:
|
1 2 |
select `users`.`name`, `city`.`city_name` from `users` right join `city` on `city`.`user_id` = `users`.`id` |
Example 2: Laravel Right Join with Multiple Condition
|
1 2 3 4 5 |
User::rightJoin('city','city.user_id','=','users.id') ->select('users.name','city.city_name') ->where('users.status', 'active') ->where('city.status', 'active') ->get(); |
When you dump the above laravel right join with multiple conditions query, you will get the following SQL query:
|
1 2 |
select `users`.`name`, `city`.`city_name` from `users` right join `city` on `city`.`user_id` = `users`.`id` where `users`.`status` = active and `city`.`status` = active |
4 – Cross Join Clause
In laravel, the cross join selects every row from the first table with every row from the second table.
|
1 2 |
Size::crossJoin('colours') ->get(); |
5 – Laravel Advanced Join
If in join query add some addition criteria with where clause it termed as advance join query.
|
1 2 3 4 5 6 |
DB::table('users') ->join('contacts', function ($join) { $join->on('users.id', '=', 'contacts.user_id') ->where('contacts.user_id', '>', 5); }) ->get(); |
6 – Laravel Sub-Query Joins
In this laravel join example we are using join query in sub query.
|
1 2 3 |
DB::table('posts') ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at')) ->where('is_published', true)->groupBy('user_id'); |
