Laravel加入3桌 [英] Laravel join with 3 Tables
问题描述
我正在建立一个类似Twitter的应用程式。有一个Feed,我只想显示我关注的用户的帖子。
我尝试了所有的连接,但没有什么似乎工作。
我有3个表:用户
,关注者
,
/ strong>:
id
关注者: user_id
, follower_id
分享: user_id
我需要获得的是ALL Shares WHERE share.user_id = followers.follower_id
ANDWHERE关注者。 user_id = users.id
假设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();
但它不工作。
帮助:)
解决方案我相信你的加入是错误的:
< pre class =lang-php prettyprint-override> $ shares = DB :: table('shares')
- > join('users','users.id' =','shares.user_id')
- > join('followers','followers.user_id','=','users.id')
- > where follower_id','=',3)
- > get();
我也建议你把你的表命名为后面
c>用户通过以下
和有很多追随者,因此感觉有点更自然。 c >。
示例
$ shares = DB :: table('shares')
- > join('users','users.id','=','shares.user_id')
- > join('follows','follows.user_id','=','users.id')
- > where('follows.follower_id','=',3)
- > get();
模型方法
实现你使用 DB ::
查询而不是模型。所以我修复答案,并提供了更多的清晰度。我建议你使用模型,对于那些以框架和特别的SQL开头的人来说,这很容易。
模型示例
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扩展模型{
public function user(){
return $ this-> belongsTo('User');
}
}
模型用法示例:
$ my = User :: find('my_id');
//按照我的用户检索所有共享
//希望加载共享的所有者
$ shares = Share :: with('user')
- > join('follows','follows.user_id','=','shares.user_id')
- > where('follows.follower_id','=',$ my- > id)
- > get('shares。*'); //注意共享。* here
//输出共享内容的用户名
foreach($ shares为$ share){
echo $ share-> user-> username;
}
//检索所有正在关注的用户
$ my-> followees;
//检索跟随我的所有用户
$ my-> followers;
I am building a Twitter-like app. There is a Feed in which I want to only show posts of Users who I follow.
I tried everything with joins, but nothing seems to work.
I have 3 tables: Users
, Followers
, Shares
The Tables look like this:
Users: id
Followers: user_id
, follower_id
Shares: user_id
What I need to get is "ALL Shares WHERE share.user_id = followers.follower_id"
"ANDWHERE followers.user_id = users.id"
Assume, the users.id is 3, I tried this:
$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 :)
解决方案 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 follows
instead, it feels a bit more natural to say user has many followers through follows
and 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.
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;
这篇关于Laravel加入3桌的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!