Estoy tratando de usar Eloquent para consultar una tabla de registros en la base de datos y solo recuperar esos registros donde al menos 1 registro en una columna json, que consiste en una matriz de objetos, está dentro de un rango de fechas determinado.
La columna de fechas quedaría de la siguiente manera:
[ {'date': '2021-01-01'}, {'date': '2021-01-02'}, {'date': '2021-01-03'}, ]Si paso una fecha de inicio de '2021-01-01', entonces debe buscar todos los registros donde fechas.*.fecha es igual o posterior a esta fecha de inicio.
Lo mismo se aplica a una fecha_final.
Tengo diferentes tipos de sintaxis como:
$this->where('dates->[*]->date', '<=', date($value)); $this->where('dates->*->date', '<=', date($value)); $this->where('dates.*.date', '<=', date($value));Nada parece funcionar.
¿Qué estoy haciendo mal?
Puede usar múltiples orWhereJsonContains()
$query->where(function ($query) use ($dates) { foreach ($dates as $date) { $query->orWhereJsonContains('dates', ['date' => $date]); } });$this->whereDate("json_extract('dates', '$.date')", '>', $date)->get(); Como puede ver arriba, utilicé el método json_extract() , que es la función de MySQL para extraer un campo de la columna JSON, ya que no puedo simplemente dar instrucciones elocuentes usando el operador de flecha.
$this->whereDate('dates->date', '>', $date)->get();Más bien, tengo que decirle explícitamente a Eloquent que extraiga el campo json y luego continúe.
JSON_TABLE es probablemente la única forma (buena) pura de SQL de hacer esto. Esto requiere MySQL 8.0.4 o posterior. Aquí está con Eloquent:
$this->whereExists(function($query) { $query->fromRaw( 'json_table(json_extract(`dates`, "$[*].date"), "$[*]" columns(`json_date` date path "$")) as `sub`' ) ->where('json_date','>=','2021-01-01'); })->get();Explicación:
json_extract con $[*].date como parámetro de ruta convierte los datos en una matriz regular ["2020-01-01", "2020-01-02", "2020-01-03"] )
json_table convierte los datos json en filas sql regulares:
| fecha_json |
|---|
| 2020-01-01 |
| 2020-01-02 |
| 2020-01-03 |
Las cláusulas where se aplican y filtran las filas resultantes solo a aquellas que tienen una fila json_date existente en la tabla json.
Puede pasar whereJsonContains segundo parámetro como matriz
Model::whereJsonContains('dates',['date'=>'2018-01-01'])->get();Prueba lo siguiente:
Model::whereJsonContains('dates', ['starts_at' => '2021-01-01'])->get();Del mismo modo, para fechas hasta un valor:
Model::whereJsonContains('dates', ['ends_at' => '2021-01-01'])->get();Aquí hay una versión de SQL sin formato, no conozco bien Eloquent, pero estoy bastante seguro de que puede ejecutar SQL sin formato.
Aquí está tu mesa
CREATE TABLE MyTable(id int, dates varchar(100)); INSERT INTO MyTable(id, dates) values(1, '[{"date": "2020-01-01"},{"date": "2020-01-02"},{"date": "2020-01-03"}]'); INSERT INTO MyTable(id, dates) values(2, '[{"date": "2021-01-01"},{"date": "2021-01-02"},{"date": "2021-01-03"}]'); INSERT INTO MyTable(id, dates) values(3, '[{"date": "2022-01-01"},{"date": "2022-01-02"},{"date": "2022-01-03"}]');Puedes usar este SQL
SELECT * FROM MyTable WHERE EXISTS ( SELECT * FROM JSON_TABLE( JSON_EXTRACT(dates, "$[*].date") , "$[*]" COLUMNS(jsondate date path '$' ) ) as fn WHERE jsondate >= '2021-06-01' ); La consulta principal es en realidad sencilla, pero la subconsulta es más compleja. Primero usa la función JSON_EXTRACT() de MySQL que toma JSON y una "ruta" para designar qué valores devolverá JSON: en nuestro caso, cada campo de date en nuestros objetos json, por ejemplo, para la primera fila, devolverá: ["2020-01-01", "2020-01-02", "2020-01-03"] .
Con esta matriz JSON ahora puede usar la función JSON_TABLE() para crear una tabla anónima, que también toma JSON y la definición de su nueva tabla. Ahora tenemos una tabla con una columna jsondate donde cada fila es una fecha, por lo tanto, podemos usar una cláusula WHERE simple para verificar si la fecha es mayor que la que desea.
Deberías probar whereRaw con JSON_EXTRACT -
$this->whereRaw('JSON_EXTRACT(`dates` , "$[*].date") <= ?', [date($value)]);Creo que esto funcionará en su caso, intente y hágame saber si funcionó.
Enlace de código: https://phpsandbox.io/n/calm-term-y1yo-upqak
para ejecutar el código, abra Tinker en la terminal usando
php artisan tinker app(App\Http\Controllers\UserController::class)->testingJsonExtract()Para su referencia: https://dev.mysql.com/doc/refman/5.7/en/json.html#json-paths