php - Laravel nested relationships

Question

I'm having trouble getting a very-nested relationship to work correctly in laravel.

The wanted behaviour is as follows,

I select an event by ID and i want to see which persons are subscribed to it. Now the problem is there are some tables between the event and the person..

This is the query that works!

SELECT persons.id, 
       persons.firstname, 
       persons.lastname, 
       event_scores.score 
FROM   events 
       JOIN cities 
         ON cities.id = events.city_id 
       JOIN companies 
         ON cities.id = companies.city_id 
       JOIN persons 
         ON companies.id = persons.company_id 
       JOIN event_scores 
         ON event_scores.person_id = persons.id 
WHERE  event_scores.event_id = 1 
GROUP  BY persons.id 

These are my relations

Event Model

class Event extends Eloquent
{
    protected $table = 'events';

    public function city()
    {
        return $this->belongsTo('City');
    }
}

City Model

class City extends Eloquent
{
    protected $table = 'cities';

    public function companies()
    {
        return $this->hasMany('Company');
    }

    public function event()
    {
        return $this->hasMany('Event');
    }
}

Company Model

class Company extends Eloquent {

    protected $table = 'companies';

    public function persons()
    {
        return $this->hasMany('Person');
    }

    public function city()
    {
        return $this->belongsTo('City');
    }
}

Person Model

class Person extends Eloquent
{
    protected $table = 'persons';

    public function company()
    {
        return $this->belongsTo('Company');
    }

    public function eventscore()
    {
        return $this->belongsToMany('Event', 'event_scores', 'person_id', 'event_id')
            ->withPivot('score')
            ->withTimestamps();
    }
}

What I have tried

return Event::with('city')->with('company')->get();

and

return Event::with('city')
    ->whereHas('companies', function($query) use ($company_id){
        $query->where('company_id', $company_id);
    })->get();

And many other possibilities, I'm really stuck on this. Is it so difficult in laravel to achieve this kind of nested relationship linking?

Thanks!

Answer 1

return Event::with('city.companies.persons')->get();

If you only want to select certain fields from the persons table, use this:

return Event::with(['city.companies.persons' => function ($query) {
    $query->select('id', '...');
}])->get();