jeroenzwart / laravel-csv-seeder

Seed your database with Laravel using CSV files
Other
90 stars 30 forks source link

Dealing with one-to-one or one-to-many relations in CSV files #33

Open Shujee opened 3 weeks ago

Shujee commented 3 weeks ago

How should I handle CSV files that have data for one-to-one or one-to-many relations? Say I have a CSV file with the following columns:

id, name, packaging, rating, price The tables that need to be seeded are:

table1: id, name, rating, price, table2_id
table2: id, packaging

This is a simplified version of the actual situation I'm dealing with. For each row in table1, there is exactly one row in table2. There are reasons why packaging column cannot be kept in table1.

baradhili commented 6 days ago

I have something similar I have a resource table with a one to many relationship with a contract table. The contract model looks like:

class Contract extends Model
{

    protected $perPage = 20;

    /**
     * The attributes that are mass assignable.
     *
     * @var array<int, string>
     */
    protected $fillable = ['start_date', 'end_date', 'resources_id'];

    /**
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function resource()
    {
        return $this->belongsTo(\App\Models\Resource::class, 'resources_id', 'id');
    }

}

When I try and populate contracts with "start_date","end_date","resource_id" I get Rows of the file "/database/seeders/contracts.csv" has been failed to insert: SQLSTATE[HY000]: General error: 1364 Field 'resources_id' doesn't have a default value (Connection: mysql, SQL: insert intocontracts(created_at,end_date,start_date,updated_at) values (2024 ...

Shujee commented 6 days ago

If this helps, I have for now achieved this by manually reading the CSV file using PHP's built-in fgetcsv, loop through its rows, and for each row, insert a new row in parent table (relevant columns only), get parent's row id, and then insert child row (relevant columns only) and parent's row id as foreign key. Works fine, nothing fancy.