将MySQL (JSON)查询转换为Laravel查询

Question

我正在尝试将MySQL查询(其中包含mysql-json函数)转换为Laravel Eloquent查询。下面是我的Mysql查询。我必须在laravel版本5.3中构建这个雄辩的查询。尝试并坚持使用json函数

MYSQL查询：

SELECT  id FROM `house_json`  WHERE JSON_CONTAINS( JSON_EXTRACT(construction_json, "$.house.room[*].window"), '"Removed"' ) AND form_id=5 AND action_date BETWEEN '2020-05-25' AND '2020-05-27'

雄辩的查询：

$data_query = HouseData::select('house_json.id as ID')
->where('house_json.form_id',5)
->whereBetween('house_json.action_date', ['2020-05-25', '2020-05-27']) ->whereRaw('json_contains("json_extract("house_json.construction_json", "$.house.room[*].window")", '"Removed"')')
->get();

示例JSON (存储在名为-construction_json的表字段中)：

{
  "visit_date": "2020-05-25",
  "operative_name": "Peter",
  "tenant_name": "Denny",
  "id": "433",
  "house": {
    "room": [
      {
        "appliance_id": "72329",
        "landlord": "Yes",
        "location_id": 4,
        "location_name": "Back Hall",
        "status": "Removed",
        "comment": "",
        "reason": "Landlord denied"
      },
      {
        "appliance_id": "72330",
        "landlord": "Yes",
        "location_id": 4,
        "location_name": "Kitchen",
        "status": "Completed",
        "comment": "",
        "reason": ""
      }
    ],
    "other_detail": {
      "pipework_done": "Yes",
      "paperwork_done": "No",
      "general_comments": ""
    }
  }
}

Answer 1

试试这个，我之前解决了同样的问题，如下所示。

$data_query = HouseData::select('house_json.id as ID')->
where('house_json.form_id', 5)->
whereBetween('house_json.action_date', ['2020-05-25', '2020-05-27'])->
whereRaw('JSON_CONTAINS(
    JSON_EXTRACT(
        house_json.construction_json,
        "$.house.room[*].window"),
    "Removed")')->
get();