Eloquent query for DB query with multiple joins

Question

My simplified Model relationships:

User (id, email) → Timeline (id, user_id) → Activity (id, timeline_id) → Trackpoint(id, activity_id)

Q1: How do I get Trackpoints for a specific user and date range?
Q2: How do I get the Timeline for a certain Trackpoint?

I currently use a DB query for Q1, but was hoping there was a more... Eloquent way.

        $trackpoints = DB::table('trackpoints')
            ->join('activities', 'activities.id', '=', 'trackpoints.activity_id')
            ->join('timelines', 'timelines.id', '=', 'activities.timeline_id')
            ->where('timelines.user_id', 1)
            ->whereBetween('trackpoints.timestamp',['2019-12-13','2019-12-16'])

Or does that require e.g. https://github.com/staudenmeir/belongs-to-through ?

Jonas Staudenmeir · Accepted Answer · 2020-01-06 15:36:12Z

1

In the first case, Laravel has no native support for a direct relationship. You can use one of my other packages: https://github.com/staudenmeir/eloquent-has-many-deep

class User extends Model
{
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;

    public function trackpoints()
    {
        return $this->hasManyDeep(
            Trackpoint::class, [Timeline::class, Activity::class]
        );
    }
}

$trackpoints = User::find($id)->trackpoints()
    ->whereBetween('trackpoints.timestamp', ['2019-12-13','2019-12-16'])
    ->get();

For the second query, you can use the belongs-to-through package you mentioned:

class Trackpoint extends Model
{
    use \Znck\Eloquent\Traits\BelongsToThrough;

    public function timeline()
    {
        return $this->belongsToThrough(Timeline::class, Activity::class);
    }
}

$timeline = Trackpoint($id)->timeline;

In Laravel 5.8+, you can also use a native HasOneThrough relationship:

class Trackpoint extends Model
{
    public function timeline()
    {
        return $this->hasOneThrough(
            Timeline::class, Activity::class,
            'id', 'id',
            'activity_id', 'timeline_id'
        );
    }
}

$timeline = Trackpoint($id)->timeline;

answered Jan 6, 2020 at 15:36

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

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

1 Comment

wivku Over a year ago

Thanks! Will check out your package. For Q2: I thought I could use hasOneThrough with just the first 2 arguments. Apparently not! FYI: argument 5 and 6 are not necessary.

Collectives™ on Stack Overflow

Eloquent query for DB query with multiple joins

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related