Can't insert into pivot table

Question

I have a pivot table in my database that combines users and loans. The table is the following.

user_id
loan_id
amount

Now I have the following code to insert data into the database

    $user = User::find(2);
    $user->loans()->create([
        // Some database insertion
    ]);

    $user->loans()->attach(2, ['amount' => '500']);

And I am getting this error...

Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1364 Field 'amount' doesn't have a default value (SQL: insert into `loan_user` (`loan_id`, `user_id`) values (42, 2)) in file G:\Development\final-project\backend\vendor\laravel\framework\src\Illuminate\Database\Connection.php

My loans and users have a many-to-many relationship and I also have the pivot table named loan_user. What am I doing wrong here?

Edit

Here's the migration of my pivot table

    Schema::create('loan_user', static function (Blueprint $table) {
        $table->id();
        $table->unsignedBigInteger('loan_id');
        $table->unsignedBigInteger('user_id');
        $table->integer('amount');
        $table->timestamps();
    });

Something Important

I made that amount column nullable. Then it was not throwing any error but sometimes the amount is 500 and sometimes it's null. I have no idea what's going on.

Solution

I solved this by creating the loan first and then attaching it to the user and pivot amount. Something like this.

    $loan = Loan::create([
        // something
    ]);

    $user->loan()->attach(['amount' => 500]);

Answer 1

You are trying to create a record via a many-to-many relationship which is why it is failing. Instead, you can create the loan separately and attach with something like this:

$loan = Loan::create([
    // data
]);

$user->loans()->attach(
    $loan, [
        'amount' => 500,
    ]
);

Also, your loans relationship should look something like this:

public function loans(): BelongsToMany
{
    return $this->belongsToMany(Loan::class)
        ->withPivot('amount')
        ->withTimestamps();
}

This will add the amount to pivot data when querying.

Refs: https://laravel.com/docs/eloquent-relationships#attaching-detaching