Save into multiple tables using stored procedure

Question

I have 2 models: ReceivedGoodsDetail and StockInventory.

When model ReceivedGoodsDetail do actionCreate then StockInventory automatically also will be inserted into table StockInventory.

I use this stored procedure, this what I've tried:

public function actionCreate($id) {
$model = new ReceivedGoodsDetail();
$connection = \Yii::$app->db;
$transaction = $connection->beginTransaction();

$model->ID_Received_Goods = $id;
if ($model->load(Yii::$app->request->post()) && $model->validate()) {
$connection = Yii::$app->db;
$command = $connection->createCommand('{call usp_T_Received_Goods_Detail#InsertData(:ID_Received_Goods,:ID_Item, :Qty, :User)}');

$ID_Received_Goods = $model->ID_Received_Goods;

$ID_Item = $model->ID_Item;
$Qty = $model->Qty;
$User = Yii::$app->user->identity->username;

$command->bindParam(":ID_Received_Goods",$ID_Received_Goods,PDO::PARAM_STR);
$command->bindParam(":ID_Item", $ID_Item, PDO::PARAM_STR);
$command->bindParam(":Qty", $Qty, PDO::PARAM_INT);
$command->bindParam(":User", $User, PDO::PARAM_STR);

if ($command->execute() == 0) {
$transaction->commit();
} else {
$transaction->rollBack();
foreach ($model->getErrors() as $key => $message) {
Yii::$app->session->setFlash('error', $message);
}
}

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

But I'm confused if use 2 models like the case above

Answer 1

Do not be afraid to do such things, here is nothing bad to use stored procedures. But in general your code is not clean and pretty confusing.

First of all, if you are using stored procedure, then why do not make a trigger for ReceivedGoodsDetail (on INSTERT)? IMHO everything will be much simpler with trigger.

Here are some remarks for your implementation.

1. Why do you open transaction before first if? If validation fails then your transaction will not be closed manually.
2. I cant't see here using of 2 models, just one - ReceivedGoodsDetail, and StockInventory as i can understand will be created in stored procedure usp_T_Received_Goods_Detail#InsertData?
3. Why do you redirect user to the item view even if transaction fails?
4. Using ActiveRecord. Then here is no need to start transaction manually. Just define what operations you wish to be transactional for this model in transactions() method.
5. Using ActiveRecord. It is better practice to get db connection from your model class, not application. It will be Yii::$app->db by default, but later you can easily change connection for this particular model.

It will be better for you (for example) to extend ActiveRecord (if not yet) and overload insertInternal() method for ReceivedGoodsDetail.

In class ReceivedGoodsDetail:

public function transactions() {
    return [
        'default' => self::OP_INSERT
    ];    
}

protected function insertInternal($attributeNames = null) {
    if (!$this->beforeSave(true)) {
        return false;
    }
    $values = $this->getDirtyAttributes($attributes);

    /* overrided part of code */

    $connection = static::getDb();
    $command = $connection->createCommand('{call usp_T_Received_Goods_Detail#InsertData(:ID_Received_Goods,:ID_Item, :Qty, :User)}');

    $ID_Received_Goods = $model->ID_Received_Goods;

    $ID_Item = $model->ID_Item;
    $Qty = $model->Qty;
    $User = Yii::$app->user->identity->username;

    $command->bindParam(":ID_Received_Goods",$ID_Received_Goods,PDO::PARAM_STR);
    $command->bindParam(":ID_Item", $ID_Item, PDO::PARAM_STR);
    $command->bindParam(":Qty", $Qty, PDO::PARAM_INT);
    $command->bindParam(":User", $User, PDO::PARAM_STR);

    if($command->execute() != 0) {
        return false;
    }

    /* end of overrided part */

    $changedAttributes = array_fill_keys(array_keys($values), null);
    $this->setOldAttributes($values);
    $this->afterSave(true, $changedAttributes);

    return true;
}

In your action:

public function actionCreate($id) {
    $model = new ReceivedGoodsDetail();

    $model->ID_Received_Goods = $id;
    if ($model->load(Yii::$app->request->post()) && $model->save(true)) {
        return $this->redirect(['receivedgoodsheader/view', 'id' => $model->ID_Received_Goods]);
    } else {
        foreach ($model->getErrors() as $key => $message) {
            Yii::$app->session->setFlash('error', $message);
        }
        return $this->render('create', [
            'model' => $model,
        ]);
    }
}

And then catch your flash messages on create form.

P.S. One more moment. It is strange practice to use path/to/model/{id} endpoint with predefined ID to create new instance. Usually this looks like POST path/to/model. But this can be subject of your business logic, so i don't know if it can be improved or not

P.P.S. This example was not tested (obviously) so here can be some mistakes