systems:yii2:master-detail_view [Audina Documentation]

Yii 2 Master-Detail View

For a model with master-detail (parent-child) relationships, we have to setup things in the model, controller and then view.

Let use an Order as an example. An Order can contain many product Items. It is 1-M relationship for Order to Items. However, it is a M-M relationship between Order and Items, so we have a separate table (order-items) holding those relationships.

SQL

CREATE TABLE `customer` (
	`id`         INT(12) UNSIGNED NOT NULL AUTO_INCREMENT,
	`first_name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	`last_name`  VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        ...
	PRIMARY KEY (`id`)
)
COLLATE='utf8_unicode_ci', ENGINE=InnoDB, AUTO_INCREMENT=1;
 
CREATE TABLE `product` (
	`id` INT(12) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	`code` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	`model_number` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	`description` TEXT NULL COLLATE 'utf8_unicode_ci',
        ...
	PRIMARY KEY (`id`)
)
COLLATE='utf8_unicode_ci', ENGINE=InnoDB, AUTO_INCREMENT=1;
 
CREATE TABLE `order` (
	`id`          INT(12) UNSIGNED NOT NULL AUTO_INCREMENT,
	`date`        DATETIME NULL DEFAULT NULL,
	`customer_id` INT(12) UNSIGNED NULL DEFAULT NULL,
	`user_id`     INT(12) UNSIGNED NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	INDEX `FK_customer_id` (`customer_id`),
	CONSTRAINT `FK_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
)
COLLATE='utf8_unicode_ci', ENGINE=InnoDB, AUTO_INCREMENT=1;
 
