Laravel 9 - SQLSTATE[HY000]: General error: 1364 Field 'order_id' doesn't have a default value

Question

Hello i'm trying to insert data to db that have to be split in 2 related tables. For now i'm using some dummy values.

Table 1:

        Schema::create('orders_grid', function (Blueprint $table) {
        $table->id();
        $table->integer('id_agente')->unsigned()->index();
        $table->tinyText('canale');
        $table->integer('codice_cliente')->unsigned();
        $table->integer('codice_destinazione')->unsigned();
        $table->timestamp('data_ordine');
        $table->date('data_richiesta')->nullable();
        $table->tinyText('tipologia',1);
        $table->integer('edi_id')->nullable();
        $table->string('edi_company',5)->nullable();
        $table->string('edi_doctype',2)->nullable();
        $table->integer('jde_id')->nullable();
        $table->string('jde_company',5)->nullable();
        $table->string('jde_doctype',2)->nullable();
        $table->integer('stato_ordine')->default(0);
        $table->decimal('total_order',8,2)->unsigned()->nullable();
        $table->text('note_consegna')->nullable();
        $table->timestamps();
    });

Table 2:

        Schema::create('orders_detail', function (Blueprint $table) {
        $table->id();
        $table->foreignId('order_id')->constrained('orders_grid')->onUpdate('cascade')->onDelete('cascade');
        $table->integer('nr_riga')->unsigned();
        $table->string('codice_articolo',25);
        $table->integer('quantita')->unsigned();
        $table->decimal('prezzo',6,4)->unsigned();
        $table->timestamps();
    });

Model 1:

    protected $fillable = [
    'id_agente',
    'canale',
    'codice_cliente',
    'codice_destinazione',
    'data_ordine',
    'data_richiesta',
    'tipologia',
    'edi_id',
    'edi_company',
    'edi_doctype',
    'jde_id',
    'jde_company',
    'jde_doctype',
    'stato_ordine',
    'total_order',
    'note_consegna',

];

public function order_detail()
{
    return $this->hasMany('App\Models\OrderDetail');
}

Model 2:

    protected $fillable = [
    'nr_riga',
    'codice_articolo',
    'quantita',
    'prezzo',
    'order_id'

];

public function order_grid()
{
    return $this->belongsTo('App\Models\OrderGrid');
}

Controller:

   function importData(Request $request) {
    
    $request->validate([
        'uploaded_file' => 'required|file|mimes:xls,xlsx'
    ]);
    
    $excel_file = $request->file('uploaded_file');
    $data_grid = []; 
    $data_detail = [];
    
    $file = IOFactory::load($excel_file->getRealPath());
    // grid data from excel
    $data_grid['codice_cliente'] = $file->getActiveSheet()->getCell('B3')->getValue();
    $data_grid['codice_destinazione'] = $file->getActiveSheet()->getCell('G3')->getValue();
    $data_grid['data_ordine'] = '2022-07-15 15:32:04';
    //$data_grid['data_ordine'] = $file->getActiveSheet()->getCell('B4')->getValue();
    $data_grid['data_richiesta'] = '2022-06-15';//$file->getActiveSheet()->getCell('B5')->getValue();
    $data_grid['note_consegna'] = $file->getActiveSheet()->getCell('G5')->getValue();
    // grid data static
    $data_grid['id_agente'] = 1;
    $data_grid['canale'] = 'B';
    $data_grid['tipologia'] = 'O';
    $data_grid['edi_id'] = 1;
    $data_grid['edi_company'] = 'C';
    $data_grid['edi_doctype'] = 'D';
    $data_grid['jde_id'] = 2;
    $data_grid['jde_company'] = 'E';
    $data_grid['jde_doctype'] = 'F';
    $data_grid['stato_ordine'] = 1;
    $data_grid['total_order'] = 99.99;

    // details data
    $data_detail['codice_articolo'] = $file->getActiveSheet()->getCell('A9')->getValue();
    $data_detail['quantita'] = $file->getActiveSheet()->getCell('B9')->getValue();
    // details data static
   // $data_detail['order_id'] = ;
    $data_detail['nr_riga'] = 50;
    $data_detail['prezzo'] = 99.9999;


   // $data_excel['Riferimento_ordine_cliente'] = $file->getActiveSheet()->getCell('G4')->getValue();
   // $data_excel['Codice_prezzo_gruppo_1'] = $file->getActiveSheet()->getCell('D9')->getValue();
   // $data_excel['Codice_prezzo_gruppo_2'] = $file->getActiveSheet()->getCell('E9')->getValue();
   // $data_excel['Codice_prezzo_gruppo_3'] = $file->getActiveSheet()->getCell('F9')->getValue();
    //dd($data_excel);

    $validator = Validator::make([$data_grid, $data_detail], [
        'codice_cliente' => 'max:15',
        'codice_destinazione' => 'max:15',
        'data_ordine' => 'max:15',
        'data_richiesta' => 'max:15',
        'note_consegna' => 'max:15',
        'id_agente' => 'max:15',
        'canale' => 'max:15',
        'tipologia' => 'max:15',
        'edi_id' => 'max:15',
        'edi_company' => 'max:15',
        'edi_doctype' => 'max:15',
        'jde_id' => 'max:15',
        'jde_company' => 'max:15',
        'jde_doctype' => 'max:15',
        'stato_ordine' => 'max:15',
        'total_order' => 'max:15',
        'codice_articolo' => 'max:15',
        'quantita' => 'max:15',
        //
        //'order_id' => 'max:15',
        'nr_riga' => 'max:15',
        'prezzo' => 'max:15',
        
    ], [
        'max' => 'Max :max characters allowed for the ":Attribute"',
        'required' => 'The :attribute is required!!!',
    ]);

    //dd($validator);

    if ($validator->fails()) {
        return redirect()
        ->back()
        ->withErrors($validator);
    } else {
        DB::table('orders_grid')->insert($data_grid);
        DB::table('orders_detail')->insert($data_detail);
        return redirect()->back()->with('message', 'File caricato correttamente!');
    }

}

