在 laravel 中查找未来 7 天内过生日的客户

Question

我需要在 Laravel 中获取未来 7 天内过生日的客户列表。我使用以下查询。该查询显示也通过了生日。

 $customerslist=Customers::WhereRaw('DAYOFYEAR(curdate()) <= DAYOFYEAR(date_of_birth) AND DAYOFYEAR(curdate()) + 7 >=  dayofyear(date_of_birth)' )->OrderBy(DB::raw("DAYOFYEAR(date_of_birth)"),'ASC')->get();

我使用下面列出的查询，但缺少一些客户

缺少客户

Answer 1

我发现使用 DAYOFYEAR 方法的原始查询非常优雅，因为它消除了不同出生年份的所有问题。然而，它不会很好地处理闰年。我发现 BETWEEN 和 DATE_FORMAT 的组合会产生正确的结果。它确实需要检查来处理多年来的边缘。以下是如何在模型范围内实现此功能的示例：public function scopeBirthDayBetween ($query, Carbon $from, Carbon $till){&nbsp; &nbsp; $fromMonthDay = $from->format('m-d');&nbsp; &nbsp; $tillMonthDay = $till->format('m-d');&nbsp; &nbsp; if ($fromMonthDay <= $tillMonthDay) {&nbsp; &nbsp; &nbsp; &nbsp; //normal search within the one year&nbsp; &nbsp; &nbsp; &nbsp; $query->whereRaw("DATE_FORMAT(birthdate, '%m-%d') BETWEEN '{$fromMonthDay}' AND '{$tillMonthDay}'");&nbsp; &nbsp; } else {&nbsp; &nbsp; &nbsp; &nbsp; //we are overlapping a year, search at end and beginning of year&nbsp; &nbsp; &nbsp; &nbsp; $query->where(function ($query) use ($fromMonthDay, $tillMonthDay) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $query->whereRaw("DATE_FORMAT(birthdate, '%m-%d') BETWEEN '{$fromMonthDay}' AND '12-31'")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->orWhereRaw("DATE_FORMAT(birthdate, '%m-%d') BETWEEN '01-01' AND '{$tillMonthDay}'");&nbsp; &nbsp; &nbsp; &nbsp; });&nbsp; &nbsp; }}用法：$users = User::birthDayBetween(Carbon::now(), Carbon::now()->addWeek())->get()要在出生日期而不是出生日期添加排序，更多原始查询和 DATE_FORMAT 可以提供帮助。$users = User::query()&nbsp; &nbsp; ->birthDayBetween($from, $till)&nbsp; &nbsp; ->orderByRaw("DATE_FORMAT(birth_date,'%m%d')")&nbsp; &nbsp; ->orderByRaw("DATE_FORMAT(birth_date,'%y') desc")&nbsp; &nbsp; ->orderBy('first_name')&nbsp; &nbsp; ->get();注意：查看年份边缘时，订购将关闭，因为将首先订购明年年初生日的用户。我选择在前端解决这个问题。

Answer 2

您的模型有一个date-of-birth字段，您将它与当前日期（可能是出生日期之后的许多年）进行比较。解决方案：为了可读性和处理年结转问题，您应该：使用 Carbon 计算日期和日期查找用户的下一个/即将到来的生日请记住，Ymd 格式通常意味着时间为 00:00:00。$now = Carbon::now();$customers = Customer::all();// find next birthday for each customerforeach ($customers as $customer) {&nbsp; &nbsp; $curyear_bd = Carbon::createFromFormat('Y-m-d', $customer->date_of_birth)->setYear($now->year);&nbsp; &nbsp; $now > $curyear_bd->endOfDay() ? $next_bd = $curyear_bd->addYear(1) : $next_bd = $curyear_bd;&nbsp; &nbsp; if ($now <= $next_bd->startOfDay() && $next_bd <= Carbon::now()->addDay(7)->endOfDay())&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; print "customer $customer->name has birthday coming up\n";&nbsp; &nbsp; }}MySQL解决方案您可以像这样使用 SQL 查询：select * from customers whereif (CURDATE() >= date_add(date_of_birth, interval&nbsp; TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE())&nbsp; &nbsp; year),&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;date_add(date_of_birth, interval (TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE())+1) year),&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;date_add(date_of_birth, interval&nbsp; TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE())&nbsp; &nbsp; year))betweencurdate()anddate_add(curdate(), interval 7 day);但我会警惕时区（服务器、客户端等）。这可能需要它自己的SO问题......