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

    /**
     * 获取客户的最新联系记录
     * 使用 contacted_at 字段的最大值来定义“最新”
     */
    public function latestContact()
    {
        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');
    }
}

$customers = Customer::select('customers.*', 'contacts.contacted_at as latest_contact_at')
    ->join('contacts', 'customers.id', '=', 'contacts.customer_id')
    ->orderBy('contacts.contacted_at', 'desc')
    ->get();

use Illuminate\Support\Facades\DB; // 确保引入 DB Facade

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

// 2. 将子查询连接到 Customer 模型，并进行排序
$customers = Customer::select('customers.*', 'latest_contacts.latest_contact_at')
    ->joinSub($latestContactsSubquery, 'latest_contacts', function ($join) {
        $join->on('customers.id', '=', 'latest_contacts.customer_id');
    })
    ->orderBy('latest_contacts.latest_contact_at', 'desc') // 按最新联系时间降序排序
    ->with('latestContact') // 可选：如果需要在结果中加载最新联系的完整模型
    ->get();

// 现在 $customers 集合中的每个 Customer 模型都将包含一个 latest_contact_at 属性
// 并且整个集合是按照这个最新联系时间排序的。