Laravel withSum Where Condition Query Example Tutorial

withSum where condition laravel; In this tutorial, we will show you how to withSum relationship query with where conditions in laravel 10|9|8 applications.

In this example, we will consider a system where you have a database with “Orders” and “Products” tables. Each order has multiple products associated with it, and you want to calculate the total quantity of a specific product type in the order, but only for orders placed after a certain date.

Laravel withSum Where Condition Query Example Tutorial

Steps by step guide on how to withSum relationship query with where conditions in laravel 10|9|8 applications:

• Step 1: Set Up the Database
• Step 2: Seed the Database
• Step 3: Define the Relationships
• Step 4: Use withSum() with Where Condition
• Step 5: Display the Results
• Step 6: Define Route
• Step 7: Test the Application

Step 1: Set Up the Database

First, open terminal or cmd and run the following commands to create the necessary migration files and models for the “orders” and “products” tables. Define the relationships between the models.

php artisan make:model Order -m
php artisan make:model Product -m

Step 2: Seed the Database

Next run the following commands on cmd or terminal to seed the database with sample data for orders and products:

php artisan make:seeder OrdersTableSeeder
php artisan make:seeder ProductsTableSeeder

Once you have executed the above command, now you need to visit database/seeders/ directory and open OrdersTableSeeder.php & ProductsTableSeeder file. Then add the following code into it:

// database/seeders/OrdersTableSeeder.php
use App\Models\Order;
use App\Models\Product;

public function run()
{
    Order::factory()->has(Product::factory()->count(3))->create();
}

// database/seeders/ProductsTableSeeder.php
use App\Models\Product;

public function run()
{
    Product::factory()->create();
}

Step 3: Define the Relationships

Now, you need to define the relationship between product and order models. So, open product.php and order.php models, define the relationships:

// Order.php
class Order extends Model
{
    public function products()
    {
        return $this->hasMany(Product::class);
    }
}

// Product.php
class Product extends Model
{
    public function order()
    {
        return $this->belongsTo(Order::class);
    }
}

Step 4: Use withSum() with Where Condition

Now, let’s create a controller method that retrieves the total quantity of a specific product type for orders placed after a certain date.

// OrdersController.php
use App\Models\Order;

public function getProductQuantityAfterDate($productId, $date)
{
    $productQuantity = Order::withSum('products', 'quantity')
        ->whereHas('products', function ($query) use ($productId) {
            $query->where('product_id', $productId);
        })
        ->where('created_at', '>', $date)
        ->get();

    return view('product_quantity', compact('productQuantity'));
}

In this example, withSum() is used to calculate the total quantity of the specified product in the orders. The whereHas method is employed to filter orders that have the specified product, and an additional where clause is added to consider only orders placed after the given date.

Step 5: Display the Results

Next, visit resources/views directory and create product_quantity.blade.php file. And add the following code into it:

<!-- product_quantity.blade.php -->
@foreach ($productQuantity as $order)
    Order ID: {{ $order->id }} <br>
    Product Quantity: {{ $order->products_sum_quantity }} <br>
@endforeach

Step 6: Define Route

Finally, define a route in your web.php file to access the controller method:

// web.php
use App\Http\Controllers\OrdersController;

Route::get('/product-quantity/{productId}/{date}', [OrdersController::class, 'getProductQuantityAfterDate']);

And run the following command on cmd or terminal to migrations and seeders:

php artisan migrate --seed

Step 7: Test the Application

Now, when you access a URL like /product-quantity/1/2023-01-01, it will display the total quantity of the product with ID 1 in orders placed after January 1, 2023. Adjust the parameters as needed for your specific use case.

Conclusion

That’s it; you have learned how to use withsum relationship with where condition in laravel apps.

Recommended Tutorials