Laravel join with 3 Tables

database, join, laravel, mysql, sql

Solution

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;

Problem

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

Original source