php Laravel Eloquent Join vs Inner Join？

Laravel Eloquent Join vs Inner Join?

提问by CMOS

So I am having some trouble figuring out how to do a feed style mysql call, and I don't know if its an eloquent issue or a mysql issue. I am sure it is possible in both and I am just in need of some help.

所以我在弄清楚如何进行提要风格的 mysql 调用时遇到了一些麻烦，我不知道这是一个雄辩的问题还是一个 mysql 问题。我相信这两者都是可能的，我只是需要一些帮助。

So I have a user and they go to their feed page, on this page it shows stuff from their friends (friends votes, friends comments, friends status updates). So say I have tom, tim and taylor as my friends and I need to get all of their votes, comments, status updates. How do I go about this? I have a list of all the friends by Id number, and I have tables for each of the events (votes, comments, status updates) that have the Id stored in it to link back to the user. So how can I get all of that information at once so that I can display it in a feed in the form of.

所以我有一个用户，他们去他们的提要页面，在这个页面上它显示了他们朋友的东西（朋友投票，朋友评论，朋友状态更新）。所以说我有汤姆、蒂姆和泰勒作为我的朋友，我需要得到他们所有的投票、评论、状态更新。我该怎么做？我有一个按 ID 编号列出的所有朋友的列表，并且我有每个事件（投票、评论、状态更新）的表格，其中存储了 ID 以链接回用户。那么我怎样才能一次获得所有这些信息，以便我可以以 的形式在提要中显示它。

Tim commented "Cool"

蒂姆评论“酷”

Taylor Said "Woot first status update~!"

泰勒说“Woot first status update~!”

Taylor Voted on "Best competition ever"

泰勒被评为“有史以来最好的比赛”

Edit @damiani So after doing the model changes I have code like this, and it does return the correct rows

编辑@damiani 所以在进行模型更改后，我有这样的代码，它确实返回了正确的行

$friends_votes = $user->friends()->join('votes', 'votes.userId', '=', 'friend.friendId')->orderBy('created_at', 'DESC')->get(['votes.*']);
$friends_comments = $user->friends()->join('comments', 'comments.userId', '=', 'friend.friendId')->orderBy('created_at', 'DESC')->get(['comments.*']);
$friends_status = $user->friends()->join('status', 'status.userId', '=', 'friend.friendId')->orderBy('created_at', 'DESC')->get(['status.*']);

But I would like them all to happen at once, this is because mysql sorting thousands of records in order is 100x faster then php taking 3 lists, merging them and then doing it. Any ideas?

但我希望它们同时发生，这是因为 mysql 按顺序对数千条记录进行排序比 php 获取 3 个列表、合并它们然后执行它快 100 倍。有任何想法吗？

回答by damiani

I'm sure there are other ways to accomplish this, but one solution would be to use jointhrough 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 Usermodel:

在您的用户模型中：

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

In your Friendmodel:

在您的朋友模型中：

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 joinfor the commentsand status_updatestables. 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.

join对comments和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 hasManyrelationship (see comments for this questionfor a discussion of that problem) so we have to modify to queries to use whereinstead:

要在一个查询中获得投票、评论和状态更新，您可以构建每个查询，然后合并结果。不幸的是，如果我们使用 EloquenthasMany关系，这似乎不起作用（有关该问题的讨论，请参阅此问题的评论），因此我们必须修改查询以改为使用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.

不过，此时我们的查询有点麻烦，因此多态关系和一个额外的表（如下面的 DefiniteIntegral 建议）可能是一个更好的主意。

回答by DefiniteIntegral

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:

你可以像这样构建它：

<?php

Schema::create('feeds', function($table) {
    $table->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:

像这样构建提要模型：

<?php

class Feed extends Eloquent
{
    protected $fillable = ['user_id', 'target_type', 'target_id'];

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

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

Then keep it up to date with something like:

然后使用以下内容使其保持最新状态：

<?php

Vote::created(function(Vote $vote) {
    $target_type = 'Vote';
    $target_id   = $vote->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:

此时，您的提要项目真的很容易一次检索：

<?php

Feed::whereIn('user_id', $my_friend_ids)
    ->with('user', 'target')
    ->orderBy('created_at', 'desc')
    ->get();