Two tables (master-detail) in GridViews on a single view.

Define our task – “Display records from two MySQL table on a screen (Yii2 View, GridView). First grid – master table, second grid – records from table according to a row in master table which was double clicked”.

Prerequisites:
1. Yii2 advanced template with pretty url.

'urlManager' => [
            'enablePrettyUrl' => true,
            'showScriptName' => false,
            'rules' => ['<alias:\w+>' => 'site/',
            ],
        ],

.htaccess

RewriteEngine on
# If a directory or a file exists, use it directly
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
# Otherwise forward it to index.php
RewriteRule . index.php

2.Add widget for our first Grid in composer

"kartik-v/yii2-grid": "@dev",
"kartik-v/yii2-mpdf": "*",

3. Create 2 tables in our database and populate them.
polls

CREATE TABLE `polls` (
  `id` int(11) NOT NULL COMMENT '№ poll',
  `question` text NOT NULL COMMENT 'question',
  `date_beg` date NOT NULL COMMENT 'Date begin',
  `date_end` date NOT NULL COMMENT 'Date end'
) ENGINE=InnoDB DEFAULT CHARSET=;

INSERT INTO `polls` (`id`, `question`, `date_beg`, `date_end`) VALUES
(1, 'Do you like our company?', '2016-11-19', '2016-11-20'),
(2, 'Do you like our plumbers?', '2016-11-24', '2016-11-25'),
(3, 'Do you have running water?', '2016-11-28', '2016-11-30');

ALTER TABLE `polls`
  ADD PRIMARY KEY (`id`),
  ADD KEY `id` (`id`);

and polls_answers

CREATE TABLE `polls_answers` (
  `id` int(11) NOT NULL COMMENT '№ answer',
  `id_poll` int(11) DEFAULT NULL COMMENT '№ poll',
  `answer` text NOT NULL COMMENT 'answer'
) ENGINE=InnoDB;

INSERT INTO `polls_answers` (`id`, `id_poll`, `answer`) VALUES
(1, 1, 'Yes, very much'),
(2, 1, 'Almost always'),
(3, 2, 'Sometimes'),
(4, 2, 'Сertainly'),
(5, 3, 'Of course'),
(6, 3, 'From time to time');

ALTER TABLE `polls_answers`
  ADD KEY `id` (`id`),
  ADD KEY `id_poll` (`id_poll`);

ALTER TABLE `polls_answers`
  ADD CONSTRAINT `polls_answers_ibfk_1` FOREIGN KEY (`id_poll`) REFERENCES `polls` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

4. With gii generate models, controllers and views in backend part
for polls
Model – Polls, Controller PollsController, views dir – polls

for polls_answers
Model – PollsAnswers, Controller – PollsAnswersController, views dir – polls-answers

We will modify view index.php in polls

First, change standard Grid widget

use kartik\grid\GridView;

then change options for our new GridView

GridView::widget([
            'dataProvider' => $dataProvider,
            'filterModel' => $searchModel,
            'columns' => [
                'id',
                'question:ntext',
                'date_beg',
                'date_end',
                    ['class' => 'yii\grid\ActionColumn'],
            ],
            'responsive' => true,
            'hover' => true,
            'bordered' => true,
            'striped' => false,
            'condensed' => true,
            'hover' => true,
            'showPageSummary' => false,
            'persistResize' => false,
            'exportConfig' => true,
        ]);

Add additional divs right after GridView

<div class="dvd">
        <div class="questiontext"></div>
        <div class="secondtable"></div>
</div>

And then add rather simple jQuery script. It will work with double click event in our first grid. Pay attention on the name of grid in HTML “.kv-grid-table”.
On double click the text in first and second column of our grid will be use in $.ajax. This ajax will return a second grid from PollsAnswerController.
$url in this script means:

$url = "@backendviews/polls-answers/indexpart";

where @backendviews – alias, returning path to views dir in backend part

this->registerJS(
        '
            $(document).ready(function () {
            $("tr").on("dblclick", function (ev) {
                var text_id = $("td:first", $(this)).text();
                var text_question = $("td:nth-child(2)", $(this)).text();
                $(".questiontext").html("<h1>"+text_question+"</h>");               
                $.ajax({
                    type: "GET",
                    url:"' . $url . '",
                    data: "id_poll="+text_id,
                    success: function (msg) {          
                    if ((msg.length>0)&&(text_question.length>0)){
                        $(".secondtable").html(msg);
                        text_question="";}
                    }}
                );//  $.ajax({

            })//$(".kv-grid-table tr").on("click", function (ev) {
        });' //$(document).ready(function () {
        , View::POS_END
);
?>

msg contain HTML-code for detail Grid
To get this we should add indexpart.php in polls-answers dir. This view is based on index.php, generated by gii with some minor changes.

<?php

//for rending only part of anwwers corresponding to one poll

use yii\helpers\Html;
use yii\grid\GridView;

/* @var $this yii\web\View */
/* @var $searchModel backend\models\PollsAnswersSearch */
/* @var $dataProvider yii\data\ActiveDataProvider */

?>
<div class="polls-answers-index">
    <h1><?= Html::encode(Yii::t('app', 'Варианты ответов')) ?></h1>
    <p>
        <?= Html::a(Yii::t('app', 'Добавить ответ'), ['create'], ['class' => 'btn btn-success']) ?>
    </p>
    <?=
    GridView::widget([
        'dataProvider' => $dataProvider,
        'columns' => [
                ['class' => 'yii\grid\SerialColumn'],
            'answer:ntext',
                ['class' => 'yii\grid\ActionColumn'],
        ],
    ]);
    ?>
</div>

In PollsAnswerController add new action to render indexpart view. We will use partial rendering to avoid showing breadcrumbs widget and footer. And we use false option to form and receive in out AJAX a string.

public function actionIndexpart($id_poll) {
        $searchModel = new PollsAnswersSearch();
        $dataProvider = $searchModel->search([$searchModel->formName() => ['id_poll' => $id_poll]]);       
        return $this->renderPartial('indexpart', ['searchModel' => $searchModel,
                    'dataProvider' => $dataProvider], false
        );
    }

The result will please us.

On the first stage we have Grid displaying polls

1

Double click on the second row and we can see answers from polls_answers with id_poll=2

2

The same with the third row and answers for poll with id_poll=3 are shown.

3

Problem solved.

Leave a Reply

Your email address will not be published. Required fields are marked *