Laravel join with 3 Tables

Question

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 :)

Answer 1

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;