Question: How do I do sum() or other aggregate functions on columns that is of type money

[rabol]

Hi

Do you have an example on how to doe e.g. a sum() of records that have Money as a column type ?

[rabol]

I have transactions like this

Illuminate\Database\Eloquent\Collection {#1971 ▼
  #items: array:2 [▼
    0 => 
App\Models
\
InvoiceItem {#1838 ▼
      #connection: "mysql"
      #table: "invoice_items"
      #primaryKey: "id"
      #keyType: "int"
      +incrementing: true
      #with: []
      #withCount: []
      +preventsLazyLoading: false
      #perPage: 15
      +exists: true
      +wasRecentlyCreated: false
      #escapeWhenCastingToString: false
      #attributes: array:12 [▼
        "id" => "01j064g5pqshtdp7ny82vzxwd2"
        "qty" => 2.0
        "currency_code_id" => "01j064g5hafk22f81ha6gt5zwz"
        "invoice_id" => "01j064g5pg7qfyeaw1avg4zq85"
        "product_id" => "01j064g5nybm4xgedwzwn9s870"
        "tax_code_id" => "01j064g5n40s7ffgmtksm9cw2c"
        "discount_amount" => "0"
        "line_amount" => "{"amount":20000,"currency":"EUR"}"
        "price" => "{"amount":10000,"currency":"EUR"}"
        "tax_amount" => "0"
        "created_at" => "2024-06-12 11:49:29"
        "updated_at" => "2024-06-12 11:49:29"
      ]
      #original: array:12 [▶]
      #changes: []
      #casts: array:5 [▶]
      #classCastCache: []
      #attributeCastCache: []
      #dateFormat: null
      #appends: []
      #dispatchesEvents: []
      #observables: []
      #relations: []
      #touches: []
      +timestamps: true
      +usesUniqueIds: true
      #hidden: []
      #visible: []
      #fillable: array:9 [▶]
      #guarded: array:1 [▶]
    }

and if I do like this:

        $invoiceTotal = InvoiceItem::query()
            ->where('invoice_id', '01j064g5pg7qfyeaw1avg4zq85)
            ->sum('line_amount');

the result is 0

[endrik-exe]

You cannot sum a json string.

this become tricky. you need to get all the record first and then sum it via laravel collection.

        $invoiceTotal = InvoiceItem::query()
            ->where('invoice_id', '01j064g5pg7qfyeaw1avg4zq85)
            ->get()
            ->sum(fn($item) => $item->line_amount->amount);

this will be heavy if you have many records, it is recommended to cache your sum in parent reords.

You can also use json_extract functions from mysql

$invoiceTotal = InvoiceItem::query()
            ->select(DB::raw("json_extract(line_amount, '$.amount')"))
            ->where('invoice_id', '01j064g5pg7qfyeaw1avg4zq85)