CREATE TABLE `order_item` (
	`id`            INT(12) UNSIGNED NOT NULL AUTO_INCREMENT,
	`order_id`      INT(12) UNSIGNED NULL DEFAULT NULL,
	`product_id`    INT(12) UNSIGNED NULL DEFAULT NULL,
	`serial_number` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	`lot_number`    VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	`color`         VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	`quantity`      INT(12) UNSIGNED NULL DEFAULT '0',
	PRIMARY KEY (`id`),
	UNIQUE INDEX `IDX_order_item` (`order_id`, `product_id`),
        CONSTRAINT `FK_order_id` FOREIGN KEY (`order_id`) REFERENCES `order` (`id`),
	CONSTRAINT `FK_item_id` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`)
)
COLLATE='utf8_unicode_ci', ENGINE=InnoDB, AUTO_INCREMENT=1;

Models

Model @app/models/Order:

class Order extends \yii\db\ActiveRecord
{
    // Model constants
    const MAX_EMPTY_RECS  = 5;
    ...
    public function getOrderItems()
    {
        // Order has_many OrderItem via OrderItem.order_id -> id
        return $this->hasMany(OrderItem::className(), ['order_id' => 'id']);
    }
 
    public function getProducts()
    {
        // Order has_many Product via Product.id -> OrderItem->product_id and OrderItem.order_id -> id
        return $this->hasMany(Product::className(), ['id' => 'product_id'])->via('orderItems');
    }
 
    public function getOrderItemsToUpdate()
    {
        return $this->getOrderItems();
    }
 
    public function getOrderItemsToCreate($emptyItems=-1)
    {
        $emptyItems = ( ($emptyItems < 0) ? self::MAX_EMPTY_RECS : $emptyItems);
        $emptyOrderItems = array();
        for($i=0; $i<$emptyItems; $i++) {
            $emptyOrderItems[] = new OrderItem();
        }
        return $emptyOrderItems;
    }
}

Model @app/models/OrderItem:

class OrderItem extends \yii\db\ActiveRecord
{
    ...
 
    public function getOrder()
    {
        // OrderItem has_one Order via Order.id -> order_id
        return $this->hasOne(Customer::className(), ['id' => 'order_id']);
    }
 
    public function getProduct()
    {
        // OrderItem has_many Product via Product.product_id -> id
        return $this->hasOne(Product::className(), ['id' => 'product_id']);
    }
}

Controllers

Controller @app/controllers/OrderController:

use yii\data\ActiveDataProvider;
use yii\base\Model;
use app\models\Order;
use app\models\OrderItem;
 
class OrderController extends Controller
{
    ...
 
    /**
     * Displays a single Order model.
     * @param string $id
     * @return mixed
     */
    public function actionView($id)
    {
        $dataProvider = new ActiveDataProvider([
            'query' => OrderItem::find()->where(['order_id' => $id])->joinWith('product'),
            'pagination' => [
                'pageSize' => 20,
            ],
        ]);
 
        /**
         * Setup your sorting attributes
         * Note: This is setup before the $this->load($params) statement below
         */
        $dataProvider->setSort([
            'attributes' => [
                'serial_number',
                'lot_number',
                'color',
                'product_name' => [
                    'asc'   => ['product.name' => SORT_ASC],
                    'desc'  => ['product.name' => SORT_DESC],
                    'label' => 'Product'
                ],
                'product_code' => [
                    'asc'   => ['product.code' => SORT_ASC],
                    'desc'  => ['product.code' => SORT_DESC],
                    'label' => 'Product Code'
                ],
                'product_model' => [
                    'asc'   => ['product.model_number' => SORT_ASC],
                    'desc'  => ['product.model_number' => SORT_DESC],
                    'label' => 'Product Model'
                ],
                'upc' => [
                    'asc'   => ['product.upc' => SORT_ASC],
                    'desc'  => ['product.upc' => SORT_DESC],
                    'label' => 'UPC'
                ],
            ]
        ]);
 
        return $this->render('view', [
            'model' => $this->findModel($id),
            'dataProvider'=> $dataProvider
        ]);
    }
 
    /**
     * Creates a new Order model.
     * If creation is successful, the browser will be redirected to the 'view' page.
     * @return mixed
     */
    public function actionCreate()
    {
        $model = new Order();
        $orderItems = $model->getOrderItemsToCreate();  // empty items
 
        if ($model->load(Yii::$app->request->post()) && $model->save()) {
            // Save order (parent), then order items (children)
            if (Model::loadMultiple($orderItems, Yii::$app->request->post()) && 
                Model::validateMultiple($orderItems)) 
            {
                foreach($orderItems as $item) {
                    if (!empty($item['serial_number'])) {
                        $item['order_id'] = $model->id;  
                        $item->save();
                    }
                }
                return $this->redirect(['view', 'id' => $model->id]);
            }    
        } else {
            return $this->render('create', [
                'model'      => $model,
                'orderItems' => $orderItems,
                'colors'     => $model->getColors(),
                'products'   => $model->getProductList(),
            ]);
        }
    }
 
    /**
     * Updates an existing Order model.
     * If update is successful, the browser will be redirected to the 'view' page.
     * @param string $id
     * @return mixed
     */
    public function actionUpdate($id)
    {
        $model = $this->findModel($id);
        $orderItems = $model->orderItemsToUpdate;  // existing items
        $emptyItems = (Order::MAX_EMPTY_RECS - count($orderItems));
        if ($emptyItems > 0) {
            // Append empty items if necessary, in case new items need to be added to current order
            $orderItems = array_merge($orderItems, $model->getOrderItemsToCreate($emptyItems));
        }
 
        if ($model->load(Yii::$app->request->post()) && $model->save()) {
            // Save order (parent), then order items (children)
            if (Model::loadMultiple($orderItems, Yii::$app->request->post()) &&
                Model::validateMultiple($orderItems)) 
            {
                foreach($orderItems as $item) {
                    if (!empty($item['serial_number'])) {
                        $item['order_id'] = $model->id;  
                        $item->save();
                    }
                }
                return $this->redirect(['view', 'id' => $model->id]);
            }
        } else {
            return $this->render('update', [
                'model'      => $model,
                'orderItems' => $orderItems,
                'colors'     => $model->getColors(),
                'products'   => $model->getProductList(),
            ]);
        }
    }
}

Views

View @app/views/order/_form.php:

<?php
 
use yii\helpers\Html;
use yii\helpers\ArrayHelper;
use yii\widgets\ActiveForm;
//use yii\data\ActiveDataProvider;
use yii\grid\GridView;
use app\models\Customer;
 
/* @var $this yii\web\View */
/* @var $model app\models\Order */
/* @var $form yii\widgets\ActiveForm */
?>
 
<div>
<div class="order-form">
 
    <?php $form = ActiveForm::begin(); ?>
 
    <?= $form->field($model, 'date')->textInput(['value' => Yii::$app->formatter->asDate('now', 'php:Y-m-d')]) ?>
    <?=  $form->field($model, 'customer_id')->label('Customer')
             ->dropDownList(ArrayHelper::map(Customer::find()->all(), 'id', 'fullname')) ?>
    <?= Html::activeHiddenInput($model, 'user_id', ["value" =>  Yii::$app->user->getId()]) ?>
 
    <table border=0 width="100%" cellpadding="2">
        <tr>
            <?php if (!$model->isNewRecord): ?>
                <!--<th>ID</th><th>Order</th>-->
            <?php endif; ?>
            <th>Product</th>
            <th>Serial Number</th>
            <th>Lot Number</th>
            <th>Color</th>
            <!--<th>Quantity</th>-->
        </tr>
        <?php $items = $orderItems;  ?>
        <?php if (count($items) > 0): ?>
            <?php foreach($items as $i=>$item): ?>
                <tr>
                    <?php if (!$model->isNewRecord): ?>
                        <!--<td><?= $form->field($item,"[$i]id")->label(''); ?></td>-->
                        <!--<td><?= $form->field($item,"[$i]order_id")->label(''); ?></td>-->
                    <?php endif; ?>
                    <td><?= $form->field($item,"[$i]product_id")->label('')->dropDownList(
                        ArrayHelper::map($model->productList, 'id', 'name'),
                        ['prompt'=>'--Select One--']    // options
                    ) ?></td>
                    <td><?= $form->field($item,"[$i]serial_number")->label(''); ?></td>
                    <td><?= $form->field($item,"[$i]lot_number")->label(''); ?></td>
                    <td><?= $form->field($item,"[$i]color")->label('')->dropDownList(
                        $colors,
                        ['prompt'=>'--Select One--']    // options
                    ) ?></td>
                </tr>
            <?php endforeach; ?>
        <?php else: ?>
            <tr>
                <td width="20">N/A</td>
                <td width="30">00000000</td>
                <td width="50">N/A&nbsp;</td>
                <td width="100">&nbsp;</td>
                <td width="100">&nbsp;</td>
                <td width="20">None&nbsp;</td>
                <td width="20">0&nbsp;</td>
            </tr>
        <?php endif; ?>
    </table>
 
    <div class="form-group">
        <?= Html::submitButton($model->isNewRecord ? 
             Yii::t('app', 'Save') : 
             Yii::t('app', 'Update'), ['class' => $model->isNewRecord ? 'btn btn-success' : 'btn btn-primary']
        ) ?>
    </div>
</div><!-- form -->
 
<?php ActiveForm::end(); ?>
 
</div>

View @app/views/order/create and also same in @app/views/order/update:

...
<?= $this->render('_form', [
    'model'      => $model,
    'orderItems' => $orderItems,  // <--- important
    'colors'     => $colors,
    'products'   => $products,
]) ?>

View @app/views/order/view:

use yii\widgets\DetailView;
use yii\grid\GridView;
...
     Order 
     <?= DetailView::widget([
        'model' => $model,
        'attributes' => [
            [
                'label' => 'Date',
                'value' => substr($model->date, 0, 10),
            ],
            [
                'label' => 'Customer',
                'attribute' => 'customer.fullname'
            ],
            'customer.company_name',
            [
                'label' => 'Address',
                'value' => $model->customer->address . ', ' . $model->customer->city . ', ' . 
                           ($model->customer->state_prov != 'N/A' ? $model->customer->state_prov : '') . ' ' .
                           $model->customer->postal_code . ', ' .  $model->customer->country
            ],
            'customer.account_number',
        ],
    ]) ?>
 
    Order Items
    <?= GridView::widget([
        'dataProvider' => $dataProvider,
        'columns' => [
            ['class' => 'yii\grid\SerialColumn'],
            [
                'attribute' => 'product_name',
                'value' => 'product.name',
                'label' => 'Product',
            ],
            [
                'attribute' => 'product_code',
                'value' => 'product.code',
                'label' => 'Product Code',
            ],
            [
                'attribute' => 'product_model',
                'value' => 'product.model_number',
                'label' => 'Model',
            ],
            'serial_number',
            'lot_number',
            [
                'attribute' => 'product_upc',
                'value' => 'product.upc',
                'label' => 'UPC',
            ],
            'color',
        ],
    ]) ?>