如何在Eloquent中使用has()关联数据库字段作为阈值查询？

我在Bottle与InventoryItem之间建立了如下关联：

InventoryItem.php: #

public function bottles(): HasMany
{
   return $this->hasMany(InventoryItemBottle::class);
}

我需要查询bottles数量大于自身JSONB类型info字段中high_level_warning阈值的InventoryItem，当前查询代码如下（标注出问题行）：

->when(
    $filters['filter'] === 'show_above_max_threshold',
    fn (Builder $query): Builder => $query->where(function (Builder $query): Builder {
        return $query->whereColumn('info->quantity', '>', 'info->high_level_warning');
    })
    ->orWhere(function (Builder $query): Builder {
        return $query->has('bottles', '>', 'info->high_level_warning'); // 此处遇到问题
    })
)

尝试用has()方法实现，但不知道如何将数据库中high_level_warning作为参数传入，求解决方法或思路。

has()方法无法直接引用当前模型的字段作为比较值，可通过以下几种方式实现需求：

方法一：子查询统计关联数量并比较 #

通过子查询算出每个InventoryItem对应的bottles数量，再和info->high_level_warning做字段间比较：

->when(
    $filters['filter'] === 'show_above_max_threshold',
    fn (Builder $query): Builder => $query->where(function (Builder $query): Builder {
        return $query->whereColumn('info->quantity', '>', 'info->high_level_warning');
    })
    ->orWhere(function (Builder $query): Builder {
        // 子查询统计每个InventoryItem的bottles数量
        $subQuery = InventoryItemBottle::selectRaw('count(*)')
            ->whereColumn('inventory_item_id', 'inventory_items.id');
        
        // PostgreSQL语法，MySQL替换为JSON_UNQUOTE(JSON_EXTRACT(info, '$.high_level_warning'))
        return $query->whereRaw('? > (info->>\'high_level_warning\')', [$subQuery]);
    })
)

方法二：Exists子查询判断 #

用exists子查询直接在数据库层面判断关联数量是否超过阈值，性能更优：

->when(
    $filters['filter'] === 'show_above_max_threshold',
    fn (Builder $query): Builder => $query->where(function (Builder $query): Builder {
        return $query->whereColumn('info->quantity', '>', 'info->high_level_warning');
    })
    ->orWhereExists(function (Builder $subQuery) {
        $subQuery->select(DB::raw(1))
            ->from('inventory_item_bottles')
            ->whereColumn('inventory_item_id', 'inventory_items.id')
            ->groupBy('inventory_item_id')
            ->havingRaw('count(*) > (inventory_items.info->>\'high_level_warning\')');
    })
)

方法三：withCount结合Having筛选 #

先预加载关联数量字段，再通过havingRaw做字段比较：

->when(
    $filters['filter'] === 'show_above_max_threshold',
    fn (Builder $query): Builder => $query->withCount('bottles')
        ->where(function (Builder $query): Builder {
            return $query->whereColumn('info->quantity', '>', 'info->high_level_warning');
        })
        ->orHavingRaw('bottles_count > (info->>\'high_level_warning\')')
)

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