Abdallah Sakre Abdallah Sakre - 5 months ago 7
SQL Question

syntax to join a table twice in laravel

I'm trying to join the below 3 tables. The issue is in the part where I want to join posts.id to the shares.post_id using 'AND' operator which is not correct. What is the correct syntax to do that ?

$sharedPosts = DB::table('shares')
->join('users', 'users.id', '=', 'shares.user_id')
->join('posts', 'posts.user_id', '=', 'users.id' , 'AND' , 'posts.id', '=', 'shares.post_id')
->where('shares.user_id', '=', $myFriends)

->get();

Answer

Try aliasing:

$sharedPosts = DB::table('shares')
            ->join('users', 'users.id', '=', 'shares.user_id')
            ->join('posts as p1', 'p1.user_id', '=', 'users.id' , 'AND' , 'p1.id', '=', 'shares.post_id')
            ->join('posts as p2', 'p2.id', '=', 'shares.post_id')
            ->where('shares.user_id', '=', $myFriends)
         ->get();

You cannot join a table twice with no alias.

Although if you have 2 clauses in your join, I think you ought to do as in the documentation :

$sharedPosts = DB::table('shares')
    ->join('users', 'users.id', '=', 'shares.user_id')
    ->join('posts as p1', , function($join){
        $join->on('p1.user_id', '=', 'users.id')
             ->on('p1.id', '=', 'shares.post_id')
    })
    ->join('posts as p2', 'p2.id', '=', 'shares.post_id')
    ->where('shares.user_id', '=', $myFriends)
->get();

Edit (with one posts join):

$sharedPosts = DB::table('shares')
    ->join('users', 'users.id', '=', 'shares.user_id')
    ->join('posts', , function($join){
        $join->on('posts.user_id', '=', 'users.id')
             ->on('posts.id', '=', 'shares.post_id')
    })
    ->where('shares.user_id', '=', $myFriends)
->get();