时间戳

首页 > 解决方案 > 将 MySQL (JSON) 查询转换为 Laravel Eloquent

mysql - 将 MySQL (JSON) 查询转换为 Laravel Eloquent

问题描述

我正在尝试将 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": ""
    }
  }
}

标签： mysqllaravel

解决方案

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


$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();

推荐阅读