laravel eloquent relationship with and where clause based on foreign column

Question

Hi I want to retrieve my projects held in a db which are owned by an auth user who also creates clients (they have many projects and tasks) and tasks (belongs to a project and tasks and user).

I want to retrieve all tasks that are not marked as closed in the status table, I know the id of this is 2 and I can retrieve this as so:

public function getOpenProjects() {      return \Project::with(['clients', 'tasks', 'status'])         ->where('status_id', '!=', '2')         ->whereUserId(Auth::user()->id)         ->get(); } 

But how can I change this to query against a column in the statuses table, i.e. the name column in that table?

Answer 1

You may try this:

$value = 'someName'; Project::with(['clients', 'tasks', 'status' => function($q) use($value) {     // Query the name field in status table     $q->where('name', '=', $value); // '=' is optional }]) ->where('status_id', '!=', '2') ->whereUserId(Auth::user()->id) ->get(); 

Also you may try this (It will fetch records only if the query returns name you want, otherwise none):

$value = 'someName'; Project::with(['clients', 'tasks', 'status'])        ->whereHas('status', function($q) use($value) {        // Query the name field in status table        $q->where('name', '=', $value); // '=' is optional }) ->where('status_id', '!=', '2') ->whereUserId(Auth::user()->id) ->get(); 

Answer 2

You may try this:

Project::with(['clients', 'tasks' => function($q) use($value) { // Query the name field in status table     $q->where('status_id', '!=', '2');  }]) ->whereUserId(Auth::user()->id) ->get();