Joins in Laravel
Inner Join Clause
The query builder may also be used to add join clauses to your queries. To perform a basic “inner join”, you may use the join
method on a query builder instance. The first argument passed to the join
method is the name of the table you need to join to, while the remaining arguments specify the column constraints for the join. You may even join multiple tables in a single query:
use Illuminate\Support\Facades\DB;
$users = DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get();
Left Join / Right Join Clause
If you would like to perform a “left join” or “right join” instead of an “inner join”, use the leftJoin
or rightJoin
methods. These methods have the same signature as the join
method:
$users = DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->get();
$users = DB::table('users')
->rightJoin('posts', 'users.id', '=', 'posts.user_id')
->get();
Cross Join Clause
You may use the crossJoin
method to perform a “cross join”. Cross joins generate a cartesian product between the first table and the joined table:
$sizes = DB::table('sizes')
->crossJoin('colors')
->get();
Advanced Join Clauses
You may also specify more advanced join clauses. To get started, pass a closure as the second argument to the join
method. The closure will receive a Illuminate\Database\Query\JoinClause
instance which allows you to specify constraints on the “join” clause:
DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')->orOn(...);
})
->get();
If you would like to use a “where” clause on your joins, you may use the where
and orWhere
methods provided by the JoinClause
instance. 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();
Subquery Joins
You may use the joinSub
, leftJoinSub
, and rightJoinSub
methods to join a query to a subquery. Each of these methods receives three arguments: the subquery, its table alias, and a closure that defines the related columns. In this example, we will retrieve a collection of users where each user record also contains the created_at
timestamp of the user’s most recently published blog post:
$latestPosts = DB::table('posts')
->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
->where('is_published', true)
->groupBy('user_id');
$users = DB::table('users')
->joinSub($latestPosts, 'latest_posts', function ($join) {
$join->on('users.id', '=', 'latest_posts.user_id');
})->get();