何处Json包含Laravel 5.6不起作用

Question

$rosters = EventRosters::where('event_id', $event_id)
->whereJsonContains('players', $user_id)
->whereNull('deleted_at')
->get();

上面雄辩的查询似乎只有在"players"json数组中有一个项目时才有效。

存储在数据库中的数据如下所示：[1]与["1","2"]

JsonContains只在数据库中看到[1]时工作，而在看到["1","2"]时不工作，这有什么原因吗？

我对拉拉维尔很陌生，一直在为这件事而挣扎。

Answer 1

数据类型必须匹配：

// [1, 2]
->whereJsonContains('players', 1)   // Works.
->whereJsonContains('players', '1') // Doesn't work.
// ["1", "2"]
->whereJsonContains('players', '1') // Works.
->whereJsonContains('players', 1)   // Doesn't work.

Answer 2

文档有点直接

https://laravel.com/docs/5.6/queries#json-其中条款

$rosters = EventRosters::where('event_id', $event_id)
->whereJsonContains(['players', [1,2]])
//->whereNull('deleted_at') Unless you setup a scope at the model's bootup, 
//Eloquent won't fetch soft deleted records
->get();

根据json列中的内容(如果id(，用players->id替换players

Answer 3

您可以使用orWhere 解决问题

$id = "1" // or = 1
Model::whereJsonContains('ids', [$id])
->orWhere(function (Builder $q) use ($model) {
$q->whereJsonContains('ids', [(string)$id]);
});

Answer 4

FoodItemsName::leftJoin('food_items','food_items.fk_food_id','food_item_name.id')
->where('food_item_name.name', 'like', '%' . $food_item . '%')
->where('food_items.meal_time_id',$meal_time_id)
->whereJsonContains('food_item_name.fk_diet_group_id',[(int)$user->fk_diet_group_id])
->select('food_items.id', 'food_item_name.name', 'food_items.calorie_count as kcal', 'food_item_name.icon as image')
->distinct('food_item_name.name')
->paginate(50);