Laravel whereHas查询失效求助：筛选关联Objective为空字段的Plan

Hey there, let's dig into why your whereHas isn't filtering your Plan records correctly and get this sorted out!

First, let's double-check the basics: your association setup. You mentioned Plan has a one-to-many relationship with Objective, and Objective has a one-to-one with Plan—I think that's a typo, and you mean Objective belongs to a single Plan. Make sure your model relationships are defined properly:

Plan Model:

public function objectives()
{
    return $this->hasMany(Objective::class); // Ensure the foreign key (like `plan_id`) exists on the `objectives` table
}

Objective Model:

public function plan()
{
    return $this->belongsTo(Plan::class);
}

If the foreign key isn't the default plan_id, you'll need to specify it in both relationships (e.g., hasMany(Objective::class, 'custom_plan_id')).

Scenario 1: You want Plans with at least one Objective where entity_id and quarter_id are null #

Your original code logic is correct here, but there might be small issues causing it to fail. Let's adjust it slightly, and make sure we're targeting the right table fields:

$plans = Plan::with(['objectives', 'objectives.keyResults'])
    ->where('companyKey', Auth::user()->companyKey) // Move this condition first for readability (doesn't affect functionality)
    ->whereHas('objectives', function($query) {
        $query->whereNull('entity_id')
              ->whereNull('quarter_id');
    })
    ->get();

If this still returns all Plans, try using a raw join instead (sometimes subqueries like whereHas can have unexpected behavior with complex setups):

$plans = Plan::with(['objectives', 'objectives.keyResults'])
    ->select('plans.*')
    ->join('objectives', 'plans.id', '=', 'objectives.plan_id')
    ->whereNull('objectives.entity_id')
    ->whereNull('objectives.quarter_id')
    ->where('plans.companyKey', Auth::user()->companyKey)
    ->distinct() // Prevents duplicate Plan records if multiple objectives match
    ->get();

Scenario 2: You want Plans where all associated Objectives have entity_id and quarter_id as null #

If your actual goal is to filter Plans where every single linked Objective meets the null condition, whereHas won't work (it only checks for at least one matching record). Instead, use whereDoesntHave to exclude any Plan that has an Objective with non-null values:

$plans = Plan::with(['objectives', 'objectives.keyResults'])
    ->where('companyKey', Auth::user()->companyKey)
    ->whereDoesntHave('objectives', function($query) {
        // Exclude Plans that have ANY Objective with non-null entity_id or quarter_id
        $query->whereNotNull('entity_id')
              ->orWhereNotNull('quarter_id');
    })
    ->get();

Quick Troubleshooting Tips #

• Verify that the entity_id and quarter_id columns are indeed on the objectives table (not the plans table).
• Check for typos in column names (e.g., entityId instead of entity_id if you're using camelCase in your model).
• Run dd($plans->toSql()) to inspect the generated SQL query—this can help spot where the filter is missing or incorrect.

内容的提问来源于stack exchange，提问作者Asad Ur Rahman