JOIN multiple table using relations in Yii2

Question

I am trying to list some data through Kartik GridView widget in yii2 by using relations. I have these tables

staffs

CREATE TABLE IF NOT EXISTS `staffs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8 DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `designation_id` int(11) DEFAULT NULL,
  `username` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `emailid` varchar(250) CHARACTER SET utf8 DEFAULT NULL,
  `staffrights` tinyint(2) DEFAULT '0',
  `staffstatus` tinyint(2) DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
designations

CREATE TABLE IF NOT EXISTS `designations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `designname` varchar(150) NOT NULL,
  `designation_group_id` varchar(250) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;



designation_group 


CREATE TABLE IF NOT EXISTS `designation_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_name` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

designations table is related to designation_group by designations.designation_group_id . designations table will have one or more values seperated by comma, of designation_group.id .

designations table is related to staffs table by staffs.designation_id =designations.id. In Staffs Model I have added relations like this

public function getDesignations() {
        return $this->hasOne( Designations::className(), ['id' => 'designation_id']);
    }

and is working perfect. But the relation for designation_group I tried like this:

public function getDesgroupstaffs(){
        return $this->hasOne(Designations::className() , ['id' => 'id'])
                    ->from(Designationgroup::tableName() ) ;
}

But it doesnt give the expected result. How the designation_group table can be joined so that all the designation group associated with the staff can also be displayed ? I want to show like, the first column of grid view will be designations, while filter of the same column should be DesignationGroup.group_name. SO if any group_name is selected , it will show data of staffs associated with that group name

Answer 1

Well, from what I was able to extract from your question, you want to be able to get the group name for each of the staffs. The code below will help you accomplish that task.

Inside the staff model, create a relationship as stated below or you can use an existing one which I am sure Yii would have automatically generated it for you

STAFF model

public function getDesignation()
{
   return $this->hasOne(Designation::className(),['designation_id'=>'id]);
}

Inside the designation model,create another relation that links the degination model with the designationGroup model, which would have been automatically created as well

Designation MOdel

public function getDesignationGroup()
{
   return $this->hasOne(DesignationGroup::className(),['id'=>'designation_group_id]);
}

Finally, on your gridview, you can use the code below to get the group name

$model->destination->designationGroup->group_name

I hope this solves your problem. Though i have used it couple of times.