Eloquent Join Query in Laravel 11 & Laravel 12

In Laravel 11, you can use Eloquent relationships or the query builder to perform JOIN queries with tables. Below are different ways to perform JOINs in Laravel using Eloquent and DB Query Builder.

1. Basic INNER JOIN using Query Builder

use Illuminate\Support\Facades\DB;
$users = DB::table('users')
->join('posts', 'users.id', '=', 'posts.user_id')
->select('users.name', 'users.email', 'posts.title', 'posts.body')
->get();

Explanation: This joins the users table with the posts table on `users.id = posts.user_id`.

2. LEFT JOIN using Query Builder

$users = DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->select('users.name', 'users.email', 'posts.title')
->get();

Explanation: This returns all users even if they don’t have posts.

3. RIGHT JOIN using Query Builder

$users = DB::table('users')
->rightJoin('posts', 'users.id', '=', 'posts.user_id')
->select('users.name', 'posts.title')
->get();

Explanation: This returns all posts even if they don’t have a user.

4. JOIN with WHERE Condition

$users = DB::table('users')
->join('posts', 'users.id', '=', 'posts.user_id')
->where('users.status', 'active')
->select('users.name', 'posts.title')
->get();

Explanation: This retrieves only users with active status.

5. Using Eloquent Relationship (Recommended)

If you’re using Eloquent Models, you should define a relationship in the `User` model:

Define the Relationship in the Model (`User.php`)

class User extends Model
{
public function posts()
{
return $this->hasMany(Post::class, 'user_id');
}
}

Query Using Eloquent Relationship

$users = User::with('posts')->get();

Explanation: This fetches all users with their posts using Eloquent without writing raw queries.

6. Multiple Table Join in Laravel

$users = DB::table('users')
->join('posts', 'users.id', '=', 'posts.user_id')
->join('comments', 'posts.id', '=', 'comments.post_id')
->select('users.name', 'posts.title', 'comments.comment_text')
->get();

Explanation: Joins `users`, `posts`, and `comments` tables.

7. LEFT JOIN with Eloquent Query

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

Explanation: Performs a LEFT JOIN using Eloquent instead of Query Builder.

8. Using RAW SQL in Laravel Query Builder

$users = DB::table('users')
->join('posts', function ($join) {
$join->on('users.id', '=', 'posts.user_id')
->where('posts.status', '=', 'published');
})
->select('users.name', 'posts.title')
->get();

Explanation: This joins the `users` table with `posts`, but only includes published posts.