Laravel pivot table with multiple columns that needs to insert later

Question

I am having two tables routes and stations and one pivot table route_station. See table details

routes table

id, number, code

stations table

id, name, code

route_station table (pivot)

id, route_id, station_id, next_station_id, interchange_station_id, sation_order, distance, duration

station_id, next_station_id, interchange_station_id all are the satations table id

Schema

Station

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

Route

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

Route_Station - Pivot

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()->nullable();
        $table->bigInteger('interchange_id')->unsigned()->nullable();
        $table->integer('station_order');
        $table->float('distance');
        $table->integer('duration');
        $table->timestamps();

        $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');

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

In the admin area, there will be a total of three sections to manage stations and routes.

1. Stations Page
2. Routes Page
3. A Page where I can add stations to route

That means I am not going to insert a record into pivot while creating a station or route but at any time later from the 3rd-page mention above.

I have two models now Station and Route and set the relationship as below.

Station Model

class Station extends Model
{
    public function routes()
    {
        return $this->belongsToMany('App\Route');
    }

    public function route()
    {
        return $this->belongsTo('App\Route');
    }
}

Route Model

class Route extends Model
{
    public function stations()
    {
        return $this->belongsToMany('App\Station');
    }

    public function station()
    {
        return $this->belongsTo('App\Station');
    }
}

So now the problem is that I don't know how to insert a record into the pivot table to fill out all the columns into the role_station table while station and route record already exists.

I have tried to use attach() but it is giving below error

Illuminate/Database/QueryException with message 'SQLSTATE[HY000]: General error: 1364 Field 'station_order' doesn't have a default value (SQL: insert into route_station (route_id, station_id) values (2, 4))'

So I need help with this

Question:

I understood the error that is asking me to set the station_order column value but I don't >know how can I pass values for next_station_id, interchange_station_id, station_order, distance, duration

Answer 1

By default, only the model keys will be present on the pivot object. If your pivot table contains extra attributes, you must specify them when defining the relationship:

return $this->belongsToMany('App\Route')->withPivot('next_station_id', 'interchange_station_id', 'station_order', 'distance', 'duration');