Laravel migration shows column doesn’t exist for existing columns

$users=DB::select('SELECT 
      users.id,
      users.name,
      course.id,
      course."courseUniqueName",
      skills.id,
      skills.skill,
      subjects.id,
      subjects.subject
       FROM users 
        LEFT JOIN course ON course."userId"=users.id
        LEFT JOIN skills ON skills."userId"=users.id
        LEFT JOIN subjects ON subjects."userId"= users.id'); 

I’ve written the above code in the up method of my migration but when running migrate command, it shows –

IlluminateDatabaseQueryException 
SQLSTATE[42703]: Undefined column: 7 ERROR:  column course.userid does not exist at character 294
HINT:  Perhaps you meant to reference the column "course.userId".

Course table migration file

Schema::create('course', function (Blueprint $table) {
                $table->id();
                $table->bigInteger('userId')->unsigned();
                $table->foreign('userId')->references('id')->on('users');
                $table->string('courseDisplayName');
                $table->string('courseUniqueName')->unique();
                $table->string('courseAddedBy');
                $table->dateTime('createdOn');
                $table->dateTime('lastUpdated');
                $table->softDeletes();        
            });

I don’t have any idea why this error occurs because i’ve the userId column in my ‘course` table migration. Any help is much appreciated.

EDIT

Output:

"id": 1,
 "name": "weeee",
 "courseUniqueName": "Frehers course",
 "skill": "swim",
 "subject": "IELTS"

View Query

DB::statement("
      CREATE VIEW course_market_view AS
      (
        SELECT 
          users.id as user_id,
          users.name,
          course.id as course_id,
          course.courseUniqueName,
          skills.id as skill_id,
          skills.skill,
          subjects.id as subject_id,
          subjects.subject
           FROM users 
            LEFT JOIN course ON course.'userId'=users.id
            LEFT JOIN skills ON skills.'userId'=users.id
            LEFT JOIN subjects ON subjects.'userId'= users.id       
      )
    ");

Model

class CourseMarketView extends Model
{
    use HasFactory;
    protected $table = 'course_market_view';
    protected $fillable = ['user_id','name','course_id','courseUniqueName','skill','skill_id','subject_id','subject'];
}

Controller

<?php

namespace AppHttpControllers;

use IlluminateSupportFacadesDB;
use AppModelsCourseMarketView;

class CourseMarketController extends Controller
{
    public function getcoursebysubjectID(){

        $users = CourseMarketView::all();
        dd($users);
    }
}

Answer

Problem A. “column course.userid does not exist”

With

HINT: Perhaps you meant to reference the column “course.userId”

This was solved by using " for field names with uppercased letters.

$users=DB::select('SELECT 
      users.id,
      users.name,
      course.id,
      course."courseUniqueName",
      skills.id,
      skills.skill,
      subjects.id,
      subjects.subject
       FROM users 
        LEFT JOIN course ON course."userId"=users.id
        LEFT JOIN skills ON skills."userId"=users.id
        LEFT JOIN subjects ON subjects."userId"= users.id'); 

• Single quotes for query
• double quotes around fields names

Problem B. id-fields

it’s not fetching the data from the columns course.id,skills.id & subjects.id

The problem is that 4 fields are named as id.

To fix it – use field aliases:

$users=DB::select('SELECT 
      users.id as user_id, # note rename of the field here
      users.name,
      course.id as course_id, # and here
      course."courseUniqueName",
      skills.id as skill_id,  # and here
      skills.skill,
      subjects.id as subject_id,  # and here
      subjects.subject
       FROM users 
        LEFT JOIN course ON course."userId"=users.id
        LEFT JOIN skills ON skills."userId"=users.id
        LEFT JOIN subjects ON subjects."userId"= users.id'); 

This way id will became 4 different fields as it’s in reality:

• user_id – from table users
• course_id – from course
• skill_id – from skills
• subject_id – from `subjects

Change application code accordingly to field renames.