Laravel 10中如何获取各客户的最新tgl_pengajuan数据？

目标需求 #

我使用Laravel 10开发项目，需要在PenawaranController.php中获取每个客户的最新tgl_pengajuan，期望得到如下格式的结果：

customer_id | name  | email           | latest_penawaran_by_cust
----------------------------------------------------------------
1           | david | david@gmail.com | 2023-12-20 01:00:00
2           | alan  | alan1@gmail.com | null

当前问题 #

执行dd($penawaran[0]->customer);后，得到的数据结构不符合预期，出现了嵌套重复的Customer模型：

App\Models\Customer {
  #connection: "mysql"
  #table: "customers"
  .. // 其他属性
  #attributes: array:9 [
    "id" => 1
    "name" => "david"
    "email" => "david@gmail.com"
    .. // 其他字段
    "latestPenawaranByCust" => Illuminate\Database\Eloquent\Collection {
      "items: array:1 {
        0 => App\Models\Customer {
          #connection: "mysql"
          #table: "customers"
          .. // 其他属性
          #attributes: array:8 [
            "id" => 1
            "name" => "david"
            "email" => "david@gmail.com"
            .. // 其余和上面一致，但无lastPenawaranByCust

表结构（简化版） #

• customers表：

id | name  | email
--------------------------------
1  | david | david@gmail.com
2  | alan  | alan1@gmail.com

• barangs表：

id | name           | stok | harga
-----------------------------------
1  | laptop 14 inch | 100  | 150
2  | USB cable      | 50   | 10

• penawarans表：

id | customer_id | tgl_pengajuan       | updated_at
----------------------------------------------------
1  | 1           | 2023-12-19 00:00:00 | 2023-12-15 05:00:00
2  | 1           | 2023-12-20 01:00:00 | 2023-12-17 07:00:00

• detail_penawaran表：

id | penawaran_id | barang_id | qty | harga
--------------------------------------------
1  | 1            | 1         | 11  | 200
2  | 1            | 2         | 22  | 20
3  | 2            | 1         | 12  | 210

现有代码 #

模型文件 #

Models/Penawaran.php

public function detail_penawaran()
{
    return $this->belongsToMany(DetailPenawaran::class);
}

public function customer()
{
    return $this->belongsTo(Customer::class);
}

Models/DetailPenawaran.php

public function penawaran()
{
    return $this->hasOne(Penawaran::class);
}

public function barang()
{
    return $this->hasMany(Barang::class);
}

Models/Customer.php

public function penawarans()
{
    return $this->hasMany(Penawaran::class, 'customer_id');
}

public function latestPenawaranByCust()
{
    return $this->hasOne(Penawaran::class, 'customer_id')->latest('updated_at');
}

控制器文件 PenawaranController.php #

$penawaran = Penawaran::where('id', '=', $id)->get();

if ($penawaran) {
    $tglPenawaranByCust = Customer::where('id', '=', $penawaran[0]->customer->id)
        ->with(['latestPenawaranByCust' => function($query) {
            $query->whereNotNull('updated_at');
        }])
        ->orderBy('nama', 'ASC')
        ->get();
    $penawaran[0]['customer']['latestPenawaranByCust'] = $tglPenawaranByCust;
}
dd($penawaran[0]->customer);

解决方案 #

1. 修正模型关联错误 #

DetailPenawaran的关联逻辑有误，需调整为：

// Models/DetailPenawaran.php
public function penawaran()
{
    return $this->belongsTo(Penawaran::class);
}

public function barang()
{
    return $this->belongsTo(Barang::class);
}

Penawaran与DetailPenawaran的关联应为一对多：

// Models/Penawaran.php
public function detail_penawaran()
{
    return $this->hasMany(DetailPenawaran::class);
}

2. 修正关联排序字段 #

要获取最新的tgl_pengajuan，需按该字段排序，而非updated_at：

// Models/Customer.php
public function latestPenawaranByCust()
{
    return $this->hasOne(Penawaran::class, 'customer_id')->latest('tgl_pengajuan');
}

3. 简化控制器逻辑 #

无需单独查询Customer再赋值，直接通过预加载获取关联数据：

// PenawaranController.php
// 按id查询单条报价，用first()替代get()
$penawaran = Penawaran::with(['customer.latestPenawaranByCust'])
    ->where('id', $id)
    ->first();

// 如果需要获取所有客户的最新报价，直接查询Customer并格式化输出
$customersWithLatestPenawaran = Customer::with(['latestPenawaranByCust'])
    ->select('id', 'name', 'email')
    ->orderBy('name', 'ASC')
    ->get()
    ->map(function ($customer) {
        return [
            'customer_id' => $customer->id,
            'name' => $customer->name,
            'email' => $customer->email,
            'latest_penawaran_by_cust' => $customer->latestPenawaranByCust?->tgl_pengajuan
        ];
    });

dd($customersWithLatestPenawaran);

问题根源说明 #

之前的控制器代码中，$tglPenawaranByCust = Customer::...->get()返回的是Customer集合，将其赋值给latestPenawaranByCust导致嵌套了Customer模型。正确做法是直接预加载latestPenawaranByCust关联（它是Penawaran模型），再取出其中的tgl_pengajuan字段。

内容的提问来源于stack exchange，提问作者dapidmini