SQL Joins with Laravel Eloquent

by

I was going through a LinkedIn post in which someone explained the SQL Joins. Let’s let “artisans” know how we use them with the medium of Laravel Eloquent.

In the realm of databases, SQL joins are key tools for pulling related data from various tables. If you’re a Laravel developer, you can make your life easier by using Eloquent relationships, which simplify these intricate queries and help you write tidy, easy-to-maintain code. In this post, we’re going to delve into the various types of SQL joins and show you how to achieve the same results using Laravel Eloquent relationships.

INNER JOIN

An INNER JOIN returns only the rows that have matching values in both tables. For instance, suppose you have User and Post models, where a user can have multiple posts. Here’s how you can get users with their posts:

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

class Post extends Model {
    public function user() {
        return $this->belongsTo(User::class);
    }
}

// Controller or any place you want to use it
$users = User::has('posts')->with('posts')->get();

LEFT JOIN

A LEFT JOIN retrieves all records from the left table and the matched records from the right table. If no match is found, NULLs are returned for columns from the right table. To get all users with their posts, including users who have no posts:

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

RIGHT JOIN

A RIGHT JOIN retrieves all records from the right table and the matched records from the left table.

While Laravel Eloquent doesn’t natively support RIGHT JOIN, you can achieve the same effect using raw queries or the query builder. Below is an example of how you can perform a RIGHT JOIN using Laravel’s query builder.

For example, to get all posts with their users, including posts that have no corresponding user:

$posts = Post::leftJoin('users', 'posts.user_id', '=', 'users.id')
             ->select('posts.*', 'users.name as user_name')
             ->get();

FULL JOIN

A FULL JOIN retrieves all records when there is a match in either the left or right table. Eloquent doesn’t directly support FULL JOIN, but you can simulate it using a union of LEFT JOIN and RIGHT JOIN.

$leftJoin = User::leftJoin('posts', 'users.id', '=', 'posts.user_id')
                ->select('users.*', 'posts.title as post_title');

//"select `users`.*, `posts`.`title` as `post_title` from `users` left join `posts` on `users`.`id` = `posts`.`user_id`"

$rightJoin = Post::rightJoin('users', 'posts.user_id', '=', 'users.id')
                 ->select('users.*', 'posts.title as post_title');

//"select `users`.*, `posts`.`title` as `post_title` from `posts` right join `users` on `posts`.`user_id` = `users`.`id`"

$fullJoin = $leftJoin->union($rightJoin)->get();

//(select `users`.*, `posts`.`title` as `post_title` from `users` left join `posts` on `users`.`id` = `posts`.`user_id`) 
union (select `users`.*, `posts`.`title` as `post_title` from `posts` right join `users` on `posts`.`user_id` = `users`.`id`)

You may find this interesting: Value Of BackSlash Prefix In PHP Functions

Conclusion

Understanding SQL joins and their Eloquent equivalents is crucial for efficient data retrieval and manipulation. Laravel Eloquent provides a clean, intuitive way to manage these relationships, allowing you to focus on your application’s logic rather than complex SQL queries. By mastering these techniques, you’ll be well-equipped to handle any data-related challenges in your Laravel applications. Happy coding!

Leave a Comment