Blog

Laravel, a popular PHP framework, provides an elegant and expressive way to interact with databases through its Eloquent ORM (Object-Relational Mapping). In this article, we’ll explore how to perform an inner join with multiple conditions using Laravel Eloquent.

Understanding Inner Join with Multiple Conditions

An inner join is a type of database operation that combines rows from two or more tables based on a related column between them. Multiple conditions allow us to specify more complex relationships for joining tables. Laravel’s Eloquent makes this process seamless and readable.

Setting Up the Database

Before we delve into the Laravel Eloquent code, let’s set up a hypothetical scenario. Consider a database with two tables: users and orders. The users table contains user information, while the orders table holds order details.

users Table

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->timestamps();
});

orders Table

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained();
    $table->decimal('total_amount');
    $table->timestamps();
});

Defining Eloquent Models

Create Eloquent models for both tables using Artisan commands:

php artisan make:model User
php artisan make:model Order

Writing the Eloquent Query

Now, let’s write an Eloquent query to perform an inner join with multiple conditions. Suppose we want to retrieve orders along with user details where the order amount is greater than $100 and the user’s name is ‘John’.

use App\Models\User;
use App\Models\Order;

$orders = Order::join('users', function ($join) {
    $join->on('orders.user_id', '=', 'users.id')
        ->where('users.name', '=', 'John');
})
->where('orders.total_amount', '>', 100)
->select('orders.*', 'users.name as user_name')
->get();

In this example, we use the join method to perform an inner join between the orders and users tables. The closure passed to the join method specifies the conditions for the join. We match the user_id column from the orders table with the id column from the users table and also filter based on the user’s name.

The where method is then used to add an additional condition for the order amount, ensuring it is greater than $100. Finally, the select method is used to specify the columns we want to retrieve, including renaming the users.name column as user_name for clarity.

Putting It All Together

Let’s put the complete code into a controller method for better organization:

use App\Models\User;
use App\Models\Order;

public function getOrdersWithUserDetails()
{
    $orders = Order::join('users', function ($join) {
        $join->on('orders.user_id', '=', 'users.id')
            ->where('users.name', '=', 'John');
    })
    ->where('orders.total_amount', '>', 100)
    ->select('orders.*', 'users.name as user_name')
    ->get();

    return view('orders.index', compact('orders'));
}

This controller method retrieves the orders meeting the specified conditions and passes them to a view named orders.index. Adjust the view according to your application’s needs.

Conclusion

Performing an inner join with multiple conditions using Laravel Eloquent is a straightforward process. Leveraging the expressive syntax of Eloquent, developers can write clear and concise code to retrieve the desired data from their databases. This example demonstrates the power and simplicity of Laravel Eloquent when working with complex relationships.

• Check our tools small Tools
• Check our tools website Word count