Rretzko's avatar
Level 15

Excel Import not adding row to database

Hi - I'm hitting a weird problem using Maatwebsite Import. This is a great product, so I'm sure I've got something wrong. I'm importing a csv file with 12 columns. In my sample, only four of the columns have data:

title	subtitle	arrangement	artist	composer	arranger	lyricist	choreographer	words and music	file locator 1	file locator 2	file locator 3
A Chorus Line		SATB	Harry Simeone						A	2	
Achieved Is The Glorious Work		SATB	Franz Joseph Haydn						A	3	
Adam Lay Ybounden		SATB	Boris Ord						A	4	
Adoramus Te		SATB	Palestrina						A	5	
Adoramus Te		SATB	Quirino Gasparini						A	6	

When I test a "title" insert on Tinker, it works as expected. When I run the Insert, it appears that rows are being inserted, but in fact, there is no row in the database.

> $t = \App\Models\Title::firstOrCreate(['title' => 'A Chorus Line', 'alpha' => 'Chorus Line, A'])
= App\Models\Title {#7327
    title: "A Chorus Line",
    alpha: "Chorus Line, A",
    updated_at: "2023-10-28 09:52:01",
    created_at: "2023-10-28 09:52:01",
    id: 30,
  }

However, when I run the Import:

<?php

namespace App\Imports;

use App\Models\Teachers\Schools\Libraries\Items\ArrangementType;
use App\Models\Teachers\Schools\Libraries\Items\Subtitle;
use App\Models\Teachers\Schools\Libraries\Items\Title;
use Illuminate\Support\Collection;
use App\Models\Teachers\Schools\Libraries\Items\Item;
use Illuminate\Support\Str;
use Maatwebsite\Excel\Concerns\OnEachRow;
use Maatwebsite\Excel\Row;

class ItemsImport implements OnEachRow
{

    /**
     * Row columns are expected to be in this order:
     * 0 => title
     * 1 => subtitle
     * 2 => arrangement
     * 3 => artist
     * 4 => composer
     * 5 => arranger
     * 6 => lyricist
     * 7 => choreographer
     * 8 => words and music
     * 9 => file locator 1
     * 10 => file locator 2
     * 11 => file locator 3
     */
    public function onRow(Row $row)
    {
        $item = new Item;

        $rowIndex = $row->getIndex();
        $rowData = $row->toArray();

        if (strlen($rowData[0]) && (strtolower(trim($rowData[0])) !== 'title')) {

            $title = Title::firstOrCreate([
                'title' => $rowData[0],
                'alpha' => $rowData[0],
            ]);

            $subtitle = ($rowData[1]) ? Subtitle::firstOrCreate(['subtitle' => $rowData[1]]) : new Subtitle;

            $arrangement = ArrangementType::firstOrCreate(['descr' => $rowData[2]]);

            dd($title);
        }
    }

the dd($title) responds:

App\Models\Teachers\Schools\Libraries\Items\Title {#7269 ▼ // app\Imports\ItemsImport.php:51
  #connection: "mysql"
  #table: "titles"
  #primaryKey: "id"
  #keyType: "int"
  +incrementing: true
  #with: []
  #withCount: []
  +preventsLazyLoading: false
  #perPage: 15
  +exists: true
  +wasRecentlyCreated: true
  #escapeWhenCastingToString: false
  #attributes: array:5 [▼
    "title" => "A Chorus Line"
    "alpha" => "A Chorus Line"
    "updated_at" => "2023-10-28 09:43:38"
    "created_at" => "2023-10-28 09:43:38"
    "id" => 29
  ]
  #original: array:5 [▶]
  #changes: []
  #casts: []
  #classCastCache: []
  #attributeCastCache: []
  #dateFormat: null
  #appends: []
  #dispatchesEvents: []
  #observables: []
  #relations: []
  #touches: []
  +timestamps: true
  +usesUniqueIds: false
  #hidden: []
  #visible: []
  #fillable: array:2 [▶]
  #guarded: array:1 [▶]

but there is no id 29 created in the database! Here's the migration for the titles table, just in case:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('titles', function (Blueprint $table) {
            $table->id();
            $table->string('title', 120);
            $table->string('alpha', 120);
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('titles');
    }
};

Thanks for any help you can offer!

0 likes
6 replies
LaryAI's avatar
Level 58

The issue seems to be with the firstOrCreate method in the ItemsImport class. The firstOrCreate method returns the model instance if it already exists in the database or creates a new one if it doesn't exist. However, it seems that the model instance is being created but not saved to the database.

To fix this issue, you need to call the save method on the model instance after creating it. Here's the updated code:

if (strlen($rowData[0]) && (strtolower(trim($rowData[0])) !== 'title')) {
    $title = Title::firstOrCreate([
        'title' => $rowData[0],
        'alpha' => $rowData[0],
    ]);

    $title->save(); // Add this line to save the model instance to the database

    $subtitle = ($rowData[1]) ? Subtitle::firstOrCreate(['subtitle' => $rowData[1]]) : new Subtitle;

    $arrangement = ArrangementType::firstOrCreate(['descr' => $rowData[2]]);

    dd($title);
}

By calling the save method, the model instance will be saved to the database, and you should see the corresponding row in the titles table.

Rretzko's avatar
Level 15

I tried the AI suggestion, adding $title->save(), etc. but that did not solve the problem or change the result.

tangtang's avatar

@Rretzko

your code is almost correct, and the title model is being created successfully.

the issue appears to be related to the fact that there's no new row inserted into the database when you create a title. this might be due to the way you're using Eloquent's firstOrCreate method.

If you want to ensure that a new record is created, you can use the firstOrNew method to retrieve the record if it exists or create a new one if it doesn't.

$title = Title::firstOrNew([
    'title' => $rowData[0],
]);

if (!$title->exists) {
    $title->alpha = $rowData[0];
    $title->save();
}
Rretzko's avatar
Level 15

Thanks, @Snapey ! In a million years I wouldn't have come up with this answer, but I will next time. In the meantime, I redesigned the workflow to implement the toModel method and it's a better fit for the use case. Thanks also @tangtang for your contribution.

novilfahlevy's avatar

@Snapey I had the same issue but it solved now, because I removed the DB::beginTransaction, DB::commit, and DB::rollBack as the Maat Excel already done that for us. Thanks for your answer.

Please or to participate in this conversation.