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 并在单个查询中完成所有操作，以减少对数据库的影响。

有什么想法可以只使用 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

- Caio Kawasaki

---

如果我正确理解了你的问题，那么你不应该删除EagerLoading，因为它目前可以节省很多查询。我认为你现在应该只有两个：一个获取发票，一个获取发票的联系人。执行$this->contacts->sum('incoming_messages');不会产生额外的查询。 - thefallen

@thefallen 问题是：有时一张发票上可能会有超过10万个联系人。据我所知，EagerLoading会将所有联系人数据返回给我，而最终我只需要指定列的总和。Mysql可以在不返回所有联系人的情况下进行求和，这将节省一些资源... - Caio Kawasaki

这里有一个“hack”，它利用了 withCount() 方法：laravel-eloquent-query-with-sum-of-related-table。但是个人而言，我更喜欢使用 Invoice::hydrateRaw('<your_raw_query>', <bindings>)。 - Paul Spiegel

Answer 1

我成功地只使用Eloquent构建了查询语句。正如预期的那样，我的数据库性能发生了巨大变化。

这是只使用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 B 中没有元素的 table A 行。

为了解决这种情况，必须使用 left join。然而，如果 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();

- Caio Kawasaki

Answer 2

你可以尝试这样做：

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

你也许可以将它集成到你的原始查询中，如下所示：

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

然后，您可以像这样访问值：$invoice->contacts->incoming_messages等。如果选择多个发票时要使其工作，您可能需要添加groupBy('invoice_id')，如果Invoice-contacts是一对多关系（即每个联系人只有一个发票），则可能需要加入中间表。

- apokryfos

---

如何将此代码放入主查询中？Invoice::number($number) - Caio Kawasaki

尝试更新。我自己没有测试过，但可能会起作用。 - apokryfos

在创建这个主题之前，我尝试过类似的东西。我尝试了你的查询，contacts 属性是这样的：[]。 - Caio Kawasaki

Answer 3

你也可以尝试使用一个Mutator来实现这个功能： https://laravel.com/docs/7.x/eloquent-mutators#defining-a-mutator

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

在你的资源中，你只需要调用：

$this->incomingMessages()

- Ben Lehr