如何在Laravel查询构建器中通过where子句获取透视表最大日期列判断订阅状态

现有查询的逻辑偏差来自两处：

• 直接关联原透视表时，没有过滤掉同一用户下的非最新过期记录，只要存在任意一条过期的订阅记录，就会匹配筛选条件，把仍持有效订阅的用户误判为过期
• 后续的groupBy没有对expired_at做聚合计算，排序、筛选时实际取到的是分组后随机返回的第一条透视表记录的过期时间（很多数据库默认返回物理存储顺序的第一条，就会出现你观察到的校验第一条记录的现象），并非用户的最大过期时间

方案1：关联派生表子查询（适配现有查询结构，性能最优） #

不直接关联原始的users_subscriptions表，而是先通过子查询按用户分组，计算出每个用户的最大过期时间，将这个结果集作为派生表和用户表关联，再基于计算后的最大过期时间做筛选：

use Illuminate\Support\Facades\DB;
$query->select(self::columns())
    // 关联预计算的用户最新过期时间派生表
    ->joinSub(
        DB::table('users_subscriptions')
            ->select('user_id', DB::raw('MAX(expired_at) as latest_expired_at'))
            ->groupBy('user_id'),
        'user_latest_sub',
        'users.id',
        '=',
        'user_latest_sub.user_id'
    )
    // 基于最大过期时间做过期判断
    ->where('user_latest_sub.latest_expired_at', '<=', now())
    // 直接按最大过期时间排序
    ->orderBy('user_latest_sub.latest_expired_at', 'asc');

这个写法的执行逻辑和预期完全对齐：以示例数据为例，user_id=1的用户，子查询计算得到的latest_expired_at为2023-01-23，只要当前时间早于该值，该用户就不会被筛选结果命中。

方案2：关联子查询筛选（适配已定义模型关联的场景） #

如果你的User模型已经和Subscription建立了多对多关联，可以直接在查询条件里通过聚合判断实现，不需要显式join表结构：

$query->whereHas('subscriptions', function ($subQuery) {
    $subQuery->selectRaw('users_subscriptions.user_id')
        ->groupBy('users_subscriptions.user_id')
        ->havingRaw('MAX(users_subscriptions.expired_at) <= ?', [now()]);
});

• 原查询中的groupBy('users.id')在以上两种实现中都可以移除：因为子查询已经将每个用户的多条订阅记录压缩为单条结果，重复分组不仅没有意义，还可能在SQL开启only_full_group_by严格模式时触发报错
• 不要直接对原透视表的expired_at字段做范围筛选，这种写法会把“有某条记录过期”等同于“所有订阅都过期”，和实际业务逻辑冲突
• 如果后续需要补充查询用户对应的最新订阅信息，可以直接在join的子查询里增加需要返回的字段，不需要再次回查原始透视表

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