Laravel雄辩加入与内部加入? [英] Laravel Eloquent Join vs Inner Join?

问题描述

所以我在弄清楚如何进行feed风格的mysql调用时遇到了一些麻烦，而且我不知道它是雄辩的问题还是mysql的问题.我相信这两种方法都是可行的，我只需要一些帮助.

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.

因此，我有一个用户，他们转到他们的供稿页面，该页面上显示了他们朋友的东西(朋友的投票，朋友的评论，朋友的状态更新).所以说我有汤姆，蒂姆和泰勒作为我的朋友，我需要得到他们的所有票数，评论和状态更新.我该怎么办?我具有按ID号列出的所有朋友的列表，并且对于每个事件(投票，注释，状态更新)都有一个表，这些表中都存储有ID以链接回用户.因此，如何立即获取所有这些信息，以便可以将其显示在Feed中.

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.

蒂姆评论很酷"

泰勒·赛德(Taylor Said)求婚状态更新！！"

Taylor Said "Woot first status update~!"

泰勒被评选为有史以来最佳比赛"

Taylor Voted on "Best competition ever"

编辑@damiani因此，在完成模型更改后，我得到了这样的代码，并且确实返回了正确的行

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

$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.*']);

但是我希望它们全部同时发生，这是因为mysql按顺序对数千条记录进行排序的速度比php接收3个列表，合并它们然后执行的速度快100倍.有什么想法吗?

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?

推荐答案

我确定还有其他方法可以实现此目的，但是一种解决方案是通过查询生成器使用 join .

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

如果您的表设置如下:

users
    id
    ...

friends
    id
    user_id
    friend_id
    ...

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

在您的用户模型中:

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

在您的朋友模型中:

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

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

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

$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

对 comments 和 status_updates 表运行相同的 join .如果希望将投票，评论和status_updates放在一个按时间顺序排列的列表中，则可以将结果三个集合合并为一个，然后对合并的集合进行排序.

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.

修改

要在一个查询中获得投票，评论和状态更新，可以构建每个查询，然后合并结果.不幸的是，如果我们使用雄辩的 hasMany 关系(

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:

$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();

但是，在这一点上，我们的查询有点毛茸茸，因此与和一个额外的表(如下面的DefiniteIntegral建议)建立多态关系可能是一个更好的主意.

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.

这篇关于Laravel雄辩加入与内部加入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！