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 </td> <td width="100"> </td> <td width="100"> </td> <td width="20">None </td> <td width="20">0 </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', ], ]) ?>