Rewriting SQL query in Laravel but getting error BadMethodCallException: Call to undefined method

Question

I'm trying to do this query in Laravel.

SELECT DISTINCT curriculum.Course_Code, transcript.Course_Grade, transcript.Course_Comp, transcript.CWID 
FROM curriculum 
LEFT JOIN transcript 
ON curriculum.Course_Code = transcript.Course_Code AND transcript.CWID = "C38475920";

Except I'm no longer using the static CWID ->"C38475920". This is what I have:

public function getProgress($id){
    return DB::table('curriculum')
    ->select('curriculum.Course_Code','transcript.Course_Comp', 'transcript.Term_Completed', 'transcript.Course_Grade')
    ->distinct('curriculum.Course_Code')
    ->leftJoin('transcript', 'curriculum.Course_Code','=','transcript.Course_Code')
    ->on('CWID', '=', $id)
    ->get();
  }

The function gives this error BadMethodCallException: Call to undefined method Illuminate\Database\Query\Builder::on() in file

Do I have to add something to my model to use ON?

Answer 1

If you would like to use a "where" style clause on your joins, you may use the where methods on a join. Instead of comparing two columns, these methods will compare the column against a value.

The selectRaw method can be used in place of select(DB::raw(...)). This method accepts an optional array of bindings as its second argument.

DB::table('curriculum')
      ->selectRaw('DISTINCT curriculum.Course_Code, transcript.Course_Grade, transcript.Course_Comp, transcript.CWID')
      ->leftJoin('transcript', function ($leftJoin) use ($id) {
            $leftJoin->on('curriculum.Course_Code', '=', 'transcript.Course_Code')
                 ->where('transcript.CWID', '=', $id);
        })
      ->get();