Laravel add where condition in DB::raw count

I have a table called project_cases and it has a child table defects.

I created a datatable and I want to populate it with project_cases details in it, but right now i need to to add another data in my query object that count how many defects associated with a particular project_cases and where its status is not resolved or closed. I'm not sure how to use DB::raw` query and right now its throwing errors. Below is my code :

Case query:

public function index($proj_id)
{
    $cases = ProjectCase::join('projects', 'projects.id', '=', 'project_cases.project_id')
                        ->join('units', 'projects.id', '=', 'units.project_id')
                        ->join('defects', 'defects.case_id', '=', 'project_cases.id')
                        ->where('projects.id', $proj_id)
                        ->select([
                            'projects.id',
                            'units.unit_no',
                            'project_cases.status',
                            'project_cases.created_at',
                            \DB::raw("count(defects.id) as unresolvedDefectsCount whereNot defects.status='resolved' and whereNot defects.status='closed'"), //Not Working
                        ])->get();
    dd($cases);
    return view('dev-admin.projects.cases.index', ['cases' => $cases, 'proj_id' => $proj_id]);
}

How can I implement the code below into the DB::Raw query? :

$unresolvedDefectsCount = Defect::where('case_id', $row->id)->whereNotIn('status', [DefectStatus::RESOLVED, DefectStatus::CLOSED])->count();

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2WcauQX
via IFTTT