Yii 2.0: Yii2 GridView Sorting and Searching with a Junction Table Column(Many to Many Relationship)

5 followers

Following is the table structure

tblgroups

CREATE TABLE IF NOT EXISTS `tblgroups` (
  `id` int(11) NOT NULL,
  `groupname` varchar(150) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Acitve,2=Inactive',
  `date` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

tblcontacts

CREATE TABLE IF NOT EXISTS `tblcontacts` (
  `id` int(11) NOT NULL,
  `firstname` varchar(100) NOT NULL,
  `lastname` varchar(100) NOT NULL,
  `company` varchar(100) NOT NULL,
  `address` text NOT NULL,
  `phone` varchar(50) NOT NULL,
  `mobile` varchar(50) NOT NULL,
  `fax` varchar(50) NOT NULL,
  `pemail` varchar(100) NOT NULL,
  `semail` varchar(100) NOT NULL,
  `country` varchar(55) NOT NULL,
  `websiteurl` varchar(100) NOT NULL,
  `gender` tinyint(4) NOT NULL COMMENT '1=male,2=female,3=shemale',
  `birthday` varchar(10) NOT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1=Active,2=Inactive',
  `sentstatus` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1=sent,2=not sent',
  `addeddate` datetime NOT NULL,
  `updateddate` datetime NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

tblcontactsgroups(junction table)

CREATE TABLE IF NOT EXISTS `tblcontactsgroups` (
  `id` int(11) NOT NULL,
  `contact_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `dateadded` datetime NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=20085 DEFAULT CHARSET=latin1;

First of all with the help of gii crud generators we will create the crud for all these tables including models and controllers,tblcontactsgroups is the table having many to many relation between tblcontacts and tblgroups. In the Contacts Model generated from tblcontacts we will use the hasMany relationship as follows

public function getGroups() {
        return $this->hasMany(Groups::className(), ['id' => 'group_id'])->viaTable('tblcontactsgroups', ['contact_id' => 'id']);
    }

In Groups model we will insert the following code

public function getContacts() {
        return $this->hasMany(Contacts::className(), ['id' => 'contact_id'])->viaTable('tblcontactsgroups', ['group_id' => 'id']);
    }

The most important model is the ContactsSearch Model we will make the following changes in it first of all we declare the groupname(related groups table attribute) public property in ContactsSearch Model

class ContactsSearch extends Contacts {
 
    /**
     * @inheritdoc
     */
    // public group_id;
    public $groupname;
............................

we will also include groupname in the rules

public function rules() {
        return [
            [['id', 'gender', 'status', 'sentstatus'], 'integer'],
            [['firstname', 'lastname', 'company', 'address','groupname', 'phone', 'mobile', 'fax', 'pemail', 'semail', 'country', 'websiteurl', 'birthday', 'addeddate', 'updateddate'], 'safe'],
        ];
    }

and then we will manipulate the search method in ContactsSearch Model like this

public function search($params) {
        $query = Contacts::find()->innerJoinWith('groups', true);
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'sort' => ['attributes' => ['firstname', 'lastname', 'groupname', 'email', 'pemail']]
        ]);
 
        $this->load($params);
        if (!$this->validate()) {
            // uncomment the following line if you do not want to return any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }
 
        // grid filtering conditions
        $query->andFilterWhere([
            'id' => $this->id,
            'gender' => $this->gender,
            'status' => $this->status,
            'sentstatus' => $this->sentstatus,
            'addeddate' => $this->addeddate,
            'updateddate' => $this->updateddate,
        ]);
 
        $query->andFilterWhere(['like', 'firstname', $this->firstname])
                ->andFilterWhere(['like', 'lastname', $this->lastname])
                ->andFilterWhere(['like', 'company', $this->company])
                ->andFilterWhere(['like', 'address', $this->address])
                ->andFilterWhere(['like', 'phone', $this->phone])
                ->andFilterWhere(['like', 'mobile', $this->mobile])
                ->andFilterWhere(['like', 'fax', $this->fax])
                ->andFilterWhere(['like', 'pemail', $this->pemail])
                ->andFilterWhere(['like', 'semail', $this->semail])
                ->andFilterWhere(['like', 'country', $this->country])
                ->andFilterWhere(['like', 'websiteurl', $this->websiteurl])
                ->andFilterWhere(['like', 'birthday', $this->birthday])
                ->andFilterWhere(['like', 'groupname', $this->groupname]);
 
        return $dataProvider;
    }

Following is the code for the ContactsController no change just gii generated code

public function actionIndex()
    {
        $searchModel = new ContactsSearch();
 
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);
 
        return $this->render('index', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
        ]);
    }

Following is the code for the gridview

GridView::widget([
        'dataProvider' => $dataProvider,
        'filterModel' => $searchModel,
        'columns' => [
            ['class' => 'yii\grid\SerialColumn'],
            [
                'label' => 'First Name',
                'attribute' => 'firstname',
            ],
            [
                'label' => 'Last Name',
                'attribute' => 'lastname',
            ],
            //  'company',
            // 'address:ntext',
            // 'phone',
            // 'mobile',
            // 'fax',
    //Following is the related column from the groups table
            [
                'label' => 'Groups',
                'format' => 'ntext',
                'attribute'=>'groupname',
                'value' => function($model) {
                    foreach ($model->groups as $group) {
                        $groupNames[] = $group->groupname;
                    }
                    return implode("\n", $groupNames);
                },
            ],
            [
                'label' => 'Primary Email',
                'attribute' => 'pemail',
            ],
            // 'semail:email',
            // 'country',
            // 'websiteurl:url',
            // 'gender',
            // 'birthday',
            // 'status',
            // 'sentstatus',
            // 'addeddate',
            // 'updateddate',
            ['class' => 'yii\grid\ActionColumn'],
        ],
    ]);
    ?>

In this way you can show the related groupname in the contacts model with sorting and searching facility.

Thanks

Total 2 comments

#20051 report it
Ocean Wind at 2017/03/18 02:21pm
Show entries without related record

The inner join in this solution narrows the results to only those that have a related record.

How do you do this in a way that does not filter the results in this way? I want to find all the entries that don't have a related record, but they get filtered out.

#20015 report it
bhatt akshay at 2017/01/17 06:21am
Awesome tutorial

After too much googling found this solution. Thanks so much.

Leave a comment

Please to leave your comment.

Write new article