Laravel Eloquent - Sum multiple relation columns

user_2953308 0
Viewed 3713

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
eloquent laravel mysql
3 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();

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

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.

user_4878130
user_138760130
Related