Laravel 中按“Has One Of Many”关联模型排序的最佳实践

// app/Models/Customer.php
namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Customer extends Model
{
    use HasFactory;

    public function contacts()
    {
        return $this->hasMany(Contact::class);
    }

    public function latestContact()
    {
        // 定义“Has One Of Many”关系，获取每个客户最新联系时间（contacted_at）的联系记录
        return $this->hasOne(Contact::class)->ofMany('contacted_at', 'max')->withDefault();
    }
}

// app/Models/Contact.php
namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Contact extends Model
{
    use HasFactory, SoftDeletes;

    protected $casts = [
        'contacted_at' => 'datetime',
    ];

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

// database/migrations/..._create_contacts_table.php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateContactsTable extends Migration
{
    public function up()
    {
        Schema::create('contacts', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            $table->softDeletes();

            $table->foreignId('customer_id')->constrained()->onDelete('cascade');
            $table->string('type');
            $table->dateTime('contacted_at');
        });
    }

    public function down()
    {
        Schema::dropIfExists('contacts');
    }
}

use Illuminate\Support\Facades\DB;
use App\Models\Customer;
use App\Models\Contact;

// 1. 构建子查询：找出每个客户的最新联系时间
$latestContactsSubquery = Contact::select('customer_id', DB::raw('MAX(contacted_at) as latest_contact'))
                                 ->groupBy('customer_id');

// 2. 将子查询联接到 Customer 模型上，并根据最新联系时间排序
$customers = Customer::select('customers.*', 'latest_contacts.latest_contact')
                     ->joinSub($latestContactsSubquery, 'latest_contacts', function ($join) {
                         $join->on('customers.id', '=', 'latest_contacts.customer_id');
                     })
                     ->orderBy('latest_contacts.latest_contact', 'desc') // 降序排列，最新联系的客户在前
                     ->get();

// $customers 现在包含了按最新联系时间排序的客户列表，每个客户都带有一个 latest_contact 字段
foreach ($customers as $customer) {
    echo "客户ID: {$customer->id}, 最新联系时间: {$customer->latest_contact}\n";
    // 也可以通过预加载来访问 latestContact 关系
    // $customer->load('latestContact');
    // echo "最新联系人类型: " . $customer->latestContact->type . "\n";
}