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](http://www.yiiframework.com/doc-2.0/guide-db-active-record.html) — vishuB, Feb 25 '16 at 06:51
The relation does not Desgroupstaffs give the designation group — user7282, Feb 25 '16 at 06:55
this one relation is wrong `['id' => 'id']` in `getDesgroupstaffs()` function — vishuB, Feb 25 '16 at 06:58
designation_group has relation with designations. designations will have multple values of designation_group — user7282, Feb 25 '16 at 06:59
It shows an error Database Exception – yii\db\Exception SQLSTATE[42S22]: Column not found: 1054 Unknown column 'staffs.designation_group_id' in 'on clause' The SQL being executed was: SELECT COUNT(*) FROM `staffs` LEFT JOIN `designations` ON `staffs`.`designation_id` = `designations`.`id` LEFT JOIN `departments` ON `staffs`.`department_id` = `departments`.`id` LEFT JOIN `designation_group` ON `staffs`.`designation_group_id` = `designation_group`.`id` — user7282, Feb 25 '16 at 07:22
So this is a many-to-many relationship? Where a Designation group can have many designations, and designations can have many designation groups? Is there a reason for the csv, rather than a junction table? — Jørgen, Feb 25 '16 at 07:23
designations table may have one or many designation_group_id within a single row which will be sepeated by ',' like 1,2,3 — user7282, Feb 25 '16 at 07:28
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, Feb 25 '16 at 07:34
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, Feb 26 '16 at 08:37

score 2 · Answer 1 · edited Feb 26 '16 at 11:52

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.

The filter in grid view does not show correct data. designation_group_id are a set of comma separated values. the first column of grid view will be designations, while filter of the same column should be DesignationGroup.group_name — user7282, Feb 26 '16 at 20:08

score 0 · Answer 2 · edited May 23 '17 at 12:15

If I understand you, it's looks like my problem a few days ago. check this out

I have three tables (contact, contact_groups and contact_contact_groups) which has many-to-many relation. It means one contact has many groups, and one group has many contacts. So I create one table to make relations One-to-Many.

but the result will like this :

contact | contact groups
-------------------------
contact #1 | business1, business2, family
contact #2 | family
contact #3 | student, partner

score 0 · Answer 3 · answered Mar 23 '18 at 11:32

staff model

use ->with(['designationgroup'])

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

designation model

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

On GridView , render the value as

$model->destination->designationgroup->group_name

Hope it helps.

score -1 · Answer 4 · edited Feb 26 '16 at 00:39

I will suggest you to remove the column designation_group_id from designation table, because it contains multiples comma separated values. Create one more table named designation_group_assigned, which will have the following columns: Id, designation_id, designation_group_id

In this table, we can insert multiple rows if one designation belongs to multiple groups.

And then you can use the yii relationships for one to many records.

JOIN multiple table using relations in Yii2

4 Answers4