How to retrieve multiple relations with multiple tables in laravel eloquent

Question

I'm using Laravel 5.8 to build a babysitting site. I have 4 tables with different relationships as below:

The relationships are:

Babysitter->hasMany(session)
Sessions->hasOne(Review)
Sessions->hasOne(Kids)
Sessions->hasOne(Babysitter)
Sessions->hasOne(Parent)

I want to achieve 2 things:

First one

I want to show this result when listing all babysitters. I'm showing this information for each babysitter:

plsease see this image

See here what I couldn't achieve

plsease see this image

This is my code

Sitters::where('Status', 'active')->where('Verified', 1)->get();

Second one

Also, I've tried to show kids name with parent review as shown here:

plsease see this image

This is what i'm using

Sessions::select('Reviews.*', 'Sessions.Parent_id')->join('Reviews', 'Reviews.Session_id', '=', 'Sessions.id')->with('owner')->where('Trainer_id', session('user')->Id)->where('Status', '=', 'complete')->with('owner')->orderBy('Sessions.id', 'DESC')->get();

Here is Session.php Model

public function owner(){
    return $this->belongsTo('App\Models\Parents', 'Parent_id');
}

The other way of HasMany relation is belongsTo not HasOne. Sessions->hasOne(Review), Sessions->belongsTo(Kids), Sessions->belongsTo(Babysitter), Sessions->belongsTo(Parent). Try see if this will allow you to query build using the relations. — N69S
– N69S, Commented Aug 23, 2019 at 14:09
Also Sessions and Reviews can be merged into the same table with nullable review's fields. but that's just a preference — N69S
– N69S, Commented Aug 23, 2019 at 14:10
I used both (BelongsTo) and (hasOne) without any luck L(, 5 days now and it seems that I haven't reached to a point. that's why I posted here :( @N69S — Mohd Domais
– Mohd Domais, Commented Aug 23, 2019 at 14:25
Is it sesstion or session? Are those typos? Plus, it's better including relevant parts of your code here than putting inline image links. The links are very distracting. — Udochukwu Enwerem
– Udochukwu Enwerem, Commented Aug 23, 2019 at 14:49
it is session, my bad :) ... I put my code, and the images are to show you what I'm looking for as a final design. @udo-e — Mohd Domais
– Mohd Domais, Commented Aug 23, 2019 at 17:06

N69S · Accepted Answer · 2019-08-23 14:59:34Z

1

As discussed change the relations:

Babysitter->hasMany(sesstion)
Sessions->hasOne(Review)
Sessions->belongsTo(Kids)
Sessions->belongsTo(Babysitter)
Sessions->belongsTo(Parent)

First one

in Babysitter.php declare the following attributes

class Babysitter extends Model
{
    public function reviews()
    {
        $this->hasManyThrough(Review::class, Session::class);
    }

    public function getAverageReviewAttribute()
    {
        return $this->reviews()->avg('Rating');
    }
}

Then you just need to call it on the model instance.

$babysitter = Babysitter::first();
return $babysitter->average_review;

Second one

Just use the relation

$babysitter = BabySitter::with(['sessions' => public function ($session) {
        $session->with(['review','parent','kids']);
    })->where('trainer_id', '=', session('user')->Id) //did not understand this condition
    ->first();

This assumes you have parent, kids and review relation declared on Session::class. (change the names if needed)

answered Aug 23, 2019 at 14:59

N69S

17.3k4 gold badges25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mohd Domais Over a year ago

What is the type of "declared relations" inside Session::class , hasMany or BelongsTo or ... ? @n69s

N69S Over a year ago

The Session::class relations are the first thing in my answer

Mohd Domais · Accepted Answer · 2019-08-24 08:39:53Z

0

After a few days of searching & testing, this is what worked for me:

Inside (Sitters) Model, put this relation

public function sessions()
{
    return $this->hasMany(Sessions::class, 'sitter_id')
        ->withCount('reviews')
        ->withCount(['reviews as review_avg' => function($query){
            $query->select(DB::raw('AVG(Rating)'));
        }]);
}

Also, inside (Sessions) Model, put this relation

public function reviews()
{
    return $this->hasOne(Reviews::class, 'Session_id');
}

Now you query like this

return $sitters = Sitters::with('sessions')->get();

I hope this can help someone :)

answered Aug 24, 2019 at 8:39

Mohd Domais

431 silver badge9 bronze badges

1 Comment

N69S Over a year ago

So you are averaging the rating by session and not by Babysitter ?

Collectives™ on Stack Overflow

How to retrieve multiple relations with multiple tables in laravel eloquent

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest