001221 001221 - 2 months ago 25
PHP Question

laravel eloquent relationship with and where clause based on foreign column

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

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();