MySQL Laravel 加入 3 个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18388664/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel join with 3 Tables
提问by Plic Pl
I am building a Twitter-like app. There is a Feed in which I want to only show posts of Users who I follow.
我正在构建一个类似 Twitter 的应用程序。有一个供稿，我只想显示我关注的用户的帖子。
I tried everything with joins, but nothing seems to work.
我尝试了所有连接，但似乎没有任何效果。
I have 3 tables: Users, Followers, Shares
我有 3 个表：Users, Followers,Shares
The Tables look like this:
表格如下所示：
Users: id
用户：id
Followers: user_id, follower_id
追随者：user_id，follower_id
Shares: user_id
股份：user_id
What I need to get is "ALL Shares WHERE share.user_id = followers.follower_id" "ANDWHERE followers.user_id = users.id"
我需要得到的是“所有共享 WHERE share.user_id = follower.follower_id”“ANDWHERE follower.user_id = users.id”
Assume, the users.id is 3, I tried this:
假设 users.id 是 3，我试过这个：
$shares = DB::table('shares')
->leftjoin('followers', 'shares.user_id', '=', 'followers.follower_id')
->leftjoin('users', 'followers.user_id', '=', 'users.id')
->where('users.id', 3)
->where('shares.user_id', 'followers.follower_id')
->get();
But it doesnt work.
但它不起作用。
Any help is appreciated :)
任何帮助表示赞赏:)
回答by vFragosop
I believe your join is wrong:
我相信你的加入是错误的：
$shares = DB::table('shares')
->join('users', 'users.id', '=', 'shares.user_id')
->join('followers', 'followers.user_id', '=', 'users.id')
->where('followers.follower_id', '=', 3)
->get();
I also suggest you to name your table as followsinstead, it feels a bit more natural to say user has many followers through followsand user has many followees through follows.
我还建议您将您的表命名为follows，这样说user has many followers through follows和感觉更自然一些user has many followees through follows。
Example
例子
$shares = DB::table('shares')
->join('users', 'users.id', '=', 'shares.user_id')
->join('follows', 'follows.user_id', '=', 'users.id')
->where('follows.follower_id', '=', 3)
->get();
Model approach
模型方法
I didn't realize you were using DB::queries and not models. So I'm fixing the answer and providing a lot more clarity. I suggest you use models, it's a lot easier for those beginning with the framework and specially SQL.
我没有意识到您使用的是DB::查询而不是模型。所以我正在修复答案并提供更多清晰度。我建议你使用模型，对于那些从框架开始，特别是 SQL 的人来说，这要容易得多。
Example of models:
模型示例：
class User extends Model {
public function shares() {
return $this->hasMany('Share');
}
public function followers() {
return $this->belongsToMany('User', 'follows', 'user_id', 'follower_id');
}
public function followees() {
return $this->belongsToMany('User', 'follows', 'follower_id', 'user_id');
}
}
class Share extends Model {
public function user() {
return $this->belongsTo('User');
}
}
Example of Model usage:
模型使用示例：
$my = User::find('my_id');
// Retrieves all shares by users that I follow
// eager loading the "owner" of the share
$shares = Share::with('user')
->join('follows', 'follows.user_id', '=', 'shares.user_id')
->where('follows.follower_id', '=', $my->id)
->get('shares.*'); // Notice the shares.* here
// prints the username of the person who shared something
foreach ($shares as $share) {
echo $share->user->username;
}
// Retrieves all users I'm following
$my->followees;
// Retrieves all users that follows me
$my->followers;
回答by JM4
In terms of general MySQL syntax, this is best written:
就一般 MySQL 语法而言，最好这样写：
SELECT * FROM USER a JOIN FOLLOWERS b ON (a.id = b.user_id) JOIN SHARES c on (b.follower_id = c.user_id) WHERE a.id = 3
will return a data set of all followers and their respective shares.
将返回所有关注者及其各自份额的数据集。
I believe you would want the following in Laravel
我相信你会在 Laravel 中想要以下内容
DB::table('USER')
->join('FOLLOWERS', 'USER.id', '=', 'FOLLOWERS.user_id')
->join('SHARES', 'FOLLOWERS.follower_id', '=', 'SHARES.user_id')
->where('USER.id', 3)
->get();
回答by svin
Instead of
代替
->where('shares.user_id', 'followers.follower_id')
It should be
它应该是
->whereRaw('shares.user_id=followers.follower_id')
because on the original example the 'followers.follower_id' is interpreted as a string.
因为在原始示例中，'followers.follower_id' 被解释为字符串。
回答by Mad an
$shares = DB::table('users')
->leftjoin('followers', 'users.user_id', '=', 'followers.follower_id')
->leftjoin('shares', 'shares.user_id', '=', 'users.id')
->where('users.id', 3)
->get();
Take a look
看一看
回答by Ak-Wahid
$data[shares] = DB::table('shares')
->leftjoin('followers', 'shares.user_id', '=', 'followers.follower_id')
->leftjoin('users', 'users.id', '=', 'users.id')
->where('users.id','=', 3)
->get();
to see results.
查看结果。
print_r($data[shares]);die;
for other query Simply give discription of your table
对于其他查询只需给出您的表的描述
