I have three tables
--
-- Table structure for table `tbl_ticket`
--
CREATE TABLE IF NOT EXISTS `tbl_ticket` (
`id` int(9) NOT NULL,
`complain_id` varchar(250) CHARACTER SET latin1 NOT NULL,
`section_id` varchar(250) CHARACTER SET latin1 NOT NULL,
`location_id` varchar(250) CHARACTER SET latin1 NOT NULL,
`status` varchar(250) CHARACTER SET latin1 NOT NULL,
`remarks` varchar(250) CHARACTER SET latin1 NOT NULL,
`r_date` datetime NOT NULL,
`d_date` datetime NOT NULL,
`hd_user_username` varchar(250) CHARACTER SET latin1 NOT NULL,
`hd_user_email` varchar(250) CHARACTER SET latin1 NOT NULL,
`description` varchar(250) NOT NULL,
`attachment` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tbl_ticket`
--
ALTER TABLE `tbl_ticket`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tbl_ticket`
--
ALTER TABLE `tbl_ticket`
MODIFY `id` int(9) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;
--
-- Table structure for table `tbl_ticket_complain`
--
CREATE TABLE IF NOT EXISTS `tbl_ticket_complain` (
`id` int(9) NOT NULL,
`ticket_id` varchar(250) NOT NULL,
`complain_id` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tbl_ticket_complain`
--
ALTER TABLE `tbl_ticket_complain`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tbl_ticket_complain`
--
ALTER TABLE `tbl_ticket_complain`
MODIFY `id` int(9) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;
and
--
-- Table structure for table `tbl_complain_type`
--
CREATE TABLE IF NOT EXISTS `tbl_complain_type` (
`id` int(9) NOT NULL,
`section_id` varchar(250) NOT NULL,
`complains` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=20 ;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tbl_complain_type`
--
ALTER TABLE `tbl_complain_type`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tbl_complain_type`
--
ALTER TABLE `tbl_complain_type`
MODIFY `id` int(9) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=20;
I want to display the contents of tbl_complain_type , i.e, id and complains to be shown as check box in the view.
tbl_ticket will be having multiple tbl_complain_type.id as values and this is related through the table tbl_ticket_complain .ticket_id =tbl_ticket.id . How can this be done in Yii2 views for CRUD ?
In controller I have made actionCreate like this
$model = new Ticket();
$ticket_complain=new TicketComplain();
$complain_type=ComplainType ::find ()->all();
if ($model->load(Yii::$app->request->post()) && $model->save()) {
return $this->redirect(['view', 'id' => $model->id]);
} else {
return $this->render('create', [
'model' => $model,
'complain_type'=>$complain_type,
'ticket_complain'=>$ticket_complain
]);
}
and in View to display them as check box I have added code like this
<?php
$model->complains = $complain_type;
$list = $complain_type;
$options = \yii\helpers\ArrayHelper::map($list, 'id', 'complains');
echo $form->field($model, 'complains')->checkboxList($options);
?>
The problem I am facing is retaining of the checkbox values and saving / updating the values . Also I am not sure whether to use checkBox instead of checkboxList , i.e,
$complains = [];
if($complain_type ) {
foreach($complain_type as $complain) {
$complains[] = $complain->complains;
$id[]= $complain->id;
echo $form->field($ticket_complain, 'id')
->checkBox(['label' => $complain->complains, 'uncheck' => null, 'selected' => true]);
}
}
Or let me simplify
How can I set model values as checkbox selected from from database and based on submitted values when submitted?
The relations between the three tables can be demostrated by this sql
SELECT `tbl_ticket`.* , tbl_complain_type.complains FROM tbl_ticket JOIN
tbl_ticket_complain ON tbl_ticket.id =tbl_ticket_complain.ticket_id JOIN
tbl_complain_type ON tbl_complain_type.id=tbl_ticket_complain.complain_id
In model \app\models\Ticket.php add the following variable,
class Ticket extends \yii\db\ActiveRecord
{
public $complains_field; //A custom variable to hold the value of checkboxlist
/*Remaining contents of Ticket model */
public function rules()
{
return [
[['complains_field'], 'safe']
];
}
public function getComplains() //Relation between ticket & ticket_complain table
{
return $this->hasMany(TicketComplain::className(), ['ticket_id' => 'id']);
}
public function afterSave($insert, $changedAttributes){
\Yii::$app->db->createCommand()->delete('tbl_ticket_complain', 'ticket_id = '.(int) $this->id)->execute(); //Delete existing value
foreach ($this->complains_field as $id) { //Write new values
$tc = new TicketComplain();
$tc->ticket_id = $this->id;
$tc->complain_id = $id;
$tc->save();
}
}
}
In actionCreate() on the controller
$model = new Ticket();
$complain_type= ComplainType::find()->all();
if ($model->load(Yii::$app->request->post()) && $model->save()) {
return $this->redirect(['view', 'id' => $model->id]);
}
else {
return $this->render('create', [
'model' => $model,
'complain_type'=>$complain_type
]);
}
In actionUpdate() on the controller
$model = $this->findModel($id);
$complain_type = ComplainType::find()->all();
//Retrieve the stored checkboxes
$model->complains_field = \yii\helpers\ArrayHelper::getColumn(
$model->getComplains()->asArray()->all(),
'complain_id'
);
if ($model->load(Yii::$app->request->post()) && $model->save()) {
return $this->redirect(['view', 'id' => $model->id]);
}
else {
return $this->render('create', [
'model' => $model,
'complain_type'=>$complain_type
]);
}
In View
<?php
$options = \yii\helpers\ArrayHelper::map($complain_type, 'id', 'complains');
echo $form->field($model, 'complains_field')->checkboxList($options, ['unselect'=>NULL]);
?>