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

use yii2 joinWidth like... Model::find()->joinWith(['Relation Name'])->all(); refer this link Join With Relation — vishuB
The relation does not Desgroupstaffs give the designation group — user7282
this one relation is wrong ['id' => 'id'] in getDesgroupstaffs() function — vishuB
Yeah, and i believe that's where your problem is. I think you need a custom SQL (have a look at Mysqls find_in_set() and like) or if its still possible add a junction table. — Jørgen
AFAIK, you cannot join with comma seperated ids. Wouldn't it be an option to have a column designation_id in designation_group instead of the designation_group_id in designations? — robsch

Oyedele Femi Oyedele Femi · Accepted Answer · 2016-02-26T11:45:33

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.

JOIN multiple table using relations in Yii2

4 Answers