CODEWITHSUNDEEP
mysqlsqllaraveleloquentleft-join
Muhammad Khan
Muhammad Khan

Reputation: 107

Laravel Eloquent query for left join

I have two tables available. 1) Users 2) Friendships. Following is the table structure:

  1. Users Table have following columns
  1. Friendships table have following columns

Now I want to retrieve data from the "users" table using the "sender_id," which is the same as the "id" in the "users" table.

I'm using the left join to perform the required query as follow:

public function show_friend_request(){

        $user_id = auth()->user()->id;

        **//Below query will get the userID's of people who have sent the request to current user.**

        $get_friend_requests = DB::table('friendships')->where('recipient_id',$user_id)
        ->where('status','pending')->get(['sender_id'])->first()->sender_id;
      
        $show_friend_request_Data = DB::table('users')
        ->leftJoin('friendships', 'sender_id', '=', $get_friend_requests)
        ->get();

       return $show_friend_request_Data ;
     
    }

But when i executed the above code i'm getting the following error:

    Illuminate \ Database \ QueryException
    SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.sender_id' in 'on clause' 
select * from `users` left join `friendships` on `users`.`sender_id` = `63b8760f-826b-4c1c-aea9-23f1dff148db`

Upvotes: 0

Views: 774

Answers (1)

Bfcm
Bfcm

Reputation: 2746

All you have to do is:

   $show_friend_request_Data = DB::table('users')
    ->leftJoin('friendships', 'friendships.sender_id', '=', 'users.id')
    ->where('users.id', '=', $get_friend_requests)
    ->get();

I also suggest you to think of how to optimise the whole query to only 1 request and also how to get multiple friend requests at the time. Because now you're only getting the first friend pending friend request ;)

Upvotes: 1

Related Questions