Laravel "where" query the relationship table

Question

This is my query:

$items = UserItems::with('item')
            ->where('user_id','=',$this->id)
            ->where('quantity','>',0)
            ->where('items.type','=',"shirt")
            ->get();

I need all items where type is shirt.

The query returns this:

Column not found: 1054 Unknown column 'items.type' in 'where clause'

For some reasons, the items is not recognized as a table in this query, and I can't use `where on it.

Then how I can get all user items where the item type is shirt?

is your table called 'item' or 'items'? if so, change -> with('item') to with('items'), if not, change -> ->where('items.type','=',"shirt") to ->where('item.type','=',"shirt") — Carlos, May 20 '16 at 20:37

score 1 · Answer 1 · edited May 23 '17 at 11:52

1

Decision

Try:

$items = UserItems::with(['item' => function($query){
    return $query->where("type", "shirt")
})
->where('user_id','=',$this->id)
->where('quantity','>',0)
->get();

edited May 23 '17 at 11:52

Community

1
1

answered May 20 '16 at 20:31

Andrii Lutskevych

1,349
13
23

user1669496 · Accepted Answer · 2016-05-23T12:34:27.857

1

You will actually need to use whereHas to handle this...

$items = UserItems::with('item')
        ->whereHas('item', function($q) {
            $q->where('type', 'shirt');
         })
        ->where('user_id','=',$this->id)
        ->where('quantity','>',0)
        ->get();

edited May 23 '16 at 12:34

answered May 20 '16 at 20:57

user1669496

32,176
9
73
65

Laravel "where" query the relationship table

2 Answers2