How to get all the people in table 1 without getting the people who have me as parent_id in table 2?

Question

I'm writing this post because I have a problem with my relationships on Laravel.

Here is the structure I currently have:

1st table: - id - name - ...

2nd table : - parent_id - child_id

knowing that parent_id and child_id correspond to the same table. Here is the function that links them

public function relation()
{
    return $this->belongsToMany('App\Person', 'person_relations', 'parent_id', 'child_id', 'id', 'id');
}

Currently I would like, for a search system, to get all the people in table 1 without getting the people who have me as parent_id in table 2.

Answer 1

I have added an inverse relation parents() to the People model instead of the relation() method in the question.

Person Model :

public function parents()
{
    return $this->belongsToMany('App\Models\Person', 'person_relations', 'child_id', 'parent_id', 'id', 'id');
}

Controller :

public function test()
{
    $myId = 1;

    $persons = \App\Models\Person::whereDoesntHave('parents')
        ->orWhereHas('parents', function($qry) use($myId){
            $qry->where('person_relations.parent_id', '!=', $myId);
        })
        ->get();
    }

This method will return all records which doesn't have a given $myId as their parent id.

Tested and working in Laravel 6.2 and 7.29