Laravel中如何对数据库array类型的price字段执行查询过滤？

你存储的是键值对结构的JSON/数组字段，无法直接用原生whereBetween直接匹配所有档位，可按你使用的数据库类型选择对应实现：

1. MySQL（5.7及以上版本，支持JSON字段） #

需要手动提取每个档位的值做区间校验：

SELECT * FROM 你的表名
WHERE 
    JSON_UNQUOTE(JSON_EXTRACT(price, '$.basic')) BETWEEN 1 AND 19
    AND JSON_UNQUOTE(JSON_EXTRACT(price, '$.standard')) BETWEEN 1 AND 19
    AND JSON_UNQUOTE(JSON_EXTRACT(price, '$.premium')) BETWEEN 1 AND 19;

如果用Laravel框架，查询构造器写法如下：

$min = 1;
$max = 19;
$results = DB::table('你的表名')
    ->whereRaw("JSON_UNQUOTE(JSON_EXTRACT(price, '$.basic')) BETWEEN ? AND ?", [$min, $max])
    ->whereRaw("JSON_UNQUOTE(JSON_EXTRACT(price, '$.standard')) BETWEEN ? AND ?", [$min, $max])
    ->whereRaw("JSON_UNQUOTE(JSON_EXTRACT(price, '$.premium')) BETWEEN ? AND ?", [$min, $max])
    ->get();

2. PostgreSQL #

PostgreSQL对JSON字段支持更灵活，可以不用写死档位键名，自动校验所有档位的值：

SELECT * FROM 你的表名 t
WHERE NOT EXISTS (
    SELECT 1 FROM jsonb_each_text(t.price) AS j(key, value)
    WHERE value::integer NOT BETWEEN 1 AND 19
);

以上写法逻辑是只要存在任意一个档位的价格不在指定区间，就排除对应数据，完全匹配所有档位都符合区间的校验需求

对应Laravel查询构造器写法：

$min = 1;
$max = 19;
$results = DB::table('你的表名')
    ->whereRaw("NOT EXISTS (
        SELECT 1 FROM jsonb_each_text(price) AS j(key, value)
        WHERE value::integer NOT BETWEEN ? AND ?
    )", [$min, $max])
    ->get();

3. Laravel场景封装自定义调用方法 #

如果想实现类似whereBetween的简洁调用，可以给查询构造器注册宏：

// 在AppServiceProvider的boot方法中注册宏
Illuminate\Database\Query\Builder::macro('whereAllPriceBetween', function ($min, $max) {
    return $this->whereRaw("NOT EXISTS (
        SELECT 1 FROM jsonb_each_text(price) AS j(key, value)
        WHERE value::integer NOT BETWEEN ? AND ?
    )", [$min, $max]);
});

// 业务代码直接调用
$results = DB::table('你的表名')->whereAllPriceBetween(1,19)->get();

提示：如果你的price字段目前是用text类型存储序列化数组，建议改成JSON/JSONB类型存储，查询效率更高，也可以加对应索引优化查询速度。

内容的提问来源于stack exchange，提问作者Ghiath Al-Khatib