Fetch one record from relationship for Datatable

Question

I'm using Laravel v8

I have 3 models as:Family, FamilyMember, FamilyRelationship

Family table:

id	address	created_at	updated_at
1	test	NULL	NULL

Family Relationship table:

id	name	created_at	updated_at
1	Head	NULL	NULL
2	Spouse	NULL	NULL
3	Child	NULL	NULL

Family Member table:

id	first_name	last_name	family_id	family_relationship_id
1	John	Doe	1	1
2	Jane	Doe	1	2
3	Max	Doe	1	3
4	Mary	Doe	1	3

I have created relation in the Family model as

public function members(){
   return $this->hasMany(FamilyMember::class, 'family_id');
}

And Family Relationship relation in Family Member model as

public function familyrelationship()
{
    return $this->belongsTo(FamilyRelationship::class, 'family_relationship_id');
}

I'm using Datatable to display the Family Information along with Head of the family

I want to display columns id and address from Family model and first_name and last_name from Family Member model where the family member relationship is Head.

How should go ahead with making relationship to display only head of the family detail in the datatable grid which is searchable too?

Above thing can be easily achieved using eloquent JOINs but I want to know if this can be achieved using relationship functionality provided by Laravel.

Answer 1

you can try whereHas mixing with hasOne , to build a new relation:

public function members(){
   return $this->hasMany(FamilyMember::class, 'family_id');
}

// then your new relation:

public function head(){
        return $this->hasOne(FamilyMember::class, 'family_id')->whereHas('familyrelationship',function ($query){
            $query->where('name','Head');
        });
    }

hasOne, will take only first result ...