Laravel雄辩的加入vs内加入?-LaravelEloquentJoinvsInnerJoin?

23  

I'm sure there are other ways to accomplish this, but one solution would be to use join through the Query Builder.

我确信还有其他方法可以实现这一点，但是一种解决方案是通过查询构建器使用join。

If you have tables set up something like this:

如果你有这样的表格:

users
    id
    ...

friends
    id
    user_id
    friend_id
    ...

votes, comments and status_updates (3 tables)
    id
    user_id
    ....

In your User model:

在你的用户模型:

class User extends Eloquent {
    public function friends()
    {
        return $this->hasMany('Friend');
    }
}

In your Friend model:

在你的朋友的模型:

class Friend extends Eloquent {
    public function user()
    {
        return $this->belongsTo('User');
    }
}

Then, to gather all the votes for the friends of the user with the id of 1, you could run this query:

然后，要为id为1的用户的朋友收集所有选票，可以运行以下查询:

$user = User::find(1);
$friends_votes = $user->friends()
    ->with('user') // bring along details of the friend
    ->join('votes', 'votes.user_id', '=', 'friends.friend_id')
    ->get(['votes.*']); // exclude extra details from friends table

Run the same join for the comments and status_updates tables. If you would like votes, comments, and status_updates to be in one chronological list, you can merge the resulting three collections into one and then sort the merged collection.

对注释和status_updates表运行相同的连接。如果您希望在一个时间列表中使用投票、注释和status_updates，您可以将产生的三个集合合并为一个，然后对合并的集合进行排序。

Edit

编辑

To get votes, comments, and status updates in one query, you could build up each query and then union the results. Unfortunately, this doesn't seem to work if we use the Eloquent hasMany relationship (see comments for this question for a discussion of that problem) so we have to modify to queries to use where instead:

要在一个查询中获得选票、注释和状态更新，可以构建每个查询，然后将结果合并。不幸的是，如果我们使用雄辩的hasMany关系(关于这个问题的讨论请参阅对这个问题的评论)，这似乎行不通，因此我们必须修改查询以使用where:

$friends_votes = 
    DB::table('friends')->where('friends.user_id','1')
    ->join('votes', 'votes.user_id', '=', 'friends.friend_id');

$friends_comments = 
    DB::table('friends')->where('friends.user_id','1')
    ->join('comments', 'comments.user_id', '=', 'friends.friend_id');

$friends_status_updates = 
    DB::table('status_updates')->where('status_updates.user_id','1')
    ->join('friends', 'status_updates.user_id', '=', 'friends.friend_id');

$friends_events = 
    $friends_votes
    ->union($friends_comments)
    ->union($friends_status_updates)
    ->get();

At this point, though, our query is getting a bit hairy, so a polymorphic relationship with and an extra table (like DefiniteIntegral suggests below) might be a better idea.

不过，在这一点上，我们的查询变得有点麻烦了，因此一个多态关系和一个额外的表(就像下面给出的定义一样)可能是个更好的主意。

2  

Probably not what you want to hear, but a "feeds" table would be a great middleman for this sort of transaction, giving you a denormalized way of pivoting to all these data with a polymorphic relationship.

可能不是您想要听到的，但是一个“提要”表将是这种事务的一个很好的中间体，使您能够以一种非规格化的方式以多态性关系对所有这些数据进行旋转。

You could build it like this:

你可以这样构建它:

increments('id');
    $table->timestamps();
    $table->unsignedInteger('user_id');
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
    $table->morphs('target'); 
});

Build the feed model like so:

构建提要模型如下:

belongsTo('User');
    }

    public function target()
    {
        return $this->morphTo();
    }
}

Then keep it up to date with something like:

然后保持最新的信息，比如:

id;
    $user_id     = $vote->user_id;

    Feed::create(compact('target_type', 'target_id', 'user_id'));
});

You could make the above much more generic/robust—this is just for demonstration purposes.

您可以使上面的内容更加通用/健壮—这只是为了演示目的。

At this point, your feed items are really easy to retrieve all at once:

此时，您的提要条目非常容易同时检索到所有内容:

with('user', 'target')
    ->orderBy('created_at', 'desc')
    ->get();