Laravel Eloquent - Sum multiple relation columns

I'm not an Eloquent master, and I did a lot of research and couldn't reproduce what I expected.

I have the following models:

<?php

use Illuminate\Database\Eloquent\Model;

class Invoice extends Model
{
    protected $fillable = [
        'uuid',
        'number_id'
    ];

    protected $dates = [
        'started_at',
        'ended_at'
    ];

    public $timestamps = false;


    public function contacts()
    {
        return $this->hasMany(Contact::class);
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Contact extends Model
{
    protected $fillable = [
        'incoming_messages',
        'outgoing_messages',
        'outgoing_template_messages',
    ];

    public $timestamps = false;


    public function invoice()
    {
        return $this->belongsTo(Invoice::class);
    }
}

When I search for a specific Invoice::class:

$invoice = Invoice::number($number)
    ->latest('started_at')
    ->with(['contacts'])
    ->firstOrFail();

I need it to be returned that way:

{
    "id": 1,
    "number_id": "444a13fd-9789-426e-bdfb-c3e2e83c4422",
    "incoming_messages": 10, #(sum of invoice.contacts.incoming_messages)
    "outgoing_messages": 10, #(sum of invoice.contacts.outgoing_messages)
    "outgoing_template_messages": 10, #(sum of invoice.contacts.outgoing_template_messages)
    "started_at": "2020-07-01T00:00:00.000000Z",
    "ended_at": "2020-07-31T23:59:59.000000Z"
}

I am currently doing this within InvoiceResource::class:

$incoming_messages = $this->contacts->sum('incoming_messages');
$outgoing_messages = $this->contacts->sum('outgoing_messages');
$outgoing_template_messages = $this->contacts->sum('outgoing_template_messages');

However the data volume is very large and I would like to remove the EagerLoading and do everything in a single query to decrease the impact on the database.

Any ideas on how to solve this problem using just Eloquent?

---

Update

A friend helped me with the query that I would like to convert to Eloquent:

SELECT a.number_id
, sum(incoming_messages) as 'incoming_messages' 
, sum(outgoing_messages) as 'outgoing_messages'
, sum(outgoing_template_messages) as 'outgoing_template_messages'
, a.started_at
, a.ended_at
FROM invoices a
inner join contacts b on a.id = b.invoice_id
Where number_id = '45bh1h2g14h214hg2'
Group By a.started_at , a.ended_at

Answers






I managed to make the query using only eloquent. My database performance has changed dramatically as expected.





This is the query using only eloquent:
$invoice = Invoice::number($number)
    ->selectRaw('invoices.uuid as uuid,
    invoices.number_id as number_id,
    count(*) as contacts,
    sum(incoming_messages) as incoming_messages,
    sum(outgoing_messages) as outgoing_messages,
    sum(outgoing_template_messages) as outgoing_template_messages,
    invoices.started_at,
    invoices.ended_at')
    ->join('contacts', 'contacts.invoice_id', '=', 'invoices.id')
    ->groupBy('invoices.id')
    ->latest('started_at')
    ->firstOrFail();