CODEWITHSUNDEEP
laraveleloquent
nikname
nikname

Reputation: 161

Eloquent filter relationship records

I want to get only those records from relationship where certain column is not null. Here are the tables:

Items:
id name
1 item1
2 item2
Parts:
id item_id name
1 1 part1
2 1 part2
3 2 null
4 2 part3

Relation inside Item model:

public function parts()
{
    return $this->hasMany('App\Models\Part');
}

Relation inside Part model:

public function item()
{
    return $this->belongsTo('App\Models\Item');
}

I have tried with this code:

Item::with('parts')
    ->whereHas('parts', function($query) {
       $query->whereNotNull('name');
    })
    ->get();

And it retrieves only item with id = 1. I would like to get item with id = 2 also, but only with part where name is not null.

Upvotes: 1

Views: 997

Answers (1)

Clément Baconnier
Clément Baconnier

Reputation: 6108

The issue that item2 as 2 parts

PARTS:

id item_id name
3 2 null
4 2 part3

Your query returns parts (id: 3 and 4) because you're querying:

Get me all the items with the parts, where items have part with a name.

But you need

Get me all the items with the parts that have a name, where the items have part with a name.

To do this, you need to add a subquery

Item::with(['parts' => function($query) {
    $query->whereNotNull('name');
}])
    ->whereHas('parts', function($query) {
        return $query->whereNotNull('name');
    })
    ->get();

Upvotes: 3

Related Questions