Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

erozas's avatar

'SQLSTATE[42S22]: Column not found: 1054 Unknown column

Hey what's up, I've been getting the following error when trying to attach or retrieve "courses" from "users".

I have a manyToMany relationship and I have a course_id pivot table.

The error I'm getting is:

Illuminate\Database\QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'courses.user_id' in 'where clause' (SQL: select * from `courses` where `courses`.`user_id` = 1 and `courses`.`user_id` is not null)'

I had an "enrollments" table that was acting as a pivot table but I decided to delete it so I deleted the migration and made "course_user" migration. I've tried various things like forcing the "course_user" on the model relationship, I've migrated:refresh several times. I also deleted the enrollments migration on the database.

My User, Course and migration files are as follow:

    public function up()
    {
        Schema::create('users', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('username');
            $table->string('email')->unique();
            $table->string('password', 60);
            $table->boolean('is_premium')->default(0);
            $table->boolean('is_confirmed')->default(0);
            $table->timestamp('activated_at');
            $table->string('confirmation_code', 80)->nullable();
            $table->tinyInteger('questions_left')->default(4);
            $table->rememberToken();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('users');
    }
    public function up()
    {
        Schema::create('courses', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('title');
            $table->string('description');
            $table->tinyInteger('lessons_count');
            $table->string('running_time');
            $table->integer('price');
            $table->boolean('is_free')->default(0);
            $table->boolean('is_available')->default(0);
            $table->boolean('is_complete')->default(0);
            $table->tinyInteger('completeness');
            $table->string('features');
            $table->string('course_url');
            $table->string('course_slug');
            $table->string('registration_url');
            $table->string('cover_image');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('courses');
    }

}
    public function up()
    {
        Schema::create('course_user', function(Blueprint $table)
        {
            $table->increments('id');
            $table->integer('course_id')->unsigned();
            $table->integer('user_id')->unsigned();
            $table->foreign('course_id')->references('id')->on('courses');
            $table->foreign('user_id')->references('id')->on('users');
            $table->timestamps();
        });

    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('course_user');
    }

}

The Models:

class User extends Model implements AuthenticatableContract, CanResetPasswordContract {

    use Authenticatable, CanResetPassword;


    protected $table = 'users';

    protected $fillable = ['username', 'email', 'password'];

    protected $hidden = ['password', 'remember_token'];


    /* Relaciones de Eloquent*/

    public function profiles()
    {
        return $this->hasOne('Profile');
    }

    public function roles()
    {
        return $this->belongsToMany('Role');
    }


    public function courses()
    {
        return $this->hasMany('Course');
    }


    /* Funciones propias */

    public function hasRole($name)
    {
        foreach($this->roles as $role)
        {
            if($role->name == $name) return true;
        }

        return false;
    }


}

class Course extends Model {

    protected $table = 'courses';

    protected $fillable = [];


    /* Relaciones de Eloquent */

    public function users()
    {
        return $this->belongsToMany('User');
    }


    public function lessons()
    {
        return $this->hasMany('Lesson');
    }

}

I can't seem to find the mistake. I've done composer dump-autoload a couple of times. I've tried changing the relations between the Models (between belongsToMany and hasMany.

Sorry for the long post but I've spent almost 3 hours trying to solve this and this is the last resource.

0 likes
24 replies
dberry's avatar

what's the code your calling that's giving you that error?

1 like
SachinAgarwal's avatar

@erozas there is nothing wrong with your code. The problem is you are trying to query the 'Course' table instead of 'course_user'. Show us how you are calling it. Then we might be able to help you.

1 like
erozas's avatar

I was just creating a user with auth/register and then doing the process to create the course with Tinker ($course->title etc). After that I try to attach $course to $user via $user->courses->attach(1), that failed, then I tried to do $user->courses->($course->save());

That's when I get the issue. I've tried manually editing the course_user database and it keeps returning the same issue.

