Laravel：利用子查询实现按“一对多中之一”关系模型排序

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\Relations\HasOne;

class Customer extends Model
{
    use HasFactory;

    /**
     * 获取客户的所有联系记录
     */
    public function contacts(): HasMany
    {
        return $this->hasMany(Contact::class);
    }

    /**
     * 获取客户的最新联系记录
     * 使用 Has One Of Many 关系
     */
    public function latestContact(): HasOne
    {
        return $this->hasOne(Contact::class)->ofMany('contacted_at', 'max')->withDefault();
    }
}

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

class Contact extends Model
{
    use HasFactory;

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

    /**
     * 获取此联系记录所属的客户
     */
    public function customer(): BelongsTo
    {
        return $this->belongsTo(Customer::class);
    }
}

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

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

use Illuminate\Support\Facades\DB;

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

// 2. 将子查询作为临时表与 customers 表进行 JOIN
$customers = Customer::select('customers.*', 'latest_contacts.latest_contact_at')
                     ->joinSub($latestContactsSubquery, 'latest_contacts', function ($join) {
                         $join->on('customers.id', '=', 'latest_contacts.customer_id');
                     })
                     // 3. 根据最新联系时间进行排序
                     ->orderBy('latest_contacts.latest_contact_at', 'desc')
                     ->get();

// 此时 $customers 集合中的每个 Customer 模型都包含一个 'latest_contact_at' 属性
// 并且已经按照这个属性进行了排序。
foreach ($customers as $customer) {
    echo "客户 ID: " . $customer->id . ", 最新联系时间: " . $customer->latest_contact_at . "\n";
}