Laravel控制器添加whereDate查询后无法获取数据求助

你添加whereDate后查不到数据，核心问题是SQL逻辑运算符的优先级规则——AND的优先级高于OR，导致实际执行的查询逻辑和你预想的完全不一样。

原代码添加whereDate后，生成的SQL大致结构是：

WHERE 
    EXISTS (...) -- whereHas对应的关联查询条件
    AND school_id = ? 
    OR school_id IS NULL 
    AND DATE(deadline_viewing) >= NOW()

由于AND优先级更高，这段SQL会被数据库解析成：

WHERE 
    EXISTS (...) 
    AND (school_id = ? AND DATE(deadline_viewing) >= NOW())
    OR (school_id IS NULL AND DATE(deadline_viewing) >= NOW())

这和你期望的「(school_id匹配指定值 或 school_id为空) 并且 deadline_viewing未过期」逻辑完全不符，自然查不到符合条件的数据。

把school_id的两个条件用闭包包裹，让它们作为一个逻辑整体，再和whereDate、whereHas形成AND关系：

public function getPublicPost($school_id){
    $public_feeds = app(AllyFeedRepository::class)->query()
        ->whereHas('user_detail', function($q){
            $q->whereNull('deleted_at');
        })
        ->with('user_detail:id,name,profile_photo_path,deleted_at','userInstance:id,user_id,role_id','userInstance.role:id,name','announcement:id,description,file_path,type,deleted_at')
        ->where(function($query) use ($school_id) {
            $query->whereSchoolId($school_id)
                  ->orWhere('school_id', null);
        })
        ->whereDate('deadline_viewing', '>=', now())
        ->orderBy('created_at','DESC')
        ->paginate(15);
    return $public_feeds;
}

修正后生成的SQL逻辑就完全符合预期了：

WHERE 
    EXISTS (...)
    AND (school_id = ? OR school_id IS NULL)
    AND DATE(deadline_viewing) >= NOW()

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