减少前面的sql查询次数

Question

    orders
        id - integer
        client_id - integer
     
    clients
        id - integer
        name - string
     
    accounts
        id - integer
        client_id - integer
        amount - integer

控制器

$orders = Order::with(['transaction', 'client', 'delivery', 'address'])
  ->latest()->paginate(50);
return view('admin.order.index', compact('orders'));

前端

      <td class="text-center">
        <strong>{{$item->client->invoice}}</strong>
      </td>

客户端模型

  public function getInvoiceAttribute()
  {
    return $this->account()->sum('amount');
  }

我不知道如何使用有很多通过。 或者说遇到这种情况怎么解决 我不需要前台账户，但我需要金额总和

Answer 1

我这样做了，

之后写入帐户总和 -> 金额

Schema::table('clients', function (Blueprint $table) {
          $table->integer('invoice')->default(0);
        });

添加了发票列，以在添加 AccountObserver

class AccountObserver
{
  public function creating(Account $account)
  {
    $account->client()->increment('invoice',$account->amount);
  }

  public function updating(Account $account)
  {
    $account->client()->increment('invoice',$account->amount);
  }
} 

控制器

$orders = Order::with(['transaction', 'client', 'delivery', 'address'])
  ->latest()->paginate(50);
return view('admin.order.index', compact('orders'));

视图

<td class="text-center">
    <strong >{{$item->client->invoice}}</strong>
</td>

Answer 2

$this->account()->sum('amount');

这将创建一个 SQL 查询。如果你在 foreach 循环中调用它，它将执行 N 次查询。

您可以急切地加载总和。

$orders = Order::query()
    ->with([
        'transaction',
        'client' => fn ($client) => $client->withSum('accounts as invoice', 'amount'), // or function ($client) { $client->withSum('accounts as invoice', 'amount'); }, 
        'delivery',
        'address'
    ])
    ->latest()
    ->paginate(50);

return view('admin.order.index', compact('orders'));

@foreach ($orders as $item)
  ...
  <td class="text-center">
    <strong>{{ $item->client->invoice }}</strong>
  </td>
  ...
@endforeach

从 Client 模型中删除 getInvoiceAttribute 方法