Adding Multiple Where Clause from Model in Laravel

Question

I am trying to clean my code up, and working on the Models

I have the following 2 tables broken down like this:

Roll Table

|id|roll_id|member_id|.......

Members table

|id|first_name|last_name|rank|

I have the following on my Roll Model

public function member()
{
    return $this->belongsTo('App\Member');
}

This on my Member model

public function roll()
{
    return $this->hasMany('App\Roll');
}

While the following code does return the correct results

$roll = Roll::with(['member'])
        ->where('status', '!=', 'A')
        ->get();

return ($roll);

I would like to add an extra where clause

->where('rank','<', 12)

However, I get the following error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'member.rank' in 'where clause' (SQL: select * from Roll where roll_id = 4 and status != A and `mem ▶"

I think you are looking for [this](https://stackoverflow.com/a/27522556/8677188) — Ishaan, Feb 26 '19 at 05:30
@Ishann that post refers to 1 table, this is 2, the problem I face is it does find the member.rank column using the code provided above, the Roll::with(['member']) is returning the correct link between the 2 tables, and I can apply a where clause to the Roll Table, I can't to the member table as well — Brendan, Feb 26 '19 at 05:34

Kamal Paliwal · Accepted Answer · 2019-02-26T05:55:19.107

3

You can use whereHas method to filter on the relations:

$roll = Roll::with(['member'])
    ->where('status', '!=', 'A')
    ->whereHas('member', function($query) {
        $query->where('members.rank', '<', 12);
    })
    ->get();

Hope this will resolve your issue.

edited Feb 26 '19 at 05:55

answered Feb 26 '19 at 05:38

Kamal Paliwal

1,251
8
16

Perfect, thanks - now know I need to refer to the table and not the model in the $query – Brendan Feb 26 '19 at 05:43
You need to use the model name on which you are applying this condition. – Kamal Paliwal Feb 26 '19 at 05:46
Model name failed but table name worked, so for me member.rank failed (where member is the model), but members.rank worked (where members is the table name) – Brendan Feb 26 '19 at 05:50
The first argument inside the whereHas method will be the name of the relation and while using it in that query it will be table name members.rank. – Kamal Paliwal Feb 26 '19 at 05:56
Is it possible to define this in the HTML blade view? I would like to pull all of my Roll and Count Officer Ranks (rank < 12), WO Rank (between 12,13), NCO rank (between 14, 18) Cadet (rank > 18). This will save me completing the code a number of times, thanks – Brendan Feb 26 '19 at 22:26

Adding Multiple Where Clause from Model in Laravel

1 Answers1