Laravel Eloquent Update Column not found: 1054 Unknown column 'id' in 'where clause'

Question

I never had any problems with Laravel but i ran into something what is really strange.

I have a one to one relation storing users metadata in a seperate table.

my Controller update looks like this

$val = Validator::make(Input::all(), $rules);

        if($val->passes())
        {
            $this->cur_user->username             = Input::get('username');
            $this->cur_user->metadata->first_name = Input::get('first_name');
            $this->cur_user->metadata->last_name  = Input::get('last_name');
            $this->cur_user->metadata->gender     = Input::get('gender');
            $this->cur_user->metadata->location   = Input::get('location');
            $this->cur_user->email                = Input::get('email');
            $this->cur_user->metadata->dob        = Input::get('dob');
            $this->cur_user->metadata->about      = Input::get('about');

            if ($this->cur_user->save() && $this->cur_user->metadata->save()) 
            {
               $data = array('msg' => 'success');
            }

Relations

Users Model

class User extends Eloquent implements UserInterface, RemindableInterface {

    /**
     * The database table used by the model.
     *
     * @var string
     */
    protected $table = 'users';

    /**
     * The attributes excluded from the model's JSON form.
     *
     * @var array
     */
    protected $hidden = array('password');

    /**
     * Get the unique identifier for the user.
     *
     * @return mixed
     */
    public function getAuthIdentifier()
    {
        return $this->getKey();
    }

    /**
     * Get the password for the user.
     *
     * @return string
     */
    public function getAuthPassword()
    {
        return $this->password;
    }

    /**
     * Get the e-mail address where password reminders are sent.
     *
     * @return string
     */
    public function getReminderEmail()
    {
        return $this->email;
    }


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


}

Metadata Model

class Metadata extends Eloquent {

    public $timestamps = false;

    protected $table = "users_metadata";

    public function user()
    {
        return $this->belongsTo('user');
    }
}

What is really weird, if i leave the $this->cur_user->metadata->gender = Input::get('gender');and $this->cur_user->metadata->about = Input::get('about'); in my validation i get the following error

{"error":{"type":"Exception","message":"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id' in 'where clause' (SQL: update `users_metadata` set `gender` = ?, `about` = ? where `id` is null) (Bindings: array (\n  0 => '1',\n  1 => 'testing one 2 3',\n))","file":"C:\\BitNami\\apache2\\htdocs\\laravel\\vendor\\laravel\\framework\\src\\Illuminate\\Database\\Connection.php","line":555}}

If i comment it out, all fine and works well, checked everything i do not see any strange things.

Could please someone give me a hint? (searched around found some answers but not really for my problem)

score 2 · Accepted Answer · edited May 23 '17 at 12:30

Sounds like your user_metadata table does not contain a foreign key to the users table, which it is requiring to be able to connect a user and his/her metadata. It also looks like the column is expected to be named id.

It would be helpful if you could post your database structure, or even better: Your migration files. I could venture a suggestion though:

Provided that you have an auto incrementing id column in your users table, your user_metadata migration file could contain something like this:

$table->integer('user_id')->unsigned();
$table->primary('user_id');
$table->foreign('user_id')
    ->references('id')->on('users')
    ->onDelete('cascade');

Perhaps you are missing some parts in your setup?

In other words, user_metadata.user_id should be an identifying foreign key, reflecting users.id. And when a user is deleted, his/her metadata should automatically be deleted too (unless you're using for instance MyISAM, which that does not support foreign key relations).

See also the manual for more info on Laravel/Eloquent foreign keys: http://four.laravel.com/docs/schema#foreign-keys

now i feel really dumb, maybe this is the result for coding late night, insted of $primaryKey i defined $key in the users_metadata model, ah man still thanks for the reply — , Aug 08 '13 at 11:51

score 0 · Answer 2 · answered Jun 17 '22 at 03:12

Just wanted to add to this since I've run into this problem and couldn't find a solution.

updateOrCreate will try to find a record with the default id column. However, if your table has a different primary key column instead of the default, you'll have to define the primary key column in your model file like so:

protected $primaryKey = '<your column name>'

Laravel Eloquent Update Column not found: 1054 Unknown column 'id' in 'where clause'

2 Answers2