Laravel中按JSON列内日期（忽略年份）排序查询结果的问题

问题分析 #

你需要基于JSON字段array_object->date中的日期，忽略年份仅比较月日，按距离今日（2023-06-11）最近的顺序排序。之前的代码存在两个核心问题：

1. 第一段代码的日期解析格式错误，导致DATEDIFF计算完全失效；
2. 第二段代码仅筛选本年之后的日期，无法处理跨年的近距场景（比如12月31日距离次年1月1日的实际距离很近）。

正确实现方案 #

核心思路是：将日期转换为一年中的第几天（DAYOFYEAR），计算该值与今日的年中天数的最小距离（考虑跨年循环的情况），再按这个距离升序排序。

Laravel Eloquent 代码

use Carbon\Carbon;
use App\Models\ContentListItem;

$currentDoy = Carbon::now()->dayOfYear; // 今日的年中天数，2023-06-11对应162

$items = ContentListItem::selectRaw("
    *,
    DAYOFYEAR(STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(array_object, '$.date')), '%Y-%m-%d')) AS target_doy
")
->orderByRaw("
    LEAST(
        ABS(target_doy - ?),
        365 - ABS(target_doy - ?)
    ) ASC
", [$currentDoy, $currentDoy])
->get();

代码说明 #

1. 日期解析：先用JSON_EXTRACT+JSON_UNQUOTE取出JSON中的日期字符串，再用STR_TO_DATE解析为MySQL日期类型，最后通过DAYOFYEAR获取该日期在一年中的第几天；
2. 最小距离计算：
   • ABS(target_doy - currentDoy)：计算同一年中目标日期与今日的天数差绝对值；
   • 365 - ABS(target_doy - currentDoy)：计算跨年场景下的最小距离（比如今日是6-11，目标日期12-31的实际近距是到次年6-11的天数）；
   • LEAST(...)：取两个距离中的较小值，确保得到真正的最近距离；
3. 参数绑定：使用?和参数数组传递currentDoy，避免SQL注入风险。

验证你的示例数据 #

针对输入日期：2023-04-22（112天）、2023-06-21（172天）、2023-10-11（284天）、2023-05-29（149天）：

• 与今日（162天）的最小距离分别为：50、10、122、13；
• 排序结果应为：2023-06-21、2023-05-29、2023-04-22、2023-10-11，完全符合距离由近到远的逻辑。

处理闰年（可选） #

如果你的数据中包含2月29日，可将目标日期的年份固定为闰年（如2020）来计算DAYOFYEAR，避免闰年日期的计算偏差：

$items = ContentListItem::selectRaw("
    *,
    DAYOFYEAR(STR_TO_DATE(CONCAT('2020-', DATE_FORMAT(STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(array_object, '$.date')), '%Y-%m-%d'), '%m-%d')), '%Y-%m-%d')) AS target_doy
")
->orderByRaw("
    LEAST(
        ABS(target_doy - ?),
        366 - ABS(target_doy - ?)
    ) ASC
", [$currentDoy, $currentDoy])
->get();

内容的提问来源于stack exchange，提问作者Mohsen Gorzin