json column as dateColumn not working
Opened this issue · 0 comments
saifulapm commented
Hi, I am getting error when I am trying to use json column as dateColumn.
$trendQuery = Trend::query($chartQuery)
->between(
start: $startDate,
end: $endDate,
)
->dateColumn('custom_data->assigned_at');
Here custom_data is a json field. Getting this error:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'assigned_at, '%Y-%m-%d') as date, count(*) as aggregate ' at line 2
select
date_format(custom_data->assigned_at, '%Y-%m-%d') as date,
count(*) as aggregate
from `items` where `user_id` = 296 and json_unquote(json_extract(`custom_data`, '$."assigned_at"')) between 2024-08-01 00:00:00 and 2024-08-31 23:59:59 group by `date` order by `date` asc