Laravel Eloquent - Sum multiple relation columns

Question

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

If I understood your question correctly, then you should not remove the EagerLoading as it currently thats the thing that saves you a lot of queries. I think you should have only two at the moment: one gets you the invoice and one gets you the contacts of the invoice. Doing $this->contacts->sum('incoming_messages'); does not make additional queries. — thefallen
– thefallen, Commented Jul 6, 2020 at 7:40
@thefallen The problem is: At some point I can have more than 100,000 contacts per invoice. As far as I know EagerLoading will return to me all contacts data, and in the end I only need the sum of the specified columns. Mysql can sum this without returning all contacts, it would save me some resources... — Caio Kawasaki
– Caio Kawasaki, Commented Jul 6, 2020 at 7:44
Here is a "hack" which exploits the withCount() method: laravel-eloquent-query-with-sum-of-related-table. But personally I would rather use Invoice::hydrateRaw('<your_raw_query>', <bindings>). — Paul Spiegel
– Paul Spiegel, Commented Jul 11, 2020 at 21:06

Caio Kawasaki · Accepted Answer · 2020-07-16 02:55:29Z

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();

Update

I got into a problem where my query didn't return invoices that didn't have any contacts, I had to make a small change.

The join() method generates an inner join, the inner join does not include rows from table A that have no elements in table B.

To overcome this situation, a left join must be used. However, if the left join has no values in table B, it was returning my sums as null.

To solve this I simply used the MySql function ifnull().

This is the final query:

$invoices = Invoice::number($number)
    ->selectRaw('invoices.uuid as uuid,
    invoices.number_id as number_id,
    ifnull(count(contacts.id), 0) as contacts,
    ifnull(sum(incoming_messages), 0) as incoming_messages,
    ifnull(sum(outgoing_messages), 0) as outgoing_messages,
    ifnull(sum(outgoing_template_messages), 0) as outgoing_template_messages,
    invoices.started_at,
    invoices.ended_at')
    ->leftJoin('contacts', 'contacts.invoice_id', '=', 'invoices.id')
    ->groupBy('invoices.id')
    ->firstOrFail();

apokryfos · Accepted Answer · 2020-07-06 08:43:25Z

0

You could try something like:

$incoming_messages = $this->contacts()
      ->selectRaw('SUM(incoming_messages) as incoming_messages, SUM(outgoing_messages) as outgoing_messages, SUM(outgoing_template_messages) as outgoing_template_messages')
      ->get();

You might be able integrate this in your original query as follows:

$invoice = Invoice::number($number)
    ->latest('started_at')
    ->with(['contacts' => function ($q) {
         $q->selectRaw('SUM(incoming_messages) as incoming_messages, SUM(outgoing_messages) as outgoing_messages, SUM(outgoing_template_messages) as outgoing_template_messages');
    }])
    ->firstOrFail();

Then you'd access the values as $invoice->contacts->incoming_messages etc. To get this to work when selecting more than one invoice you may need to add groupBy('invoice_id') if Invoice-contacts is a 1 to many relationship (i.e. there's only one invoice for a contact) if it's many to many you might end up needing to join the pivot table.

edited Jul 6, 2020 at 8:43

answered Jul 6, 2020 at 8:19

apokryfos

40.9k11 gold badges85 silver badges128 bronze badges

3 Comments

Caio Kawasaki Over a year ago

How to put this inside the main query? Invoice::number($number)

apokryfos Over a year ago

Try the update. I've not tested it myself but it might work

Caio Kawasaki Over a year ago

I tried something similar before creating this topic. I tried your query and the contacts attribute comes like this: []

Ben Lehr · Accepted Answer · 2020-07-06 08:38:12Z

-1

You can also try to use a Mutator for this: https://laravel.com/docs/7.x/eloquent-mutators#defining-a-mutator

public function incomingMessages()
{
    return $this->contacts->sum('incoming_messages');
}

And in your Resource you will just call:

$this->incomingMessages()

answered Jul 6, 2020 at 8:38

Ben Lehr

1

1 Comment

Jan 'splite' K. Over a year ago

I am afraid that this will not solve anything - there is around 100k+ contacts. If you eager load them, they will materialize in RAM, just for three numbers per invoice. If you dont, they will be lazy loaded in your mutation, which is even worse (all of them load but not in one step)

Collectives™ on Stack Overflow

Laravel Eloquent - Sum multiple relation columns

Update

3 Answers 3

Update

Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Update

3 Answers 3

Update

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related