Eloquent查询用于具有多个连接的数据库查询

huangapple go评论76阅读模式
英文:

Eloquent query for DB query with multiple joins

问题

Q1: 如何获取特定用户和日期范围的 Trackpoints?
Q2: 如何获取特定 Trackpoint 的 Timeline?

我目前使用了一个数据库查询来回答Q1,但希望有一种更... Eloquent 的方法。

或者这是否需要使用像 https://github.com/staudenmeir/belongs-to-through 这样的工具?

英文:

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 ?

答案1

得分: 1

在第一种情况下,Laravel 没有原生支持直接关系。您可以使用我的其他包之一: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();

对于第二个查询,您可以使用您提到的 belongs-to-through 包:

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

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

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

在 Laravel 5.8+ 中,您还可以使用原生的 HasOneThrough 关系:

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

$timeline = Trackpoint($id)->timeline;
英文:

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

<!-- language-all: php -->

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

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

$trackpoints = User::find($id)-&gt;trackpoints()
    -&gt;whereBetween(&#39;trackpoints.timestamp&#39;, [&#39;2019-12-13&#39;,&#39;2019-12-16&#39;])
    -&gt;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-&gt;belongsToThrough(Timeline::class, Activity::class);
    }
}

$timeline = Trackpoint($id)-&gt;timeline;

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

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

$timeline = Trackpoint($id)-&gt;timeline;

huangapple
  • 本文由 发表于 2020年1月6日 22:41:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/59614028.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定