It would just be a separate query. Like,
Grand totals are separate queries, just so much easier.
Just example of a monthly report.
i have two models Sell and Slip where:
class Slip extends Model
{
protected $guarded = [];
protected $dates = ['bill_date'];
public function orders()
{
return $this->hasMany(Sell::class);
}
}
class Sell extends Model
{
protected $guarded = [];
function product()
{
return $this->belongsTo('App\Product');
}
function slip()
{
return $this->belongsTo('App\Slip');
}
}
in migration I have column for slips table:
Schema::create('slips', function (Blueprint $table) {
$table->increments('id');
$table->string('customer_name');
$table->string('customer_age');
$table->string('customer_gender');
$table->dateTime('bill_date')->nullable();
$table->integer('paid')->nullable();
$table->integer('discount')->nullable();
$table->integer('doctor_id')->nullable();
$table->timestamps();
});
and for sell:
Schema::create('sells', function (Blueprint $table) {
$table->increments('id');
$table->string('product_id');
$table->integer('sell_price');
$table->integer('doctor_id')->nullable();
$table->integer('slip_id')->nullable();
$table->timestamps();
});
I have this query:
$slips = Slip::with(['doctor']);
$slips = $slips->withCount(['orders as total_sales' => function ($q) {
$q->select(DB::raw('SUM(sell_price)'));
}]);
$slips = $slips->whereBetween('bill_date', [$from, $to])->whereHas('doctor', function ($q) {
$q->where('id', request()->query('doctor'));
});
What I want is to get is the sum of all sell_price from the databse after I filter. I can get sum of sell_price(total_sales) for each slip, but I want sum of total_sales before I do pagination.
I know a way I to do that:
$slips->get()->sum('total_sales')
But if I do this with 10,000 data, it takes 5 min to load which I don't want that. I would like to get the sum from database. Is there any workaround to do that?
Please or to participate in this conversation.