So I have orders model and table.
it contains integer fields that I want to calculate to find daily, monthly and yearly earnings. My current solution works fine but as shown in the image below it runs 3 queries to find all of them. is there any way better to calculate the earning without running the query 3 times?
I have also tried getting all the orders into one variable then using laravel's collection methods to calculate earnings, that also works but it's slower than my current solution.
public function index()
{
$dailyEarning = Order::whereDate('created_at', Carbon::today())->get()->sum(function ($order) {
return (($order->cost - $order->product->original_cost) * $order->quantity);
});
$monthlyEarning = Order::whereBetween('created_at', [
Carbon::today()->startOfMonth(),
Carbon::today()->endOfMonth(),
])->get()->sum(function ($order) {
return (($order->cost - $order->product->original_cost) * $order->quantity);
});
$yearlyEarning = Order::whereBetween('created_at', [
Carbon::today()->startOfYear(),
Carbon::today()->endOfYear(),
])->get()->sum(function ($order) {
return (($order->cost - $order->product->original_cost) * $order->quantity);
});
return view('admin.home',[
'dailyEarning' => $dailyEarning,
'monthlyEarning' => $monthlyEarning,
'yearlyEarning' => $yearlyEarning,
]);
}
Orders table schema:
public function up()
{
Schema::create('orders', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('product_id');
$table->foreign('product_id')->references('id')->on('products')->onDelete('restrict');
$table->unsignedBigInteger('invoice_id');
$table->foreign('invoice_id')->references('id')->on('invoices')->onDelete('cascade');
$table->double('cost');
$table->double('quantity');
$table->timestamps();
});
}
Products table schema:
public function up()
{
Schema::create('products', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('code');
$table->string('brand');
$table->double('quantity')->default(0);
$table->double('original_cost')->default(0);
$table->double('cost')->default(0);
$table->string('photo')->default('no-image');
$table->timestamps();
});
}
