如何基于HasMany关系带条件创建动态HasOne关联？

模型定义 #

Document.php #

class Document extends Model
{
    public function approvals(): HasMany
    {
        return $this->hasMany(Approval::class);
    }

    public function nextApproval(): HasOne
    {
        return $this->hasOne(Approval::class)->where('status', 'pending')->orderBy('created_at', 'asc');
    }
}

Approval.php #

class Approval extends Model
{     
    public function document(): BelongsTo
    {
        return $this->belongsTo(Document::class);
    }
}

Approval表示例数据 #

需求 #

在文档列表页仅展示当前登录用户的职位处于下一个待审批环节的文档：

• 示例数据中，职位ID为2的用户不应看到该文档，因为当前第一个待审批项属于职位ID为1
• 只有当职位ID为1的审批状态改为done后，职位ID为2的用户才能看到该文档

尝试过的无效方法 #

1. 调整关联定义：

// 尝试1
$this->approvals()->orderBy('created_at', 'asc')->where('status', 'pending')->one();
// 尝试2
$this->hasOne(Approval::class)->where('status', 'pending')->oldest('created_at')->limit(1);

2. 调整查询语句：

$documents = Document::query()
    ->whereHas('nextApproval', fn ($q) => $q->where('position_id', auth()->user()->position_id))
    ->paginate();

$documents = Document::query()
    ->whereExists(function ($query) {
        $query->from('approvals')
            ->whereColumn('approvals.document_id', 'documents.id')
            ->where('status', 'pending')
            ->where('position_id', auth()->user()->position_id)
            ->orderBy('created_at', 'asc');
    })
->paginate();

3. 使用latestOfMany/oldestOfMany：仅取最新/最旧记录，无法结合自定义筛选条件。

解决方案 #

方法1：使用关联子查询筛选 #

$userPosition = auth()->user()->position_id;

$documents = Document::query()
    ->whereExists(function ($query) use ($userPosition) {
        // 子查询获取当前文档的第一个待审批项ID
        $firstPending = Approval::query()
            ->select('id')
            ->whereColumn('document_id', 'documents.id')
            ->where('status', 'pending')
            ->orderBy('created_at', 'asc')
            ->limit(1);
        
        $query->from('approvals')
            ->whereColumn('approvals.id', $firstPending)
            ->where('approvals.position_id', $userPosition);
    })
    ->paginate();

方法2：修正nextApproval关联（推荐） #

在Document.php中更新关联定义，使用ofMany实现带条件的HasOne关联：

public function nextApproval(): HasOne
{
    return $this->hasOne(Approval::class)
        ->where('status', 'pending')
        ->ofMany(
            ['created_at' => 'min'], // 取最早创建的记录
            fn ($query) => $query->where('status', 'pending') // 额外约束确保是待审批状态
        );
}

然后直接用whereHas查询：

$documents = Document::query()
    ->whereHas('nextApproval', fn ($q) => $q->where('position_id', auth()->user()->position_id))
    ->paginate();

方法3：原生SQL子查询 #

$userPosition = auth()->user()->position_id;

$documents = Document::query()
    ->whereRaw("
        (SELECT position_id FROM approvals 
         WHERE document_id = documents.id AND status = 'pending' 
         ORDER BY created_at ASC LIMIT 1) = ?
    ", [$userPosition])
    ->paginate();

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