Using except when creating query with laravel scope

Question

When I type the second where, the query doesn't work as I want. DB rows:

batch_id - sub_stage_id

• 1 - NULL
• 1 - NULL
• 1 - NULL
• 1 - 28
• 2 - NULL
• 2 - NULL
• 2 - NULL
• 2 - NULL

I'm sending substage into scope. I want it to return the Null ones, but not the ones that are equal to sub_stage_id

public function scopeBatchSubStageReady($query,$subStage,$stage = "Data Collection"){

    $stage_id = JRoadStage::findStageId($stage);
    $sub_stage_id = JRoadStage::findStageId($subStage);

    return $query->whereHas('roads',function (Builder $query) use($stage_id,$sub_stage_id){
        $query->items()->where(function (Builder $query) use($stage_id,$sub_stage_id){

            //substage equal null
                $query->where('stage_id',$stage_id)
                    ->whereNull('sub_stage_id')
                    ->where("is_here",true);

        })->where(function (Builder $query) use($stage_id,$sub_stage_id){

            //not equal to substage 
            $query->where('stage_id',$stage_id)
                ->where('sub_stage_id','!=',$sub_stage_id);

        });
    });
}

When I put the second where, it gives no results. It gives batch_id 1 and 2 when I don't. what i want is it just give the batch_id 2 because of the substage I sent.

$readyForItems = BatchItem::BatchSubStageReady("Screening")->get();
dd(readyForItems);

How can i fix my query in the scope method?

Answer 1

what you need is whereDoesntHave. Can you try this?

$query->whereHas('roads',function (Builder $query) use($stage_id,$sub_stage_id){
        $query->items()->where(function (Builder $query) use($stage_id,$sub_stage_id){
            
                $query->where('stage_id',$stage_id)
                    ->whereNull('sub_stage_id')
                    ->where("is_here",true);

        });
    })->whereDoesntHave('roads',function (Builder $query) use($stage_id,$sub_stage_id){
        $query->items()->where(function (Builder $query) use($stage_id,$sub_stage_id){
            
            $query->where('stage_id',$stage_id)
                ->where('sub_stage_id',$sub_stage_id);

        });
    });