Laravel 10中JSON列WHERE子句无法按类型过滤通知的原因？

问题描述 #

在Laravel 10应用中使用数据库驱动实现通知功能，通知的data字段包含type键，值为server或internal。尝试通过以下代码筛选单一类型的未读通知时，无法得到任何结果：

auth()->user()->unreadNotifications->where('data->type', 'server');

但使用数据库查询构造器却能返回预期数据：

DB::table('notifications')
 ->where('notifiable_type', '=', 'App\Models\User')
 ->where('read_at','=', null)
 ->where('notifiable_id','=', auth()->user()->user_id)
 ->where('data->type', '=', 'server')
 ->get();

通过Laravel DebugBar观察发现，只有查询构造器会生成json_unquote(json_extract(data, '$."type"')) = 'server'这样的JSON查询语句，而前者不会。

原因解析 #

• 集合操作与数据库查询的本质区别：auth()->user()->unreadNotifications返回的是Eloquent集合对象，此时所有未读通知已经从数据库加载到内存中。调用->where('data->type', 'server')是在内存中对集合元素进行过滤，而非向数据库发起新查询。
• 集合where方法不支持JSON路径语法：集合的where方法只会把data->type当作模型的一个字面属性名去匹配，但通知模型并没有名为data->type的属性——模型的data属性是JSON字符串或已解析的数组/对象，集合无法识别->这种JSON路径写法，因此匹配不到任何结果。
• 查询构造器的JSON语法支持：使用DB::table()或关联关系的查询构造器（带括号的unreadNotifications()）时，Laravel会识别data->type这种JSON路径格式，自动转换为对应数据库的JSON提取语法，在数据库层面完成筛选，所以能返回正确结果。

正确写法 #

要实现数据库层面的JSON筛选，需调用关联关系的查询构造器（注意括号）：

auth()->user()->unreadNotifications()->where('data->type', 'server')->get();

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