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]);

My first question why do you use column `amount` in pivot table? — A.A Noman, Mar 26 '21 at 07:03
multiple users can have multiple amounts in multiple loans. Let's say, five users are involved in a loan with a specified amount, the user can be a lender or borrower but that user might also be involved in other loans. — Pranta, Mar 26 '21 at 07:05

A.A Noman · Answer 1 · 2021-03-26T07:15:08.117

3

You have to use like this

return $this->belongsToMany('App\Loan', 'amount')->withPivot('amount');

and If you insert multiple rows at a time then you have to use like this

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

For More details

edited Mar 26 '21 at 07:15

answered Mar 26 '21 at 06:28

A.A Noman

5,244
9
24
46

The problem here is that my code doesn't reach the line where I am attaching, It fails when creating the loan – Pranta Mar 26 '21 at 06:57
Have any column `amount` in your pivot table?? – A.A Noman Mar 26 '21 at 06:57

P. K. Tharindu · Accepted Answer · 2021-03-26T07:14:38.067

2

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

edited Mar 26 '21 at 07:14

answered Mar 26 '21 at 06:49

P. K. Tharindu

2,565
3
17
34

But, I already did that, It didn't work. My code didn't reach to the line where I am attaching. It fails in the $loan->create line. – Pranta Mar 26 '21 at 06:56
Oops, sorry, you were right. I have to insert the loan first and then attach it to the user with the pivot amount. Thanks.... – Pranta Mar 26 '21 at 07:12
1

Yeah, sorry if I didn't make it clear earlier. I've updated my answer :) – P. K. Tharindu Mar 26 '21 at 07:15

Can't insert into pivot table

Edit

Something Important

Solution

2 Answers2

Linked