0

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?

DelB95
  • 37
  • 5

1 Answers1

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();
Nick
  • 536
  • 2
  • 8
  • Alright nice! But I'm getting `BadMethodCallException: Call to undefined method Illuminate\Database\Query\Builder::selectRow()` I'm having trouble figuring out how to fix these badMethodCallExceptions – DelB95 Apr 02 '20 at 11:26
  • need to use the method - selectRaw – Nick Apr 02 '20 at 11:27