[FIXED] Eloquent filter relationship records

Issue

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.

Solution

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

Answered By - Clément Baconnier