Has many of a model where ancestors may belong to it.

[saade]

I am currently working on a scenario where I have a Subject model with a hierarchical structure, and each subject belongs to a Department. But also, a Subject may heve its department_id null but one of its ancestors have the foreign key to the Department.

In my use case, I need to retrieve subjects that belong to a specific department, as well as subjects that have a department_id of null but their ancestors have a filled department_id.

Here's my. table structure:

Departments table

id	other_columns
1	...
2	...
3	...

Subjects Table

id	parent_id	department_id	other_columns
1	null	1	A
2	1	null	A.A
3	1	null	A.B
4	2	2	A.A.A
5	2	null	A.A.B
6	4	null	A.A.A.A

With the following query:

Departments::find(1)->subjects

i want to retrieve the A, A.A and A.B event though A.A and A.B doesnt have the department_id filled (but one of its ancestors have).

I also need the inverse relationship:

Subject::find('A.B')->department // Department 1 event though department_id is null.

I know its sounds much, but i'm willing to pay for your help, thank you!

[staudenmeir]

Hi @saade,
Shouldn't this be 4?

[saade]

I dont think so? A.A.A and A.A.B are siblings

[staudenmeir]

i want to retrieve the A, A.A and A.B

I asked because you didn't include A.A.B here. Why should it not be part of this result despite belonging to department 1 (as a descendant of subject 1)?

[saade]

i want to retrieve the A, A.A and A.B

I asked because you didn't include A.A.B here. Why should it not be part of this result despite belonging to department 1 (as a descendant of subject 1)?

oh, yeah my bad mate, it should include a.a.b too 😅

[saade]

Just to be completly clear:

It should not include A.A.A nor A.A.A.A even though A.A.A's parent belongs to department 1. Because A.A.A belongs to department 2 so it overrides its parent department 1.

[saade]

Made a little visual representation =)

[staudenmeir]

Departments::find(1)->subjects

It's not possible to define a relationships here, but you can run a "normal" query:

$department = Departments::find(1);

$subjects = Subject::withRecursiveQueryConstraint(function (Builder $query) {
    $query->whereNull('subjects.department_id');
}, function () use ($department) {
    $constraint = function ($query) use ($department) {
        $query->where('department_id', $department->id);
    };

    return Subject::treeOf($constraint)->get();
});

Subject::find('A.B')->department // Department 1 event though department_id is null.

You can define a relationship for this case using concatenation:

class Subject extends Model
{
    // Install https://github.com/staudenmeir/eloquent-has-many-deep
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;

    public function department()
    {
        return $this->belongsTo(Department::class);
    }

    public function rootDepartment()
    {
        return $this->hasOneDeepFromRelations(
            $this->ancestorsAndSelf(),
            (new static)->department()
        )->orderByDesc('depth');
    }
}

$department = Subject::find('A.B')->rootDepartment;

[saade]

Hey! Thanks for that!

I didnt have the time to test it as i'm on another part of the project, but i'll let you know. Do you have a sponsor profile?

[staudenmeir]

I have a PayPal page: https://paypal.me/JonasStaudenmeir