CODEWITHSUNDEEP
phplaraveleloquent
user3118789
user3118789

Reputation: 609

Laravel Eloquent sum on realtions

I try to sum the original_total column of order_items table from orders table. Relationship: Order has many order_items

....
$total[ $from->format('Y-m') ] = Order::whereBetween(
    'created_at', [$cloneFrom->startOfMonth()->toDateTimeString(), $cloneFrom->endOfMonth()->toDateTimeString()]
)->withCount(['orderItems as original_total' => function ($query) {
    $query->select(DB::raw('sum(original_total)'));
}])->sum('original_total');
....

I got this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'original_total' in 'field list' (SQL: select sum(`original_total`) as aggregate from `orders` where `created_at` between 2019-01-01 00:00:00 and 2019-01-31 23:59:59)

Then, I use get before sum, it worked but it generated too many queries and slow down my application. Is there any way to calculate the sum without use get method?

Upvotes: 0

Views: 130

Answers (1)

mrhn
mrhn

Reputation: 18916

You are trying to use subselect results in another select, which is a limitation in SQL. This approach could work, but i think there is something simpler. I would just join the relation in.

Order::whereBetween(
    'created_at', [
        $cloneFrom->startOfMonth()->toDateTimeString(),
        $cloneFrom->endOfMonth()->toDateTimeString(),
    ])
    ->leftJoin('order_items', 'orders.id', 'order_items.order_id')
    ->sum('order_items.original_total');

Without the sum the query would look something similar to this, missing some columns for easier formatting.

order.id | order_items.id | order_items.original_total

       1                1                           10
       1                2                           10
       1                3                           10
       2                4                            5
       2                5                            5

If you where doing ordinary queries you would have duplicate orders in your return, but since you only care about gathering all the totals, you can utilize this approach performance should be really good as it joins on foreign keys.

Upvotes: 1

Related Questions