Display results while joining tables in yii2

Question

I am joining two tables tbl_complain_type and tbl_section.The sql for that tables are

CREATE TABLE IF NOT EXISTS `tbl_complain_type` (
`id` int(9) NOT NULL,
  `section_id` varchar(250) NOT NULL,
  `complains` varchar(250) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=20 ;

-- --------------------------------------------------------

--
-- Table structure for table `tbl_section`
--

CREATE TABLE IF NOT EXISTS `tbl_section` (
`id` int(9) NOT NULL,
  `section` varchar(250) CHARACTER SET latin1 NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_complain_type`
--
ALTER TABLE `tbl_complain_type`
 ADD PRIMARY KEY (`id`);

--
-- Indexes for table `tbl_section`
--
ALTER TABLE `tbl_section`
 ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_complain_type`
--
ALTER TABLE `tbl_complain_type`
MODIFY `id` int(9) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=20;
--
-- AUTO_INCREMENT for table `tbl_section`
--
ALTER TABLE `tbl_section`
MODIFY `id` int(9) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=8;

tbl_complain_type and tbl_section have relation like tbl_section.id=tbl_complain_type.section_id .

I have made relation in ComplainType model as

public function getSection()
    {

        return $this->hasMany(Section::className(), ['id' => 'section_id']);
    }

and in section model as

public function getComplainType()
    {

        return $this->hasOne(ComplainType::className(), ['id' => 'section_id']);
    }

and I have modified ComplainTypeSearch model as

public function search($params) {
    $query = ComplainType::find() ;  
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);


     $dataProvider->setSort([
        'attributes' => [
            'id',
            'complains' => [
                'asc' => ['complains' => SORT_ASC, 'complains' => SORT_ASC],
                'desc' => ['complains' => SORT_DESC, 'complains' => SORT_DESC],
                'label' => 'complains',
                'default' => SORT_ASC
            ],
            'section' => [
                'asc' => ['tbl_section.id' => SORT_ASC],
                'desc' => ['tbl_section.id' => SORT_DESC],
                'label' => 'Section'
            ]
        ]
    ]);

    if (!($this->load($params) && $this->validate())) {

        $query->joinWith(['section']);
        return $dataProvider;
    }

    $this->addCondition($query, 'id');
    $this->addCondition($query, 'complains', true);
    $this->addCondition($query, 'section_id');
    return $dataProvider;
    }

So in my index function, to display section with section name instead of section_id

I had to write as

  <?= GridView::widget([
            'dataProvider' => $dataProvider,
            'filterModel' => $searchModel,
            'columns' => [
                ['class' => 'yii\grid\SerialColumn'],

                'id',

                ['attribute' => 'section',
                'label'=>'section',
                'format' => 'raw',
                'value'=>function ($data) {
                return    $data['section'][0]->section     ;



                }, 'filter' => true,
                ],
                'complains',

                ['class' => 'yii\grid\ActionColumn'],
            ],
        ]); ?>

while

<?= GridView::widget([
        'dataProvider' => $dataProvider,
        'filterModel' => $searchModel,
        'columns' => [
            ['class' => 'yii\grid\SerialColumn'],

            'id',
             'section',
             ,
            'complains',

            ['class' => 'yii\grid\ActionColumn'],
        ],
    ]); ?>

doesnt print section in view file. Is there any better way than using $data['section'][0]->section ; to display the section ? I think using $data['section'][0]->section is not the right way. I would like to hear some suggestions or better way to achieve this.

Answer 1

You are only showing the first section, if the records has multiple sections it will show only the first section. if it has no section then you should see an error. Probably you should do something like this

[
   'attribute' => 'section',
   'label'=>'section',
   'format' => 'raw',
   'value'=>function ($model) {
      $sections = [];
      if($model->section) {
         foreach($model->section as $section) {
             $sections[] = $section->section
         }
      }
      return implode(', ', $sections);
   }, 
   'filter' => true,
],