Level 122
where is 'base price' ?
May 27, 2024
3
Level 1
Laravel 8 Conditional SUM based on table column
Hi, I am building an ecommerce application. In my case the product will have multiple options. Based on the option is selected, i want to calculate the sum of query result based on table column like price_prefix. this column value can hold "=" or "+" or "-"
My Query:
$option_value = ProductOptionValue::where('product_id', $request->product_id)->whereIn('product_option_value_id', [1,2,3,4])->get();
The result:
Illuminate\Support\Collection {#2704
#items: array:4 [
0 => array:4 [
"type" => "Egg"
"original_price" => "0.0000"
"selling_price" => "0.0000"
"price_prefix" => "="
]
1 => array:4 [
"cream_type" => "Fresh Cream"
"original_price" => "0.0000"
"selling_price" => "0.0000"
"price_prefix" => "-"
]
2 => array:4 [
"shape" => "Heart Shape"
"original_price" => 50.0000
"selling_price" => 50.0000
"price_prefix" => "+"
]
3 => array:4 [
"size" => "Medium"
"original_price" => "1075.0000"
"selling_price" => "1075.0000"
"price_prefix" => "="
]
]
#escapeWhenCastingToString: false
}
Now i want to calculate sum of original_price and selling_price based on price_prefix from above collection. When the price_prefix value is "=" then it should be base_price and when price_prefix having "+" then base_price + selling_price and when price_prefix having "-" then base_price - selling_price I am expecting the end result as 1125.0000 from above collection. I know to simply calculate the sum of collection using sum() method
$selling_price = collect($collection)->sum('selling_price');
But how can i conditionally calculate this!?
Can some one help me please...
Thank you.
Level 1
@snapey
Hi, Thank you very much for quick response.
In my case, When no options are selected then i have to consider $product->price as base_price. When the user selected option and the price_prefix having value "=" (equal) then the base_price should be original_price from the collection. and when price_prefix having "+" or "-" (Plus or Minus) value then the base price will be $product->price
$product = Product::find($request->product_id);
Once the user selected Product options, then i have to calculate based on price_prefix value from result
For more clarity my product controller looks like:
class ProductController extends Controller
{
protected $product;
public function __construct(ProductInterface $product)
{
$this->product = $product;
}
/**
* Get product option
* @param int $product_option_id
* @return array
*/
public function findOption(Request $request)
{
try {
$option_value = ProductOptionValue::where('product_id', $request->product_id)->whereIn('product_option_value_id', array_values($request->options))->get();
$product = $this->product->get($request->product_id);
if ($option_value) {
$original_price = 0;
$price = 0;
$result = $option_value->map(function($value) use ($product, &$price, &$original_price) {
if ($product->special_discount_end > now()) {
if ($value->price_prefix == '=') {
$original_price = $value->price;
if ($product->special_discount_type == 'flat') {
$price = $value->price - $product->special_discount;
} else if ($product->special_discount_type == 'percentage') {
$price = ($value->price - ($value->price / 100) * $product->special_discount);
}
} else if ($value->price_prefix == '+') {
$original_price = $product->price + $value->price;
if ($product->special_discount_type == 'flat') {
$price = ($product->price + $value->price) - $product->special_discount;
} else if ($product->special_discount_type == 'percentage') {
$price = ($product->price + $value->price - ($value->price / 100) * $product->special_discount);
}
} else if ($value->price_prefix == '-') {
$original_price = $product->price - $value->price;
if ($product->special_discount_type == 'flat') {
$price = ($product->price - $value->price) - $product->special_discount;
} else if ($product->special_discount_type == 'percentage') {
$price = ($product->price - $value->price - ($value->price / 100) * $product->special_discount);
}
}
} else {
if ($value->price_prefix == '=') {
$original_price = $value->price;
$price = $value->price;
} else if ($value->price_prefix == '+') {
$original_price = $product->price + $value->price;
$price = $product->price + $value->price;
} else if ($value->price_prefix == '-') {
$original_price = $product->price - $value->price;
$price = $product->price - $value->price;
}
}
return [
'option_name => $value->option_name,
'original_price' => $original_price,
'selling_price' => $price,
'price_prefix' => $value->price_prefix,
];
});
}
$orig_price = 0;
$sell_price = 0;
$quantity = 0;
$subtract = 0;
foreach ($result as $item) {
$orig_price = $item['original_price'];
$sell_price = $item['selling_price'];
}
$data = [
'original_price' => $orig_price,
'selling_price' => $sell_price,
];
return response()->json($data);
} catch (\Exception $e) {
return response()->json([
'error' => __('Oops...Something Went Wrong'),
'message' => __($e->getMessage()),
]);
}
}
}
My Post Request
{
"options": {
// "product_option_id": "product_option_value_id
"108": 159,
"109": 160,
"110": 161,
"111": 164
},
"product_id": 6
}
Thank you.
Level 1
Hi, I have finally did myself and got the result as i expected. This may helpful for someone who looking for same solution. For simplification i have created OptionCalculator.php under App\Services
<?php
namespace App\Services;
use App\Models\ProductOptionValue;
class OptionCalculator
{
public static function calculateFinalPrice($product, array $selectedOptions)
{
// Fetch all selected options in one query
$option_values = ProductOptionValue::where('product_id', $product->id)
->whereIn('product_option_value_id', array_values($selectedOptions))
->get();
if ($option_values->isNotEmpty()) {
// Initialize variables
$original_price = 0;
$option_price = 0;
$quantity = 0;
// Iterate and re map the reselt to object
$result = $option_values->map(function($optionValue) use ($product, &$option_price, &$original_price, &$quantity) {
if ($product->special_discount_end > now()) {
if ($optionValue->price_prefix == '=') {
$original_price = $optionValue->price;
$quantity = $optionValue->quantity;
if ($product->special_discount_type == 'flat') {
$option_price = $optionValue->price - $product->special_discount;
} else if ($product->special_discount_type == 'percentage') {
$option_price = ($optionValue->price - ($optionValue->price / 100) * $product->special_discount);
}
} else if ($optionValue->price_prefix == '+') {
$original_price = $product->price + $optionValue->price;
$quantity = $optionValue->quantity;
if ($product->special_discount_type == 'flat') {
$option_price = ($product->price + $optionValue->price) - $product->special_discount;
} else if ($product->special_discount_type == 'percentage') {
$option_price = ($product->price + $optionValue->price - ($optionValue->price / 100) * $product->special_discount);
}
} else if ($optionValue->price_prefix == '-') {
$original_price = $product->price - $optionValue->price;
$quantity = $optionValue->quantity;
if ($product->special_discount_type == 'flat') {
$option_price = ($product->price - $optionValue->price) - $product->special_discount;
} else if ($product->special_discount_type == 'percentage') {
$option_price = ($product->price - $optionValue->price - ($optionValue->price / 100) * $product->special_discount);
}
}
} else {
if ($optionValue->price_prefix == '=') {
$original_price = $optionValue->price;
$option_price = $optionValue->price;
$quantity = $optionValue->quantity;
} else if ($optionValue->price_prefix == '+') {
$original_price = $product->price + $optionValue->price;
$option_price = $optionValue->price;
$quantity = $optionValue->quantity;
} else if ($optionValue->price_prefix == '-') {
$original_price = $product->price - $optionValue->price;
$option_price = $optionValue->price;
$quantity = $optionValue->quantity;
}
}
return [
'original_price' => $original_price,
'option_price' => $option_price,
'price_prefix' => $optionValue->price_prefix,
'quantity' => $optionValue->quantity
];
});
// Initialize the variables again.
$original_price = 0;
// Get the sum of the option_price
$selling_price = $result->sum('option_price');
// Loop through the $result and get the original_price
foreach ($result as $value) {
$original_price = $value['original_price'];
// $selling_price += $value['option_price'];
}
// Return to the final result
return [
'original_price' => $original_price,
'selling_price' => $selling_price,
'quantity' => $quantity
];
}
}
}
This approach ensures that each selected option's price is adjusted according to its price_prefix ('+', '-', '=') and any applicable special discounts are applied correctly.
And in my ProductController.php file i have modified and changed the method name to calculatePrice
/**
* @param int $product_option_is => $product_option_value_id
* @return Response array
*/
public function calculateOptionPrice(Request $request)
{
$product = Product::find($request->product_id);
$selectedOptions = $request->input('options', []);
if ($product) {
$finalPriceDetails = OptionCalculator::calculateFinalPrice($product, $selectedOptions);
return response()->json($finalPriceDetails);
}
return response()->json(['error' => 'Product not found'], 404);
}
My Route:
Route::post('calculate/product/option', [ProductController::class, 'calculateOptionPrice'])->name('product.calculate.options');
Thank you.
Please or to participate in this conversation.