Brand new to Yii, not new to PHP though. In the tutorial that Larry Ullman provides for Yii, I set up an Employees MySQL table and a Department table. The Employee table has a column, departmentId, which relationally links to Department to fetch the Id and get the name for that Department.
But here is my issue. Employees at my company can float in multiple departments (such as Customer Service, and Technical Support). My current Yii setup appears to only be set to fetch a single department. How could I make it so that I could enter multiple department IDs for an employee (separated by commas or anything such as |) and when Yii pulls down that employee, it will break those out and list all the departments for that employee?
I know this is bordering on the line of being too general, so I have posted some of my code below which contains the relations for Employee in the model:
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'department' => array(self::Belongs_To, 'Department', 'departmentId'),
);
}
It sounds like you need an n:m relationship: So one employee can be assigned to many departments and a department has many employees. What you need is a so called pivot table to connect an employee to one or more departments like this:
tbl_employee (id, name, age, etc.) tbl_employee_department (employee_id, department_id) tbl_department (id, name, etc.)
Now your employee relation would look like this:
public function relations()
{
return array(
'departments' => array(self::MANY_MANY, 'Department', 'tbl_employee_department(employee_id, department_id)'),
);
}
And your department relation would look like this:
public function relations()
{
return array(
'employees' => array(self::MANY_MANY, 'Employee', 'tbl_employee_department(employee_id, department_id)'),
);
}
Now $employee->departments would return an array of department models. But what you want for forms is an arrayof department ids, so you have to do this in your employee model
public $departmentIds=array();
public function afterFind()
{
if(!empty($this->departments)) {
foreach($this->departments as $id=>$department)
$this->departmentIds[]=$department->id;
}
parent::afterFind();
}
public function afterSave()
{
$tx = Yii::app()->db->beginTransaction();
try {
//delete all connections from this employee to any department first
$command = Yii::app()->db->createCommand();
$command->delete('tbl_employee_department', 'employee_id=:id', array(':id'=>$this->id));
//now reconnect the employee to the departments in the departmentIds array
foreach($this->departmentsIds as $id) {
$command = Yii::app()->db->createCommand();
$command->insert('tbl_employee_department', array(
'employee_id' => $this->id,
'department_id'=> (int)$id
));
}
$tx->commit();
} catch (Exception $e) {
$tx->rollback();
throw new CException('Something bad happend. Cancelled transaction and rolled back data! '.$e->getMessage());
}
parent::afterSave();
}
This code fetches the departments from the database and stores their ids in the $departmentIds array as soon as you fetch an employee model from the database (via Employee::model()->find() etc.). The afterSave method gets called when you call $employee->save() and the saving process was successful. It will then start a transaction deleting all connections between this employee and all departments he is assigned to and then add all departments that are stored in the departmentIds array.
If you want to set this field via $model->setAttributes() you will have to declare it as safe (so Yii allows it to be mass assigned) by putting this in your employee rules
public function rules()
{
return array(
//...all the other rules...
array('departmentIds', 'safe'),
);
}
I know, looks pretty complicated but it should give you an idea I guess. The only problem left is to set the ids of the departmentIds array via a form but you could explode a string of a texfield to do that etc. This shouldn't be that hard I guess