Laravel 8中如何获取Dns关联的各类型最新DnsCheck记录？

我正在开发一个Laravel 8前后端应用，包含三个模型：

• Domain：存储网站域名（如example.com）
• Dns：通过belongsTo关联Domain，存储DNS检查元数据
• DnsCheck：通过belongsTo关联Dns，每条记录对应一种DNS类型（如a/aaaa）的检查结果，checked_at字段标记检查时间

每个域名对应多条不同类型的DnsCheck记录（最多15条），Dns的interval字段指定检查频率。现在需要获取各类型的最新DnsCheck记录，返回可遍历的数组，但latestOfMany()只能返回单条结果，请问如何实现？

现有模型代码 #

Dns模型

<?php

namespace App\Models;

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

class Dns extends Model
{
    use HasFactory;

    /**
     * Indicates if the model's ID is auto-incrementing.
     *
     * @var bool
     */
    public $incrementing = false;

    /**
    * The table associated with the model.
    *
    * @var string
    */
    protected $table = 'dns';

    /**
     * The attributes that should be cast.
     *
     * @var array
     */
    protected $casts = [
        'last_notified_at' => 'datetime',
        'last_checked_at' => 'datetime',
        'disabled_at' => 'datetime',

        // record types that we can monitor
        'a_record_is_enabled' => 'boolean',
        'aaaa_record_is_enabled' => 'boolean',
        'caa_record_is_enabled' => 'boolean',
        'cname_record_is_enabled' => 'boolean',
        'mx_record_is_enabled' => 'boolean',
        'ns_record_is_enabled' => 'boolean',
        'soa_record_is_enabled' => 'boolean',
        'srv_record_is_enabled' => 'boolean',
        'txt_record_is_enabled' => 'boolean',
        'ptr_record_is_enabled' => 'boolean'
    ];

    /**
     * Get the dns checks that this dns has
     */
    public function dns_checks()
    {
        return $this->hasMany(DnsCheck::class);
    }

    /**
     * Get the latest dns check
     */
    public function latest_dns_check()
    {
        return $this->hasOne(DnsCheck::class)->latestOfMany();
    }

    /**
     * Get the domain that owns this dns
     */
    public function domain()
    {
        return $this->belongsTo(Domain::class);
    }

    /**
     * The "booted" method of the model.
     *
     * @return void
     */
    protected static function booted()
    {
        static::deleted(function ($model) {
            $model->dns_checks()->delete();
        });
    }
}

DnsCheck模型

<?php

namespace App\Models;

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

class DnsCheck extends Model
{
    use HasFactory;

    /**
    * The table associated with the model.
    *
    * @var string
    */
    protected $table = 'dns_checks';

    /**
     * Get the dns that owns this dns check
     */
    public function certificate()
    {
        return $this->belongsTo(Dns::class);
    }
}

1. 修正DnsCheck模型关联错误 #

首先注意到DnsCheck模型中的关联方法命名错误，原方法certificate应改为dns，否则关联无法正常工作：

// DnsCheck模型中修正关联
public function dns()
{
    return $this->belongsTo(Dns::class);
}

2. 通过Eloquent关联获取各类型最新记录 #

在Dns模型中添加一个新的关联方法，通过子查询筛选出每个type分组下的最新记录：

// 在Dns模型中添加
public function latest_dns_checks_by_type()
{
    return $this->hasMany(DnsCheck::class)
        ->whereIn('id', function ($query) {
            $query->selectRaw('MAX(id)')
                ->from('dns_checks')
                ->whereColumn('dns_id', 'dns.id')
                ->groupBy('type');
        })
        ->orderBy('type');
}

使用时直接预加载关联即可得到可遍历的集合：

// 获取指定Dns及其各类型最新检查记录
$dns = Dns::with('latest_dns_checks_by_type')->find($dnsId);
$latestChecks = $dns->latest_dns_checks_by_type; // 可直接循环遍历

如果更倾向于用checked_at字段判断最新，可修改子查询逻辑：

public function latest_dns_checks_by_type()
{
    return $this->hasMany(DnsCheck::class)
        ->whereRaw('(dns_id, type, checked_at) IN (
            SELECT dns_id, type, MAX(checked_at)
            FROM dns_checks
            WHERE dns_id = ?
            GROUP BY dns_id, type
        )', [$this->id]);
}

3. 直接用查询构造器查询 #

如果不需要模型关联，可直接通过查询构造器获取指定Dns的各类型最新记录：

use Illuminate\Support\Facades\DB;

$latestChecks = DnsCheck::query()
    ->select('dns_checks.*')
    ->joinSub(
        DnsCheck::query()
            ->select('dns_id', 'type', DB::raw('MAX(checked_at) as max_checked_at'))
            ->where('dns_id', $dnsId)
            ->groupBy('dns_id', 'type'),
        'latest_checks',
        function ($join) {
            $join->on('dns_checks.dns_id', '=', 'latest_checks.dns_id')
                ->on('dns_checks.type', '=', 'latest_checks.type')
                ->on('dns_checks.checked_at', '=', 'latest_checks.max_checked_at');
        }
    )
    ->get();

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