I started using Yii2 not quite long. I am developing an Electrical Monitoring Application, and I want to link the four tables below to create a gridview with search and filter.
CREATE TABLE building (
building_id int(23) NOT NULL AUTO_INCREMENT PRIMARY KEY, //PK
building_name varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE inspector (
inspector_id int(23) NOT NULL AUTO_INCREMENT PRIMARY KEY, //PK
inspector_name varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE inspection_booking (
bookinspection_id int(12) NOT NULL AUTO_INCREMENT PRIMARY KEY,
inspector_id int NOT NULL, //FK
building_id int NOT NULL, //FK
‘inspection_date’ date NOT NULL,
‘fee_id’ int DEFAULT 0,
‘STATUS’ varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE inspection_plan (
inspection_id int(12) NOT NULL AUTO_INCREMENT PRIMARY KEY,
building_id int NOT NULL, //FK
‘last_date’ date NOT NULL,
‘next_date’ date NOT NULL,
‘payment_id’ int DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
The main table is inspection_booking
This is my mysql query:
SELECT ib.bookinspection_id, ib.inspection_date, ib.status, i.inspector_id, i.inspector_name as inspector, b.building_id, b.building_name as building, ip.inspection_id, ip.payment_id
from inspection_booking ib
INNER JOIN inspector i ON (ib.inspector_id = i.inspector_id AND ib.inspector_id <> 0)
INNER JOIN building b ON (ib.building_id = b.building_id)
INNER JOIN inspection_plan ip ON (b.building_id = ip.building_id AND ip.payment_id <> 0)
ORDER BY ib.inspection_date DESC;
CONTROLLER
public function actionInspectionpreview()
{
$searchModel = new InspectionBookingSearch();
$dataProvider = $searchModel->searchbooking(Yii::$app->request->queryParams);
return $this->render('inspectionpreview', [
'searchModel' => $searchModel,
'dataProvider' => $dataProvider,
]);
SEARCH MODEL
public function searchbooking($params)
{
$query = Inspectionbooking::find()->where(['<>', 'fee_id', 0])->andwhere(['<>', 'inspector_id', 0]); //->where(['fee_id' <> 0 AND 'inspector_id' <> 0])
$query->joinWith(['building','inspector']);
$dataProvider = new ActiveDataProvider([
'query' => $query, 'sort' => ['enableMultiSort' => true],
'sort'=> ['defaultOrder' => ['inspection_date'=>SORT_DESC]]
]);
// die(var_dump($dataProvider));
$dataProvider->sort->attributes['inspector_id'] = [
'asc' => ['inspector.inspection_name' => SORT_ASC],
'desc' => ['inspector.inspection_name' => SORT_DESC],
];
$dataProvider->sort->attributes['building_id'] = [
'asc' => ['building.building_name' => SORT_ASC],
'desc' => ['building.building_name' => SORT_DESC],
];
$dataProvider->sort->attributes['inspection_date'] = [
'asc' => ['inspection_date' => SORT_ASC],
'desc' => ['inspection_date' => SORT_DESC],
];
$dataProvider->sort->attributes['status'] = [
'asc' => ['status' => SORT_ASC],
'desc' => ['status' => SORT_DESC],
];
$this->load($params);
if (!$this->validate()) {
// uncomment the following line if you do not want to return any records when validation fails
// $query->where('0=1');
return $dataProvider;
}
$query->andFilterWhere([
'building_id' => $this->building_id,
'inspector_id' => $this->inspector_id,
'inspection_date' => $this->dbDateSearch($this->inspection_date),
'status' => $this->status,
]);
$query->andFilterWhere(['like', 'building_id', $this->building_id])
->andFilterWhere(['like', 'inspector_id', $this->inspector_id])
->andFilterWhere(['like', 'status', $this->status]);
return $dataProvider;
}
VIEW
<?php $visible = Yii::$app->user->can("/inspection/inspection/view") ? true : false; ?>
<?php Pjax::begin([ 'enablePushState' => false ]); ?>
<?= GridView::widget([
'dataProvider' => $dataProvider,
'filterModel' => $searchModel,
'summary'=>'',
'columns' => [
['class' => 'yii\grid\SerialColumn'],
[
'label' => Yii::t('inspection', 'Contractor'),
'attribute' => 'inspector_name',
'value' => 'inspector.inspector_name',
],
[
'label' => Yii::t('inspection', 'Building'),
'attribute' => 'building_name',
'value' => 'building.building_name',
],
[
'attribute' => 'inspection_date',
'value' => function ($model) {
return Yii::$app->formatter->asDate($model->inspection_date);
},
'filter' => \yii\jui\DatePicker::widget([
'model'=>$searchModel,
'attribute'=>'inspection_date',
'clientOptions' =>[
'dateFormat' => 'dd-mm-yyyy',
'changeMonth'=> true,
'changeYear'=> true,
'defaultValue'=>null,
'yearRange'=>'1900:'.(date('Y')+1),
'defaultDate'=> null,],
'options'=>[
'class' => 'form-control',
'value' => NULL,
],
]),
'format' => 'html',
],
[
'class' => 'app\components\CustomActionColumn',
'template' => '{view} {update} {delete}',
'buttons' => [
'view' => function ($url, $model) {
return ((Yii::$app->user->can("/inspection/inspection/view")) ? Html::a('<span class="glyphicon glyphicon-search"></span>', $url, ['title' => Yii::t('inspection', 'View'),]) : '');
},
'update' => function ($url, $model) {
return ((Yii::$app->user->can("/inspection/inspection/update")) ? Html::a('<span class="glyphicon glyphicon-edit"></span>', $url, ['title' => Yii::t('inspection', 'Update'),]) : '');
},
'delete' => function ($url, $model) {
return ((Yii::$app->user->can("/inspection/inspection/delete")) ? Html::a('<span class="glyphicon glyphicon-remove"></span>', $url, ['title' => Yii::t('inspection', 'Delete'), 'data' => ['confirm' => Yii::t('inspection', 'Are you sure you want to delete this item?'),'method' => 'post'],]) : '');
}
],
'visible' => $visible,
],
],
]);
Pjax::end(); ?>
The problem am having is that am unable to link the 4th table inspection_plan in my yii2 model search query. This is because its not having foreign in in the main table (inspection_booking).
Note that it has forign key in building table. Plase help me. My search Model in particular
Secondly, how do i create custom CustomActionColumn button apart from the regular view/update/delete