mysql - Laravel Eloquent - 对多个关系列求和

Question

我不是Eloquent大师，我做了很多研究，无法重现我的预期。

我有以下型号：

<?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);
    }
}

当我搜索特定的Invoice::class：

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

我需要以这种方式返回它：

{
    "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"
}

我目前正在这样做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');

但是数据量非常大，我想删除EagerLoading并在单个查询中执行所有操作以减少对数据库的影响。

关于如何使用 just 解决此问题的任何想法Eloquent？

---

更新

一位朋友帮助我完成了我想转换为的查询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

标签： mysqllaraveleloquent

Answer 1

我设法只使用雄辩的方式进行查询。正如预期的那样，我的数据库性能发生了巨大变化。

这是只使用 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();

---

更新

我遇到了一个问题，我的查询没有返回没有任何联系人的发票，我不得不做一个小改动。

该join()方法生成一个inner join，inner join不包括table A其中没有元素的行table B。

为了克服这种情况，left join必须使用 a。但是，如果left join中没有值table B，它会将我的总和返回为null。

为了解决这个问题，我简单地使用了 MySql 函数ifnull()。

这是最终的查询：

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