Laravel迁移显示现有列不存在

Question

$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'); 

我已经在迁移的up方法中编写了上面的代码，但是当运行迁移命令时，它显示了-

Illuminate\Database\QueryException 
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".

课程表迁移文件

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();        
            });

我不知道为什么会出现这个错误，因为我在我的“课程”表迁移中有userId列，任何帮助都是非常感谢的。

编辑

输出：

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

视图查询

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       
      )
    ");

模型

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'];
}

控制器

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use App\Models\CourseMarketView;

class CourseMarketController extends Controller
{
    public function getcoursebysubjectID(){

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

Answer 1

问题A.“列course.userid不存在”

使用

提示:也许您的意思是引用列"course.userId“

这是通过对字段名使用"来解决的。

$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'); 

• 单引号用于围绕字段名

查询

• 双引号

问题B. id-fields

它不是从course.id、skills.id和subjects.id列中获取数据

问题是有4个字段被命名为id。

要修复它-使用字段别名：

$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'); 

这样，id将变成4个不同的领域，因为它实际上是这样的：

• user_id -来自users
• course_id表-来自course
• skill_id -来自skills
• subject_id -来自“`subjects

”

相应地将应用程序代码更改为字段重命名。

Answer 2

暗示是自己说的。您没有列userid

你桌子上的是userId