Laravel 5.4 subquery in where condition?

Question

I want to write sub query inside where in condition and in subquery were condition, checking with parent query field.

as follows,

$query = DB::table('users');
$query->whereNotIn('users.id', function($query) use ($request) {
            $query->select('award_user.user_id')
                    ->from('award_user')
                    ->where('award_user.user_id', 'users.id')
                    ->where('award_user.award_id', $request->award_id);
        });

Query is working fine, but

 ->where('award_user.user_id', 'users.id')

This line, users.id is not taking from parent query. If I enter manually number, then it is working correctly.

What is wrong with my query.. can you please suggest.

Answer 1

use whereRaw rather than where

$query = DB::table('users');
$query->whereNotIn('users.id', function($query) use ($request) {
            $query->select('award_user.user_id')
                    ->from('award_user')
                    ->whereRaw('award_user.user_id', 'users.id')
                    ->whereRaw('award_user.award_id = '.$request->award_id);
        });