英文:
OrderBy date in json column of table in laravel
问题
表格中的一列是array_object。该列的输入是JSON格式的数据。在这个JSON中，有两个变量，name和date。现在我想根据从今天开始最接近的日期对这个表格的行进行排序。需要注意的是年份始终是固定的。实际上，我想按最接近的月份和日期进行排序。
假设今天是2023-06-11。我想指定最接近今天的日期，而不考虑年份。我的输入和输出如下：
输入 = 2023-04-22, 2023-06-21, 2023-10-11, 2023-05-29
输出 = 2023-06-21, 2023-06-21, 2023-04-22, 2023-05-29
我编写的代码如下：
$ooo = ContentListItem::orderByRaw("ABS(DATEDIFF(STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(array_object, '$.date')), '%m-%d'), '" . Carbon::now()->format('m-d') . "'))")
->get();
这段代码的输出如下，是错误的：
2023-04-22, 2023-06-21, 2023-05-29, 2023-10-11
当我将代码修改为以下内容时，我的输出不会重复，它只会对今年进行排序，不考虑以后的年份，但结果是正确的直到今年年底。
$ooo = ContentListItem::where('array_object->date', '>=', now()->format('Y-m-d'))
->orderBy('array_object->date', 'asc')
->get();
输出 = 2023-06-21, 2023-06-21
我该如何修改我的代码以获得所需的输出？
英文:
One of the columns in my table is array_object. The input of this column is json. In this json there are two variables name and date. Now I want to sort the rows of this table based on the closest date from today onwards. It is important to note that the year is always fixed. Actually, I want to sort by the nearest month and day.
Given that today is 2023-06-11. I want to specify the dates closest to today regardless of the year. My inputs and outputs are as follows
input = 2023-04-22, 2023-06-21, 2023-10-11, 2023-05-29
output respectively = 2023-06-21, 2023-06-21, 2023-04-22, 2023-05-29
The code I wrote is as follows:
$ooo = ContentListItem::orderByRaw("ABS(DATEDIFF(STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(array_object, '$.date')), '\"%m-%d\"'), '" . Carbon::now()->format('m-d') . "'))")
->get();
The output of this code is as follows, which is wrong :
2023-04-22, 2023-06-21, 2023-05-29 ,2023-10-11,
When I modified the code as below, my output is not repeated and it only performs the sorting for this year and does not consider the data for the following years, but the result is correct until the end of this year.
$ooo = ContentListItem::where('array_object->date', '>=', now()->format('Y-m-d'))
->orderBy('array_object->date', 'asc')
->get();
output = 2023-06-21, 2023-06-21
How can I modify my code to get the desired output?
答案1
得分: 2
在回答您的问题之前，我建议您在这种情况下使用一个单独的表，其中包含包含日期、名称和任何其他数据的多重形态关系。
这将在性能、组织和将来的修改和扩展方面更好。
如果您想要在搜索中使用JSON，您可以为要搜索的列创建一个别名。
Model::selectRaw('JSON_EXTRACT(data, "$.date") AS dataDate')->orderByRaw('CAST(dataDate AS DATE) ASC');
有关更多详细信息，请参考以下链接：
英文:
Before answering your question, my advice to you is that in this case you should use a separate table in which there is a multiple morph relationship that contains the date, name, and any other data.
It will be better in performance, organization, and easier to modify and expand in the future
In the event that you want to use JSON in the search, you can make an alias for the column you want to search with.
Model::selectRaw('JSON_EXTRACT(data, "$.date") AS dataDate')->orderByRaw('CAST(dataDate AS DATE) ASC');
for more details.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论