CODEWITHSUNDEEP
mysqlsqldatabasejoinlaravel
Plic Pl
Plic Pl

Reputation: 541

Laravel join with 3 Tables

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

Upvotes: 33

Views: 128005

Answers (5)

Mad an
Mad an

Reputation: 159

First of all, go through users table and join it with followers table, and then with share table to get corresponding shares data of only followers. Here is the code

 $shares = DB::table('users')
->leftjoin('followers', 'users.user_id', '=', 'followers.follower_id')
->leftjoin('shares', 'shares.user_id', '=', 'users.id')
->where('users.id', 3)
->get();

Upvotes: 0

svin
svin

Reputation: 149

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.

Upvotes: 1

Ak-Wahid
Ak-Wahid

Reputation: 67

$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

Upvotes: 0

vFragosop
vFragosop

Reputation: 5773

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;

Upvotes: 58

JM4
JM4

Reputation: 6788

In terms of general MySQL syntax, this is best written:

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

DB::table('USER')
  ->join('FOLLOWERS', 'USER.id', '=', 'FOLLOWERS.user_id')
  ->join('SHARES', 'FOLLOWERS.follower_id', '=', 'SHARES.user_id')
  ->where('USER.id', 3)
  ->get();

Upvotes: 1

Related Questions