Laravel如何通过Eloquent查询中间表字段获取待提醒多频率周年纪念日

疑问1：如何引用pivot表的字段 #

首先需要在多对多关联定义时声明需要读取的pivot字段，以Contact模型的关联为例：

public function anniversaries()
{
    return $this->belongsToMany(Anniversary::class)
        ->withPivot('date', 'frequency'); // 声明需要读取的pivot表字段
}

引用字段分两种场景：

• 关联查询后读取属性：直接通过$anniversary->pivot->date即可访问对应pivot字段
• SQL条件中引用：直接写pivot表全名加字段名即可，示例中pivot表名为anniversary_contact，所以字段写作anniversary_contact.date

疑问2：查询逻辑简化方案 #

完全不需要写大量orWhereBetween语句，这里提供两种成熟方案，可根据业务数据量选择：

方案1：PHP层面处理（推荐绝大多数场景使用） #

如果单用户的纪念日总数不超过1000条，直接取出所有数据后用Carbon做日期判断，代码可读性和维护性远高于复杂SQL，性能完全满足需求：

$thresholdDays = 3; // 未来3天内的纪念日
$now = now();
$upcomingAnniversaries = collect();

// 预加载关联数据，避免N+1问题
$contacts = auth()->user()->contacts()
    ->with('anniversaries')
    ->get();

$contacts->each(function ($contact) use ($now, $thresholdDays, $upcomingAnniversaries) {
    $contact->anniversaries->each(function ($anniversary) use ($now, $thresholdDays, $contact, $upcomingAnniversaries) {
        $baseDate = Carbon::parse($anniversary->pivot->date);
        $frequency = $anniversary->pivot->frequency;
        
        // 计算今年的对应触发日期
        $nextOccurrence = match($frequency) {
            'monthly' => $baseDate->setYear($now->year)->setMonth($now->month),
            'quarterly' => $baseDate->setYear($now->year)->setMonth(ceil($now->month/3)*3 - 2 + ($baseDate->month -1) %3),
            'semi-annually' => $baseDate->setYear($now->year)->setMonth($now->month <=6 ? $baseDate->month : $baseDate->month +6),
            'annually' => $baseDate->setYear($now->year),
            default => null
        };

        // 平年2月29日兼容处理
        if ($nextOccurrence?->format('m-d') === '02-29' && !$nextOccurrence->isLeapYear()) {
            $nextOccurrence->subDay();
        }

        // 今年触发日已过的话，加一个对应周期
        if ($nextOccurrence && $nextOccurrence->isPast()) {
            $nextOccurrence = match($frequency) {
                'monthly' => $nextOccurrence->addMonth(),
                'quarterly' => $nextOccurrence->addQuarter(),
                'semi-annually' => $nextOccurrence->addMonths(6),
                'annually' => $nextOccurrence->addYear(),
                default => null
            };
        }

        // 判断是否在未来阈值天数内
        if ($nextOccurrence && $nextOccurrence->diffInDays($now, false) >=0 && $nextOccurrence->diffInDays($now, false) <= $thresholdDays) {
            $upcomingAnniversaries->push([
                'contact' => $contact,
                'anniversary' => $anniversary,
                'next_occurrence' => $nextOccurrence
            ]);
        }
    });
});

方案2：数据库层面查询（适用大数据量场景） #

如果单用户纪念日数据过万，可以用SQL的CASE表达式直接计算下次触发日期，无需大量OR条件：

$thresholdDays = 3;
$startDate = now()->toDateString();
$endDate = now()->addDays($thresholdDays)->toDateString();

$upcomingAnniversaries = auth()->user()->contacts()
    ->join('anniversary_contact', 'contacts.id', '=', 'anniversary_contact.contact_id')
    ->join('anniversaries', 'anniversaries.id', '=', 'anniversary_contact.anniversary_id')
    ->where('anniversaries.user_id', auth()->id())
    ->selectRaw("
        contacts.*, anniversaries.*,
        anniversary_contact.date as pivot_date,
        anniversary_contact.frequency as pivot_frequency,
        CASE 
            WHEN 
                -- 计算今年触发日
                CASE anniversary_contact.frequency
                    WHEN 'monthly' THEN STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(CURDATE()), '-', DAY(anniversary_contact.date)), '%Y-%m-%d')
                    WHEN 'quarterly' THEN STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', CEIL(MONTH(CURDATE())/3)*3 - 2 + (MONTH(anniversary_contact.date) -1) %3, '-', DAY(anniversary_contact.date)), '%Y-%m-%d')
                    WHEN 'semi-annually' THEN STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', IF(MONTH(CURDATE()) <=6, MONTH(anniversary_contact.date), MONTH(anniversary_contact.date) +6), '-', DAY(anniversary_contact.date)), '%Y-%m-%d')
                    WHEN 'annually' THEN STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(anniversary_contact.date), '-', DAY(anniversary_contact.date)), '%Y-%m-%d')
                END < CURDATE()
            THEN 
                -- 今年已过就加一个周期
                CASE anniversary_contact.frequency
                    WHEN 'monthly' THEN DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(CURDATE()), '-', DAY(anniversary_contact.date)), '%Y-%m-%d'), INTERVAL 1 MONTH)
                    WHEN 'quarterly' THEN DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', CEIL(MONTH(CURDATE())/3)*3 - 2 + (MONTH(anniversary_contact.date) -1) %3, '-', DAY(anniversary_contact.date)), '%Y-%m-%d'), INTERVAL 3 MONTH)
                    WHEN 'semi-annually' THEN DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', IF(MONTH(CURDATE()) <=6, MONTH(anniversary_contact.date), MONTH(anniversary_contact.date) +6), '-', DAY(anniversary_contact.date)), '%Y-%m-%d'), INTERVAL 6 MONTH)
                    WHEN 'annually' THEN DATE_ADD(STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(anniversary_contact.date), '-', DAY(anniversary_contact.date)), '%Y-%m-%d'), INTERVAL 1 YEAR)
                END
            ELSE 
                CASE anniversary_contact.frequency
                    WHEN 'monthly' THEN STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(CURDATE()), '-', DAY(anniversary_contact.date)), '%Y-%m-%d')
                    WHEN 'quarterly' THEN STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', CEIL(MONTH(CURDATE())/3)*3 - 2 + (MONTH(anniversary_contact.date) -1) %3, '-', DAY(anniversary_contact.date)), '%Y-%m-%d')
                    WHEN 'semi-annually' THEN STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', IF(MONTH(CURDATE()) <=6, MONTH(anniversary_contact.date), MONTH(anniversary_contact.date) +6), '-', DAY(anniversary_contact.date)), '%Y-%m-%d')
                    WHEN 'annually' THEN STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(anniversary_contact.date), '-', DAY(anniversary_contact.date)), '%Y-%m-%d')
                END
        END as next_occurrence
    ")
    ->whereBetween('next_occurrence', [$startDate, $endDate])
    ->get();

注：该方案如需兼容平年2月29日，可在SQL中加对应日期判断逻辑即可。

选型建议 #

优先选择PHP层面处理的方案，除了性能足够之外，调试、修改逻辑的成本远低于复杂SQL，除非你明确遇到性能瓶颈再考虑数据库方案。

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