how to solve SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id' in 'where clause'

1
votes

I'm working on database Eloquent Relationship One to one but after writing code, I'm getting an error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'posts.deleted_at' in 'where clause' (SQL: select * from `posts` where `posts`.`user_id` = 1 and `posts`.`user_id` is not null and `posts`.`deleted_at` is null limit 1)

this code is for post table where I linked the user_id

Post table migration

class CreatePostTable extends Migration
{
   
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('user_id')->unsigned();
            $table->string('title');
            $table->text('body');
            $table->timestamps();
        });
    }

  
    public function down()
    {
        Schema::dropIfExists('posts');
    }
}

this is the user table where I have to link the post

User model migration

class CreateUsersTable extends Migration
{
    
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }


    public function down()
    {
        Schema::dropIfExists('users');
    }
}

User.php code


 
public function post(){
   return $this->hasOne('App\Post'); //select * from post where user_id = 1
    
}

and this is the route:

Routes\web.php

 Route::get('user/{id}/post', function ($id) {
     

    return User::find($id)->post;
 });
2
phpmysqleloquentlaravel-7
Check table's structure directly (via CLI or raw SQL) for correct column name. PS. where `posts`.`user_id` = 1 and `posts`.`user_id` is not null - if 1st condition is true then 2nd is true too - so 2nd condition is excess. – Akina
What is the primary key for user table? – Senthilnadhan Ramasamy
Primary key for user table is 'id=1' – Ausaf Liaquat
have you tried stackoverflow.com/questions/48478645/… – Senthilnadhan Ramasamy
Thank you I tried it and boom it solved my problem @SenthilnadhanRamasamy – Ausaf Liaquat

2 Answers

0
votes

You haven't created a deleted_at column in the posts table. If you create a deleted_at column in the posts table, then it will work.

0
votes

Your Post model probably uses Trait SoftDeletes which adds to query clause deleted_at is null when you call eloquent methods.

You have 2 solution for that:

  1. Add to your migration (Create new migration), with $table->softDeletes(); which adds that column - deleted_at.
  2. Remove Trait SoftDeletes from Post model.