Why does it expect a default value if it has to get the id from orders_grid table which is related to? If i add some dummy value like 1 in // $data_detail['order_id'] = ; i don't get errors. But i need there the related ID

Vinod Patidar · Accepted Answer · 2022-07-19T07:41:38.960

2

Just update on last else condition in the controller

else {
        $insert_id = DB::table('orders_grid')->insertGetId($data_grid);
        $data_detail['order_id'] = $insert_id;
        DB::table('orders_detail')->insert($data_detail);
        return redirect()->back()->with('message', 'File caricato correttamente!');
    }

edited Jul 19 '22 at 07:41

answered Jul 18 '22 at 13:14

Vinod Patidar

381
4
11

this is not working, same error – Jul 18 '22 at 13:26
1

Please check your 'orders_grid' table for new entry. If it is happening then you will get last insert id using insertGetId and you can use this id in your related table i.e. orders_detail. – Vinod Patidar Jul 18 '22 at 13:31
Yes it is inserting data to orders_grid, but not in orders_detail.. keeps returning this error SQLSTATE[HY000]: General error: 1364 Field 'order_id' doesn't have a default value... the fact is that with one "upload()" function i'm inserting all the data, but the order_details is looking for an empty record i guess – Jul 18 '22 at 14:06
@shaddark I had typo mistake in my variable name. I used $data_details['order_id'] instead of $data_detail['order_id']. Can you check using it ? – Vinod Patidar Jul 19 '22 at 07:41
Yes it's working! Thank you very much, it was also my mistake to not check it.. I've made something similar with another db query for get the ID, then cycled it and pushed to the array. But your way seems to be cleaner! – Jul 19 '22 at 07:57
@shaddark Make answer correct is also a thankyou on stack overflow :) – Vinod Patidar Jul 19 '22 at 07:59
I'm remaking it with Eloquent.. is there anything similar for get the ID and pass it to second table? – Jul 19 '22 at 09:36
1

@shaddark May be this can help you https://stackoverflow.com/questions/21084833/get-the-last-inserted-id-using-laravel-eloquent https://stackoverflow.com/questions/27873777/how-to-get-last-insert-id-in-eloquent-orm-laravel – Vinod Patidar Jul 19 '22 at 13:32
Yes i did it like that, thanks again! – Jul 19 '22 at 14:24

Laravel 9 - SQLSTATE[HY000]: General error: 1364 Field 'order_id' doesn't have a default value

1 Answers1