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

Ideally, you should be retrieving the data you seek in reverse order of your current setup. You would not find all shares and then go backwards. First find your followers, then the shares those followers have. It may be irrelevant in some regards but it definitely makes more sense syntactically. — JM4
– JM4, Commented Aug 22, 2013 at 20:20

vFragosop · Accepted Answer · 2013-08-22 20:59:00Z

58

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;

edited Aug 22, 2013 at 20:59

answered Aug 22, 2013 at 19:20

vFragosop

5,7731 gold badge31 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

Plic Pl Over a year ago

Hm, for me it seems right, but somehow it doesnt work. I changed followers.user_id to followers.follower_id and followers.follower_id to followers.user_id, but now it returns just all shares

vFragosop Over a year ago

Are you storing the id of the user being followed on followers.user_id, or are you storing the id of the user following? And are you using leftjoin or join? Also make sure you are including the = on the where clause.

Plic Pl Over a year ago

followers.user_id is the user who is following. follower.follower_id is therefore the id of the user who is followed. I am using join, and i think the "=" is default if not set, but i have set it now

vFragosop Over a year ago

It should be the inverse, at least english speaking.

Plic Pl Over a year ago

Ye, i have some troubles with the naming .. So I will do it as you said, the table "Follows" and the "follower_id" is the user who follows the "user_id". Is this right?

|

JM4 · Accepted Answer · 2013-08-22 20:25:25Z

1

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();

edited Aug 22, 2013 at 20:25

answered Aug 22, 2013 at 20:18

JM4

6,80819 gold badges80 silver badges130 bronze badges

Comments

svin · Accepted Answer · 2017-05-25 20:29:13Z

1

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.

answered May 25, 2017 at 20:29

svin

1491 silver badge3 bronze badges

1 Comment

Nate S Over a year ago

This worked for me... I had an issue with additional duplicate records, that didn't exist, popping up on my join. I had one day record that referenced a rate_id and a user_id and I wanted to show data from days, rates and users. I kept getting two days popping up, one of which didn't exist, because there was a second rate attached to the user but no day for it. I call the position_user relationship rates for better understanding in my software. ->whereRaw('days.rate_id = position_user.id')

Ak-Wahid · Accepted Answer · 2016-04-06 09:27:05Z

0

$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

answered Apr 6, 2016 at 9:27

Ak-Wahid

673 bronze badges

Comments

Mad an · Accepted Answer · 2022-06-12 12:06:36Z

0

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();

edited Jun 12, 2022 at 12:06

answered Nov 22, 2018 at 19:59

Mad an

15911 bronze badges

2 Comments

Wayne Phipps Over a year ago

You should edit your answer and describe how or why it answers the question

Mad an Over a year ago

I have updated the answer. Upvote if it deserves

Collectives™ on Stack Overflow

Laravel join with 3 Tables

5 Answers 5

Model approach

15 Comments

Comments

1 Comment

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Model approach

15 Comments

Comments

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related