Table column name is incorrect

Hi,

Pretty new to Laravel, but really enjoying the learning experience and Laracasts.

I am following along with the 30 day course. However, I'm creating tables for things I actually wish to create.

I have run into an issue, that I am hoping to get some guidance on. My issue appears to be with the naming of the columns in my database, specifically when I am generating a factory.

When I run App\Models\Testimonials::factory(5)->create() from with tinker. I get the following error.

 Illuminate\Database\QueryException  SQLSTATE[42S22]: Column not found: 1054 Unknown column 'member_id' in 'field list' (Connection: mysql, SQL

Within my migration file I have…

return new class extends Migration {
    public function up()
    {
        Schema::create('testimonials', function (Blueprint $table) {
            $table->id();
            $table->foreignIdFor(\App\Models\Members::class);
            $table->text('testimonial');
            $table->dateTime('submitted');
            $table->timestamps();
        });
    }

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

But, within my database the related table column name is members_id. I am assuming this is the problem. However, I cannot work out why the column is named members_id instead of member_id.

Some thoughts would be very welcome. I have tried dropping the table and migrate:fresh, but the members_id column just comes back.

LaryAI

2 years ago

Level 58

The issue you're encountering is due to the naming convention used by the foreignIdFor method in your migration. By default, foreignIdFor will generate a column name based on the plural form of the related model's name, which is why you're seeing members_id instead of member_id.

To resolve this, you can explicitly specify the column name in your migration. Here's how you can modify your migration file:

return new class extends Migration {
    public function up()
    {
        Schema::create('testimonials', function (Blueprint $table) {
            $table->id();
            $table->foreignId('member_id')->constrained('members');
            $table->text('testimonial');
            $table->dateTime('submitted');
            $table->timestamps();
        });
    }

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

In this updated migration:

$table->foreignId('member_id')->constrained('members'); explicitly sets the column name to member_id and establishes a foreign key constraint to the members table.

After updating your migration file, you should run the following commands to refresh your database:

php artisan migrate:fresh

This will drop all tables and re-run all migrations, ensuring that the testimonials table is created with the correct column name.

Now, when you run App\Models\Testimonials::factory(5)->create() in Tinker, it should work without any issues related to the column name.

1 like

Snapey

2 years ago

Best Answer

Level 122

Lary gets the right answer but the solution is wrong.

The correct answer is that your models should always be named singular and the tables plural.

The table contains lots of members, but the model only represents a single member - always

So your migration should be $table->foreignIdFor(\App\Models\Member::class); and of course renaming Members class to Member and filename to Member.php then there would not be an issue and you would not need to implement workarounds.

And of course your testimonials should be using model Testimonial.

admindoddle

2 years ago

Level 3

@Snapey Thank you for the response, looks like I have some refactoring to do!

admindoddle

2 years ago

Level 3

Hi,

After refactoring I am now running into some related issues.

My Testimonials migration now looks as such…

        Schema::create('testimonials', function (Blueprint $table) {
            $table->id();
            $table->foreignIdFor(\App\Models\Member::class);
            $table->text('testimonials');
            $table->dateTime('submissions');
            $table->timestamps();
        });

However, after running App\Models\Testimonial::factory(8)->create(), I am running into this error…

Illuminate\Database\QueryException  SQLSTATE[42S22]: Column not found: 1054 Unknown column 'member_ids' in 'field list' (Connection: mysql, SQL: insert into `testimonials` (`created_at`, `updated_at`, `testimonials`, `submissions`, `member_ids`) values (2024-05-18 12:43:52, 2024-05-18 12:43:52, Sint dolor accusantium ut enim numquam doloribus maiores cumque. Enim repellendus repellat maxime ipsa ut. In adipisci omnis laudantium dolor eligendi in., 2024-05-18 12:43:52, 42)).

The related Factory looks as such…

    public function definition(): array
    {
        return [
            'created_at' => Carbon::now(),
            'updated_at' => Carbon::now(),
            'testimonials' => $this->faker->paragraph(),
            'submissions' => Carbon::now(),

            'member_ids' => Member::factory(),
        ];
    }

And member_ids exists in the members table

Snapey

2 years ago

Level 122

And member_ids exists in the members table

Why? it should be member_id ?

Please or to participate in this conversation.