How to define a hasMany far relationship

Question

Suppose you have

Facility  1------*  Section  1------*  Session  *------1  Event

I.e., a facility has many sections; each section can hold many sessions; each session belongs to an event.

How can I define this as a relationship in the Facility model to retrieve all unique instances of Event that a facility is hosting? I have tried this:

class Facility extends Eloquent\Model {
    public function events() {
        return $this->hasMany('Event')
            ->join('session', 'session.event_id', '=', 'event.id')
            ->join('section', 'section.id', '=', 'session.section_id')
            ->join('facility', 'facility.id', '=', 'section.facility_id');
    }
}

I don't know if I'm very close with that; Laravel adds a constraint implicitly ("events"."facility_id" in (...)) and everything gets messed up.

What is the proper way of doing this?

Answer 1

This is the closer i've got to my initial purpose:

I created an SQL view:

CREATE VIEW facility_events AS
  SELECT DISTINCT ON (e.id) e.id,
    e.name,
    e.created_at,
    e.updated_at,
    f.id AS facility_id
  FROM events e
    JOIN sessions s ON e.id = s.event_id
    JOIN sections_extended fse ON s.section_id = fse.id
    JOIN facilities f ON fse.root_facility_id = f.id;

Then I create the corresponding FactoryEvent Eloquent model and, finally, in my class Facility:

public function events() {
    return $this->hasMany('App\FacilityEvent');
}

I look forward to see Laravel-only solutions for this. In other frameworks, such as Yii, I have been able to do things like that without the need to work on the database directly.