Level 3
If you're using eloquent the method you want is orderBy()
Model::orderBy('data->date');
Mar 21, 2022
8
Level 7
Order by date in json column
order_by('data->date') is sorted by data->date like string instead of by date.
the values data->date are like: "2022-01-05"
Level 7
@kmoore This is what I try but it arranges it by string and not by date
For example 2022-05-11 will be after 2022-11-11
Level 102
@simcha so orderByDesc?
Level 7
@Sinnbeck That's not the point, the problem is that it treats the entry as text and not as a date
Level 102
@simcha Not sure I understand the problem then? Year-month-day. I assume its sorted in the same way as in php?
$arr = [
'2022-08-11',
'2022-01-22',
'2022-05-01',
'2022-11-11',
];
sort($arr);
//returns
[
"2022-01-22",
"2022-05-01",
"2022-08-11",
"2022-11-11"
]
But could you perhaps move the data to another table ? Then you have full control over it.
Level 3
@simcha If you need it cast as a date before the orderBy() you could write a raw query to do it in the sql. Or you could write an accessor and use the collection sort() method.
Raw query for example (not tested):
Model::selectRaw('JSON_EXTRACT(data, "$.date") AS dataDate')->orderByRaw('CAST(dataDate AS DATE) ASC');
Level 3
Level 75
@simcha json is text. I would store data in database then ORDER BY date.
Please or to participate in this conversation.