SachinAgarwal's avatar

@erozas And this:

$user->courses->($course->save()

will not work for many to many relation because it doesn't perform pivot operations. attach, detach, sync performs pivot operations for many to many relations.
For one-to-many relations you can do it like that.

1 like
erozas's avatar

Thanks for the answer Sachin, however I did it both ways, the thing is it doesn't works neither ways.

dberry's avatar

@erozas Well in your User Model you've setup a return $this->hasMany('Course'); relationship, not a belongsToMany(). That is why it's looking for the user_id

2 likes
erozas's avatar

I just did it again:

  • Created user via registration
  • $course = new App\Course and then fill in the data and $course->save();
  • $user->courses()->attach(1) returns "BadMethodCallException with message 'Call to undefined method Illuminate\Database\Query\Builder::attach()'"

@dberry belongsToHasMany()? You lost me there hehe, I tried it and still got the BadMethodCallException

dberry's avatar

belongsToMany() not hasMany()

2 likes
erozas's avatar

@dberry, I tried both ways. I just did what you said (belongsToMany on User) and it returns the BadMethod exception. When I do $users->course->attach(1) I get:

Illuminate\Database\QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'courses.user_id' in 'where clause' (SQL: select * from courses where courses.user_id = 1 and courses.user_id is not null)'

Which is the same I was getting. I really think that the problem was the "enrollments" table. I did composer dump-autoload (cause I read it somewhere) and I've checked the code multiple times for typos and I can't find anything.

dberry's avatar

hasMany is not a many to many relationship... If the User hasMany Courses, it's going to look through the courses table and find all of the courses that has a user_id matching the user. hasMany === a one to many relationship.

You want a many to many relationship here, so use belongsToMany

3 likes
erozas's avatar

Thanks for that @dberry, the thing is that even I didn't fully understand the difference between hasMany and belongsToMany I tried both before posting this, I just tried doing belongsToMany on both Models and I'm getting:

BadMethodCallException with message 'Call to undefined method Illuminate\Database\Query\Builder::attach()'

SachinAgarwal's avatar

@erozas

user.php

 public function courses()
    {
        return $this->belongsToMany('Course', 'course_user'); 
    }

Course.php
 public function users()
    {
        return $this->belongsToMany('User', 'course_user');
    }
1 like
erozas's avatar

@SachinAgarwal That's what I have right now, I forced the pivot table too, even though it follows convention. I'm still getting the same BadMethod exception.

dberry's avatar

$user->courses()->attach(1)

Courses should be the method...

You are doing $user-.courses->attach(1) which is not correct.

1 like
erozas's avatar

@dberry and @SachinAgarwal thank you both for the help. I was doing both ($user->courses()->attach(1)) and ($user->courses->attach(1)) and getting the same results. I just exit Tinker, instantiated the objects again and got $user->courses()->attach(1) to work.

I tried everything you suggested me (even the belongsToMany relationship on both models although I didn't know the difference) before posting this and I still don't really know what I did wrong. Probably I was trying the $user->courses->attach() before doing $user->courses()->attach so that's the closes I got to a conclusion.

You've been both really helpful, thanks again and sorry to bother you with this kind of mistakes.

SachinAgarwal's avatar

@erozas how silly of me. I forgot. If you make any changes to your code, You have to restart the tinker. Other wise tinker will be in our previous code. i.e., after making it to belongsToMany(), you have to restart the tinker.

2 likes
erozas's avatar

Haha don't worry @SachinAgarwal if there's someone silly it's clearly me. Thanks for your quick answers and help. The same goes to @dberry. I don't know what time's over there, here's 05:19 AM and I was suffering a lot.

Fawwad's avatar

In my roles table primary key is rid not id . so how to use this with belongsToMany public function roles() { // you will need a role model return $this->belongsToMany('App\Role','users_roles','uid', 'rid'); }

Please or to participate in this conversation.