Code Lover
Code Lover

Reputation: 8348

Laravel insert multiple row in pivot table for one relational id for clone fields

I have two relational tables with pivot routes, stations, and route_station. The routes table contains the route number for the metro, and the stations contains all the stations.

The pivot table multiple routes and multiple stations for each route. So not sure how to consider it. Either many-to-many or one-to-many?

HTML Form

The HTML form on the Route contains clonable fields that need to insert into the pivot table. See below.

The Question

So my question is how to insert clonable fields data (in the above list) so that insert row in the pivot table for each cloned field?

Also how I can update so it updates the exact record in the pivot table and if the clone field is removed then the record from the pivot table also removes?

Route Schema

Schema::create(
    'routes',
    function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->smallInteger('number')->unsigned()->unique();
        $table->string('code')->unique();
        $table->timestamps();

        $table->unique(['number', 'code'], 'routes_unique_columns');
    }
);

Station Schema

Schema::create(
    'stations',
    function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('name')->index()->unique();
        $table->string('code')->index()->unique();
        $table->text('info');
        $table->string('photo')->nullable();
        $table->timestamps();
    }
);

Route Station (Pivot) Schema

Schema::create(
    'route_station',
    function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->bigInteger('route_id')->unsigned();
        $table->bigInteger('station_id')->unsigned();
        $table->bigInteger('next_station_id')->unsigned();
        $table->integer('station_order');
        $table->float('distance');
        $table->integer('duration');
        $table->timestamps();

        $table->unique(['route_id', 'station_id'], 'route_station_unique');

        $table->foreign('route_id')
              ->references('id')
              ->on('routes')
              ->onDelete('restrict');

        $table->foreign('station_id')
              ->references('id')
              ->on('stations')
              ->onDelete('restrict');

        $table->foreign('next_station_id')
              ->references('id')
              ->on('stations')
              ->onDelete('restrict');
    }
);

Route Create/Update Form

enter image description here

Update

I have tried below but not working. Giving Array to String conversion error. This is because, I believe the attach and sync is inserting only one record, while the request fields is an array.

So how can I insert a record for each item of an array?

$route = Route::create([
    'number' => $request->number,
    'code'   => strtoupper($request->code),
]);

$route->stations()->sync($route, [
    'next_station_id' => $request->next_station_id,
    'distance'        => $request->distance,
    'duration'        => $request->duration,
    'station_order'   => $request->station_order,
]);

Upvotes: 0

Views: 420

Answers (1)

Kirmin
Kirmin

Reputation: 301

So as i can see for one route section you have one starting station and one ending station so you can consider this a one to one relationship.

By route section i mean one row of the route_station table.

One route section => one starting station, one ending section.

Second route section => second starting station, second ending station.

You can consider it one to one for each route_station.station_id to stations.id and route_station.next_station_id to stations.id

With this you can take the start and next station for every route_station. So in the route_station table you can have one to one relationship for the station_id field to the stations table and another one to one relationship for the next_station_id field to the stations table

On the other hand you have one route to many route_stattions (many routing sections) with this you can take all the route_stattions for one route. So in the routes table you can have a one to many relationship on the field routes.id with the table route_stations and the field route_stations.route_id

You can read more about the laravel relationships on the link

https://laravel.com/docs/7.x/eloquent-relationships#one-to-many

Make sure you select the correct laravel version

You can insert the values model by model for each table.

You just need to make sure you get the correct ids for tge foreign fields for each record.

You can also update the data model by model separatelly as well but you first need get the table row id that needs to be updated.

You can read more about updating the model on this link https://laravel.com/docs/7.x/eloquent#updates

Upvotes: 1

Related Questions