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?

The Alpha · Accepted Answer · 2014-09-11T22:20:21.677

105

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

edited Sep 11 '14 at 22:20

answered Sep 11 '14 at 21:57

The Alpha

143,660
29
287
307

I want to query the status table name field not the projects table – user0129e021939232 Sep 11 '14 at 21:58
Yes, that's it `$q->where('name', '=', $value)` inside the `closure` which is the value of `status`. – The Alpha Sep 11 '14 at 21:59
why does it fetch status with null? – user0129e021939232 Sep 11 '14 at 22:05
1

Do you have a `select` statement ? It's probably you don't have any status with that name. – The Alpha Sep 11 '14 at 22:07
I do though, take this for example project has a project_name of Open but is still retrieved: {"id": "115","user_id": "1","client_id": "69","status_id": "2","project_name": "Open","clients": {"id": "69","user_id": "1","client_name": "A Arcu Sed Associates", }, "status": null}] and I have the open statuses retrieved too – user0129e021939232 Sep 11 '14 at 22:11
Do you want to `exclude` i.e. `where != 'Open'` ? – The Alpha Sep 11 '14 at 22:13
btw I have not included the where('status_id', '!=', '2') as I want to avoid specifying it this way, as the id could be anything really. – user0129e021939232 Sep 11 '14 at 22:14
To exclude it, you may use `$q->where('name', '!=', $value);`, updated. – The Alpha Sep 11 '14 at 22:14
No i want to retrieve the Open projects. Open is a status name in the statuses table. – user0129e021939232 Sep 11 '14 at 22:15
4

The ->whereHas() method was what I needed to get this to work. Thank you! – Erin Geyer Mar 06 '18 at 15:27
Glad to know that @MiftahAfina :-) – The Alpha Feb 08 '19 at 08:19
1

oh no i have this error, Lazy loading was disabled to detect N+1 problems @TheAlpha – Azade Oct 25 '21 at 04:57

score 12 · Answer 2 · edited Jul 20 '17 at 08:02

12

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

edited Jul 20 '17 at 08:02

Aatish Sai

1,647
1
26
41

answered Sep 08 '16 at 19:51

Md Shahadat Hossain

339
2
13

1

What if I want to run where clause for 'clients' table also along with this $q->where('status_id', '!=', '2'); – leaveme_alone Apr 13 '20 at 21:41

sh6210 · Answer 3 · 2023-06-30T23:13:29.343

If you need add condition you could do

return Query::with(['department' => function ($query) use ($department) {
           $query->when($department, function ($query) use ($department) {
              $query->whereId($department);
           });
        }, 'category' => function($query) use ($category) {
            $query->when($category, function ($query) use ($category) {
              $query->whereId($category);
           });
        }])->when($activeStatus, function ($query) use ($activeStatus) {
              $activeStatus = (request()->active_status === 'active') ? 1 : 0;
              $query->whereActive($activeStatus);
         })->latest()->paginate($dataPerPage);

Or you could do

return Query::with('department', 'category')
    ->when($department, function($query) use ($department) {
        $query->whereHas('department', function (Builder $query) use ($department) {
            $query->whereId($department);
        });
    })->when($category, function ($query) use ($category) {
        $query->whereHas('category', function (Builder $query) use ($category) {
            $query->whereId($category);
        });
    })->when($activeStatus, function ($query) use ($activeStatus) {
        $activeStatus = (request()->active_status === 'active') ? 1 : 0;
        $query->whereActive($activeStatus);
    })->latest()->paginate($dataPerPage);

Please do not use images as an answer as this is less accessable. — Robin Bastiaan, Jan 11 '23 at 17:46

laravel eloquent relationship with and where clause based on foreign column

3 Answers3

Linked