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