CODEWITHSUNDEEP
phpmysqlsqllaraveleloquent
Kenta
Kenta

Reputation: 391

Laravel 5 - compare query from one table to another query

I have two tables: a relationship table and a users table.

Relationship table looks like: 'user_one_id', 'user_two_id', 'status', 'action_user_id'.

Users table looks like: 'id', 'username'.

I would like to query the relationship table first and return an array of all the rows where the 'status' column = 0.

Then I would like to query the users table and return an array of ids and usernames where 'user_one_id' matches 'id'.

My code so far: 

public function viewRequests()
{
    $currentUser = JWTAuth::parseToken()->authenticate();

    $friendRequests = DB::table('relationships')
        ->where('user_two_id', '=', $currentUser->id)
        ->where('status', '=', '0')
        ->get();

    $requestWithUsername = DB::table('users')
        ->where('id', '=', $friendRequests->user_one_id)
        ->get();

    return $requestWithUsername;

}

It's not working and I'm not sure what method is easiest to reach my desired output. How can I change these queries?

EDIT:

After reviewing the response, this is the working code:

$friendRequests = DB::table('users')
        ->select('users.id','users.username')
        ->join('relationships', 'relationships.user_one_id','=','users.id')
        ->where('relationships.status','=',0)
        ->where('relationships.user_two_id', '=', $currentUser->id)
        ->get();

Upvotes: 0

Views: 2419

Answers (1)

Felippe Duarte
Felippe Duarte

Reputation: 15131

Your SQL seems to be this:

SELECT id, username
  FROM users
  JOIN relationships
    ON relationships.user_one_id = id
 WHERE relationships.status = 0

Then the Laravel way:

DB::table('users')
  ->select('id','username')
  ->join('relationships', 'relationships.user_one_id','=','id')
  ->where('relationships.status','=',0)
  ->get();

Upvotes: 1

Related Questions