Yii 2: Populate Another Table after Saving Data to One Table using values of the saved data table

Question

I have two tables: Stations and Routes. The station table has the columns (station_id, address, office_hours, date_create) and Routes table has the columns (routes_id, from_id, destination_id). The from_id and destination_id on the Routes table are foreign keys referencing stations table.

Now what I want to do is whenever a station is added, the routes for the station is calculated using stations already in the table. Example, assuming we already have station A in the stations table. Adding station B results to two routes, A-->B and B-->A whereby for route A-->B, A =>from while B => destination, hence their ids are only picked and populated on the routes table accordingly.

I have tried the following codes in the stations controller but I am not getting any success. The codes are:

StationsController:

public function actionCreate()
{
    $model = new Stations();

    $routesModel = new Routes();

    //checking whether we are getting the logged in user id value
    Yii::info("User id=".Yii::$app->user->id); 

    $model->registered_by_id = Yii::$app->user->id;

    $model->status = 10;

    if ($model->load(Yii::$app->request->post()) && $model->save()) {

        //checking here the saved user id value in  table
        Yii::info("checking User id after saving model=".$model->registered_by_id);

        $this->createRoutes($model->station_id);

        return $this->redirect(['view', 'id' => $model->station_id]);
    } else {
        return $this->render('create', [
            'model' => $model,
        ]);
    }
}

public function createRoutes($id)
{
    $model = new Routes;

    $command = Yii::$app->db->createCommand('SELECT station_id FROM stations WHERE station_id !='.$id);
    $all_stations = $command->queryAll();

    foreach ($all_stations as $new_route) {
        $from_id = $id;
        $destination_id = $new_route;
        $model->load(Yii::$app->request->post()) && $model->save();
        $from_id = $new_route;
        $destination_id = $id;
        $model->load(Yii::$app->request->post()) && $model->save();
    }

    return;
}

The stations table is populated but the routes table is not populated. However, I am not getting any error. Where could I be going wrong?

Please assist where you can.

Revised function createRoutes()

public function createRoutes($id)
{
    $model = new Routes;

    $count = (new \yii\db\Query())->from('stations')->count();

    if($count > 1) {
        $command = Yii::$app->db->createCommand('SELECT station_id FROM stations WHERE station_id !='.$id);
        $all_stations = $command->queryAll();

        foreach ($all_stations as $new_route) {
            $model->from_id = $id;
            $model->destination_id = $new_route['station_id'];
            $model->save();
            $model->from_id = $new_route['station_id'];
            $model->destination_id = $id;
            $model->save();
        }
        return;
    }
    else
        return;
}

Now, after making the above changes, it only saves a single route to the database, yet there are several stations, hence several routes should be created. If I have 10 stations, it will only generate route for station 9 to 10; my assumption is that it only saves the last bit of the foreach look and in other occasions, the $model->save() parameter is not invoked. Am I doing something wrong here?

Answer 1

In your createRoutes method you are creating only one Routes models and populating it and saving it in the loop, which is why you are only getting one record. Instead you just have to create new model inside foreach like this:

public function createRoutes($id)
{
    //$model = new Routes; not here

    // $command = Yii::$app->db->createCommand('SELECT station_id FROM stations WHERE station_id !='.$id);
    // $all_stations = $command->queryAll();

    // I am using  active record instead of single query, you can do anything you want here
    $all_stations = Stations::find()
                ->where('station_id != :id', [':id' => $id])
                ->all();

    foreach ($all_stations as $new_route) {
        $model = new Routes; // create record here
        $model->from_id = $id;
        $model->destination_id = $new_route->station_id;
        // no need to load because these data are not in request param
        // $model->load(Yii::$app->request->post()) && $model->save();
        $model->save();

        $model = new Routes; // and another one
        $model->from_id = $new_route->station_id;
        $model->destination_id = $id;
        $model->save();
    }

    return;
}

---

Refactored Version: Above should do the trick, but here is another optimization.

in the Routes model create a static method like this:

public static function create($data)
{
    $model = new self;
    $model->from_id = $data['from_id'];
    $model->destination_id = $data['destination_id'];
    return $model->save();
}

then rewrite the createRoutes method like this:

public function createRoutes($id)
{
    $all_stations = Stations::find()
                ->where('station_id != :id', [':id' => $id])
                ->all();

    foreach ($all_stations as $new_route) {
        Routes::create([
            'from_id' => $id,
            'destination_id' => $new_route->station_id,
        ]);

        Routes::create([
            'from_id' => $new_route->station_id,
            'destination_id' => $id,
        ]);
    }

    return;
}

And we have much much cleaner version now.