shoaibsharif's avatar

How to get SUM from sum of relational field

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?

0 likes
8 replies
jlrdw's avatar

It would just be a separate query. Like,

Grand totals are separate queries, just so much easier.

Just example of a monthly report.

shoaibsharif's avatar

But what would I have to do for the query? What query should it be? The table you are showing that’s exactly what I meant but I don’t know how to sum that up before I paginate. If I paginate and get the sum I would bot be able get the correct sum, it would just give me the sum of the current page where I want the sum after the filter.

jlrdw's avatar

The above report like I said is a monthly report. Take for example vet Medical, that may have been 50 different vet bills. Okay in the background that data is summed for that month. Same for the others.

But normally Grand totals if you want to show them on every page, just make a header and show them.

However depending on usage sometimes I show a running sum like a checkbook program see last column here.

A running some, the SQL is just calculating up to the current record,

>= whatever.

But in your case I would just using the sum aggregate function get the grand totals and display in a header on each page. Or if you are referring to running sum, show that for current page, it would be just the last record in that particular pagination on that page.

In the documentation on the pagination it shows different techniques for getting where you are at in the paginator. But it's just a offset calculation.

I do have detailed monthly reports that shows every single transaction, but for monthly things like board meetings they just need summary data for the overall month.

Are you wanting grand totals to show or the total up to that current page to show.

shoaibsharif's avatar

I want to show the grand total. Showing total on current page is easy but I want the grand_total based on the filtering before pagination. Like on my snippet I want something like SUM(total_sales)

jlrdw's avatar

I suggest looking at https://www.mysqltutorial.org/mysql-sum/

Like I said it would be a separate sum query to get grand total. Hard to believe a query takes that long, are you using indexing.

How many records are in the database.

What does

$q->select(DB::raw('SUM(sell_price)'));

give you.

Edit, you don't need to query total each ineration of the paginator, just if page is equal to 1.

shoaibsharif's avatar

I think I wasn't able to explain what I am trying to say. I am trying to get SUM of SUM(sell_price). The above query works fine until I try to get the sum of SUM(sell_price) (total_sales). because I think if I do

$slips->get()->sum('total_sales')

it actually first getting the data and then suming up total_sales which I don't want it.

jlrdw's avatar
        $page = Request::input('page', '1');
        $bdate = Request::input('begindate');  if getting from input
        $edate = Request::input('enddate');

        if (!$page === '1') {
            $grandtotal = Session::get('grandtotalsum');
            
        } else {
           $grandtotal = **your grand total sum query;**
            Session::put('grandtotalsum', $grandtotal);
        }

        **Your paginated query here**

View


**A header, or span or div to show grand total**

**Your regular foreach here**

Just learn aggregate sum function.

Please or to participate in this conversation.