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

EverSQL Database Performance Knowledge Base

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

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?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `transcript` ADD INDEX `transcript_idx_cwid_course_code` (`CWID`,`Course_Code`);
The optimized query:
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'

Related Articles



* original question posted on StackOverflow here.