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

CodePudding user response：

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!');
    }