How to combine multiple sum() in 1 query?

Hi,

While building a simple invoicing system, I have some total stats on top of my index view. Stuff like 'total open', 'total paid', 'total late', etc.

For this I use the eloquent sum() method. But I'm doing it multiple times, for each value. I'm looking to do it in 1 request. Any advice?

This is my current render function in livewire component:

public function render()
    {
        return view('livewire.invoice.index', [
            'invoices' => Invoice::with('customer')
                ->orderBy('invoice_date', 'desc')
                ->paginate($this->limit),
            'total_tax_incl' => Invoice::whereIn('status', ['open', 'paid', 'late'])->whereYear('invoice_date', date('Y'))->sum('total_incl'),
            'total_tax_excl' => Invoice::whereIn('status', ['open', 'paid', 'late'])->whereYear('invoice_date', date('Y'))->sum('total_excl'),
            'total_open_tax_incl' => Invoice::where('status', 'open')->sum('total_incl'),
            'total_open_tax_excl' => Invoice::where('status', 'open')->sum('total_excl'),
            'total_late_tax_incl' => Invoice::where('status', 'late')->sum('total_incl'),
            'total_late_tax_excl' => Invoice::where('status', 'late')->sum('total_excl'),
            'total_pending_tax_incl' => Invoice::where('status', 'pending')->sum('total_incl'),
            'total_pending_tax_excl' => Invoice::where('status', 'pending')->sum('total_excl'),
            'total_concept_tax_incl' => Invoice::where('status', 'concept')->sum('total_incl'),
            'total_concept_tax_excl' => Invoice::where('status', 'concept')->sum('total_excl'),
        ]);
    }

These are the resulting queries in mySql:

Invoice	SELECT sum(`total_incl`) AS aggregate FROM `invoices` WHERE `STATUS` in ('open', 'paid', 'late') and YEAR(`invoice_date`) = '2023';
Invoice	SELECT sum(`total_excl`) AS aggregate FROM `invoices` WHERE `STATUS` in ('open', 'paid', 'late') and YEAR(`invoice_date`) = '2023';
Invoice	SELECT sum(`total_incl`) AS aggregate FROM `invoices` WHERE `STATUS` = 'open';
Invoice	SELECT sum(`total_excl`) AS aggregate FROM `invoices` WHERE `STATUS` = 'open';
Invoice	SELECT sum(`total_incl`) AS aggregate FROM `invoices` WHERE `STATUS` = 'late';
Invoice	SELECT sum(`total_excl`) AS aggregate FROM `invoices` WHERE `STATUS` = 'late';
Invoice	SELECT sum(`total_incl`) AS aggregate FROM `invoices` WHERE `STATUS` = 'pending';
Invoice	SELECT sum(`total_excl`) AS aggregate FROM `invoices` WHERE `STATUS` = 'pending';
Invoice	SELECT sum(`total_incl`) AS aggregate FROM `invoices` WHERE `STATUS` = 'concept';
Invoice	SELECT sum(`total_excl`) AS aggregate FROM `invoices` WHERE `STATUS` = 'concept';

How to combine it into 1 query? Thanks

LaryAI

3 years ago

Level 58

You can use the selectRaw method to select multiple sums in a single query. Here's an example:

public function render()
{
    $totals = Invoice::selectRaw("
            SUM(CASE WHEN status IN ('open', 'paid', 'late') AND YEAR(invoice_date) = YEAR(NOW()) THEN total_incl ELSE 0 END) AS total_tax_incl,
            SUM(CASE WHEN status IN ('open', 'paid', 'late') AND YEAR(invoice_date) = YEAR(NOW()) THEN total_excl ELSE 0 END) AS total_tax_excl,
            SUM(CASE WHEN status = 'open' THEN total_incl ELSE 0 END) AS total_open_tax_incl,
            SUM(CASE WHEN status = 'open' THEN total_excl ELSE 0 END) AS total_open_tax_excl,
            SUM(CASE WHEN status = 'late' THEN total_incl ELSE 0 END) AS total_late_tax_incl,
            SUM(CASE WHEN status = 'late' THEN total_excl ELSE 0 END) AS total_late_tax_excl,
            SUM(CASE WHEN status = 'pending' THEN total_incl ELSE 0 END) AS total_pending_tax_incl,
            SUM(CASE WHEN status = 'pending' THEN total_excl ELSE 0 END) AS total_pending_tax_excl,
            SUM(CASE WHEN status = 'concept' THEN total_incl ELSE 0 END) AS total_concept_tax_incl,
            SUM(CASE WHEN status = 'concept' THEN total_excl ELSE 0 END) AS total_concept_tax_excl
        ")->first();

    return view('livewire.invoice.index', [
        'invoices' => Invoice::with('customer')
            ->orderBy('invoice_date', 'desc')
            ->paginate($this->limit),
        'totals' => $totals,
    ]);
}

This will generate a single query that selects all the sums you need. The CASE statements are used to conditionally sum the values based on the status and invoice date. The first() method is used to retrieve the first row of the result set, which contains the sums. You can then pass the sums to your view as a variable.

1 like

tvbz

3 years ago

Level 4

Wauw! This Larry Quickdraw A.I. is awesome! :)

This selectRaw is just a bit slower then the collection of seperate queries though. It runs at 600ms, while the collection was finished at 450ms. Any human input on this?

Thanks

Please or to participate in this conversation.