SQLSTATE[HY000]: General error: 1 table posts has no column named *

Question

I'm actually new to Laravel and I'm trying to build a basic social network with this Framework. For this project, I created a page called post which users can add new posts. So I tried creating the posts table like this:

public function up()
{
    Schema::create('posts', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->unsignedBigInteger('user_id');
        $table->text('caption');
        $table->string('image');
        $table->timestamps();
        $table->index('user_id');
    });
}

And on User.php model:

public function posts()
{
    return $this->hasMany(Post::class);
}

And the Post.php which extends the model:

class Post extends Model
{
    protected $guarded = [];

    Public function user()
    {
        return $this->belongsTo(User::class);
    }
}

And the controller which is named PostsController.php goes like this:

class PostsController extends Controller
{
    public function create()
    {
        return view('posts.create');
    }
    public function store()
    {
        $data = request()->validate([
            'caption' => 'required',
            'image' => ['required','image'],
        ]);

        auth()->user()->posts()->create($data);

        dd(request()->all());
    }
}

And here is the create.blade.php under the posts folder at resources dir:

@extends('layouts.app')

@section('content')
<div class="container">
    <form action="/p" enctype="multipart/form-data" method="post">
        @csrf
        <div class="row">
            <div class="col-8 offset-2">
                <div class="row">
                    <h1>Add New Post</h1>
                </div>
                <div class="form-group row">
                    <label for="caption" class="col-md-4 col-form-label">Post Caption</label>

                  
                    <input id="caption" 
                    type="text" 
                    class="form-control @error('caption') is-invalid @enderror" 
                    name="caption" 
                    value="{{ old('caption') }}" 
                    autocomplete="caption" autofocus>

                    @error('caption')
                        <span class="invalid-feedback" role="alert">
                            <strong>{{ $message }}</strong>
                        </span>
                    @enderror
                 
                </div>
                <div class="row">
                    <label for="image" class="col-md-4 col-form-label">Post Image</label> 
                    <input type="file" class="form-control-file" id="image" name="image">
                    @error('image')
                        <strong>{{ $message }}</strong>
                    @enderror
                </div>
                <div class="row pt-4">
                    <button class="btn btn-primary">Add New Post</button>
                </div>
            </div>
        </div>
    </form>
</div>
@endsection

And if you want to take a look at routes.php here it is:

Auth::routes();

Route::get('/p/create','PostsController@create');
Route::post('/p','PostsController@store'); 

Route::get('/profile/{user}', 'ProfilesController@index')->name('profile.show');

So everything looks nice and clean but the problem is whenever I try to upload some dummy pictures with a caption, I see this error:

SQLSTATE[HY000]: General error: 1 table posts has no column named caption

However I tried to run php artisan migrate syntax on CMD to check if the database migration missed anything or not, and it outputs: Nothing to migrate!

So if you know why this problem occurs or how can I fix this, please let me know, I would really appreciate that!

Thanks in advance.

Answer 1

In your migration you need to create the relationship, like this :

public function up()
{
    Schema::create('posts', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->unsignedBigInteger('user_id');
        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
        $table->text('caption');
        $table->string('image');
        $table->timestamps();
        // $table->index('user_id');
    });
}

Error : You make a column for foreign key, $table->unsignedBigInteger('user_id');
But you didn't define it as a foreign key with a relationship, you need to add this line for make a relation with users table as, $table->foreign('user_id')->references('id')->on('userses')->onDelete('cascade');

Laravel 7 added Foreign Key Constraints method, in this method you can define a foreign key with one line code, as :

$table->foreignId('user_id')->constrained();

This is same as :

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

The foreignId method is an alias for unsignedBigInteger while the constrained method will use convention to determine the table and column name being referenced. If your table name does not match the convention, you may specify the table name by passing it as an argument to the constrained method:

Note : MySQL makes foreign key as an index automatically, so you don't need to define , so I delete this line $table->index('user_id'); You can find more about here.

Now, if you run php artisan migrate you will get the same Nothing to migrate! , because you already migrated all tables according to your migrations, and laravel saved all your migrated files name on a table called migrations. You need to run php artisan migrate:refresh, this command will delete your all previous table and make new table.

Answer 2

class Post extends Model
{
    protected $guarded = [];

    Public function user()
    {
        return $this->belongsTo(User::class?'user_id', 'id');
    }
}

You can try to specify the corresponding foreign key, maybe your foreign key does not specify a pair, or you can provide your User's Model to have a look

Answer 3

i had the same probleme and i fixed it with this commande even if it will delete ur data in ur database but it will work

php artisan migrate:refresh 

Answer 4

I have faced this error before and all I did was run this command:

php artisan migrate:refresh