How to perform a join query with multiple tables in Laravel?

In Laravel, you can use Eloquent ORM to perform a multi-table joint query.

Assuming we have two tables, one being the users table and the other being the posts table. The users table stores information about users, while the posts table stores the posts that users have published.

We can create two models, one is the User model and the other is the Post model. In the User model, we can establish a relationship with the Post model.

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

In the Post model, we can define the relationship with the User model.

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

Now, we can use these two models to perform a joint query. For example, if we want to retrieve all users and their posted posts, we can use the following code:

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

foreach ($users as $user) {
    echo $user->name;

    foreach ($user->posts as $post) {
        echo $post->title;
    }
}

In the code above, we use the with(‘posts’) method to preload a user’s posts. This way, when retrieving user data, the post data is also fetched together, avoiding the issue of N+1 queries.

In addition, you can also use other methods to perform multi-table queries, such as manually specifying the relationship between tables using the join method.

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

foreach ($users as $user) {
    echo $user->name;
    echo $user->title;
}

In the code above, we use the join method to specify the relationship between the users table and posts table, and the select method to choose the desired fields.

The above is the method for performing multi-table joins in Laravel. Depending on specific business needs, you can choose to use Eloquent model relationships or query builders for multi-table joins.