I am looking for four tables and joined them and got the desired result. But inability to sort or filter the output. Please tell me how I can search for it by region or range of sales or collection. Side Search Model -
<?php namespace frontend\modules\districtreport\models; use Yii; use yii\base\Model; use yii\data\ActiveDataProvider; use frontend\modules\districtreport\models\Parties; use frontend\modules\districtreport\models\Bills; use frontend\modules\districtreport\models\Payment; use yii\db\Query; use yii\db\Command; $query = \Yii::$app->db; /** * PartiesSearch represents the model behind the search form about `frontend\modules\districtreport\models\Parties`. */ class PartiesSearch extends Parties { /** * @inheritdoc */ public function rules() { return [ [['party_id'], 'integer'], [['parties_partyname', 'address', 'parties_district', 'name_manager', 'transport', 'dlno', 'instruction', 'con', 'district','sale','sell','collection'], 'safe'], ]; } /** * @inheritdoc */ public function scenarios() { // bypass scenarios() implementation in the parent class return Model::scenarios(); } /** * Creates data provider instance with search query applied * * @param array $params * * @return ActiveDataProvider */ public function search($params) { $sql = 'select tsell.district as district, tsell.totalsale as sell, coalesce(tcollection.collection,0) as collection from (SELECT district, coalesce(sell.sale,0) as totalsale FROM `districts` left join (SELECT parties_district, billdate, sum(billamount) as sale FROM `bills` left join parties on bills.bills_partyname = parties.parties_partyname group by parties_district) as sell on sell.parties_district = districts.district) as tsell left join (SELECT parties_district, payment_date, COALESCE(sum(payment_amount),0) as collection FROM `payment` left join parties on payment.payment_partyname = parties.parties_partyname group by parties_district) as tcollection on tsell.district = tcollection.parties_district'; $query = Parties::findBySql($sql); // add conditions that should always apply here $dataProvider = new ActiveDataProvider([ 'query' => $query, //'sort'=> ['defaultOrder' => ['district'=>SORT_DESC]] ]); $dataProvider->setSort([ 'attributes' => [ 'sell' => [ 'asc' => ['sell' => SORT_ASC], 'desc' => ['sell' => SORT_DESC], 'label' => 'Sell' ], 'collection' => [ 'asc' => ['collection' => SORT_ASC], 'desc' => ['collection' => SORT_DESC], 'label' => 'Collection' ], 'district' => [ 'asc' => ['tsell.district' => SORT_ASC], 'desc' => ['tsell.district' => SORT_DESC], 'label' => 'District' ] ] ]); $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; } // grid filtering conditions $query->andFilterWhere([ 'party_id' => $this->party_id, ]); $query->andFilterWhere(['like', 'parties_partyname', $this->parties_partyname]) ->andFilterWhere(['like', 'address', $this->address]) ->andFilterWhere(['like', 'parties_district', $this->parties_district]) ->andFilterWhere(['like', 'name_manager', $this->name_manager]) ->andFilterWhere(['like', 'transport', $this->transport]) ->andFilterWhere(['like', 'dlno', $this->dlno]) ->andFilterWhere(['like', 'instruction', $this->instruction]) ->andFilterWhere(['like', 'con', $this->con]) ->andFilterWhere(['like', 'sell', $this->sell]) ->andFilterWhere(['like', 'collection', $this->collection]) ->andFilterWhere(['like', 'district', $this->district]); return $dataProvider; } }
Side Model
<?php namespace frontend\modules\districtreport\models; use Yii; /** * This is the model class for table "parties". * * @property integer $party_id * @property string $parties_partyname * @property string $address * @property string $parties_district * @property string $name_manager * @property string $transport * @property string $dlno * @property string $instruction * @property string $con */ class Parties extends \yii\db\ActiveRecord { public $sale; public $district; public $sell; public $collection; public $bills; public $partyname; public $billdate; //public $sale; /** * @inheritdoc */ public static function tableName() { return 'parties'; } /** * @inheritdoc */ public function rules() { return [ [['parties_partyname', 'parties_district', 'name_manager'], 'required'], [['parties_partyname'], 'string', 'max' => 60], [['address', 'instruction'], 'string', 'max' => 100], [['parties_district'], 'string', 'max' => 20], [['name_manager', 'transport', 'dlno'], 'string', 'max' => 30], [['con'], 'string', 'max' => 10], [['parties_partyname'], 'unique'], [['name_manager'], 'exist', 'skipOnError' => true, 'targetClass' => Managers::className(), 'targetAttribute' => ['name_manager' => 'manager_managername']], [['con'], 'exist', 'skipOnError' => true, 'targetClass' => Console::className(), 'targetAttribute' => ['con' => 'console']], [['parties_district'], 'exist', 'skipOnError' => true, 'targetClass' => Districts::className(), 'targetAttribute' => ['parties_district' => 'district']], ]; } /** * @inheritdoc */ public function attributeLabels() { return [ 'party_id' => 'Party ID', 'parties_partyname' => 'Parties Partyname', 'address' => 'Address', 'parties_district' => 'Parties District', 'name_manager' => 'Name Manager', 'transport' => 'Transport', 'dlno' => 'Dlno', 'instruction' => 'Instruction', 'con' => 'Con', ]; } public function getDistricts() { return $this->hasOne(Districts::className(), ['district' => 'parties_district']); } public function getBills() { return $this->hasMany(Bills::className(), ['bills_partyname' => 'parties_partyname']); } public function getPayment() { return $this->hasMany(Payment::className(), ['payment_partyname' => 'parties_partyname']); } }
index.php
<?php use yii\helpers\Html; use kartik\grid\GridView; //use kartik\widgets\DatePicker; use kartik\daterange\DateRangePicker; use kartik\form\ActiveForm; use dosamigos\datepicker\DatePicker; use frontend\modules\districtreport\models\ExpartiesSearch; /* @var $this yii\web\View */ /* @var $searchModel frontend\modules\districtreport\models\PartiesSearch */ /* @var $dataProvider yii\data\ActiveDataProvider */ $this->title = 'Parties'; $this->params['breadcrumbs'][] = $this->title; ?> <div class="parties-index"> <h1><?= Html::encode($this->title) ?></h1> <?php // echo $this->render('_search', ['model' => $searchModel]); ?> <!-- <p> <?= Html::a('Create Parties', ['create'], ['class' => 'btn btn-success']) ?> </p> --> <!-- <div class="custom-filter"> Date range: <input name="start" /> <input name="end" /> </div> --> <?= GridView::widget([ 'dataProvider' => $dataProvider, 'filterModel' => $searchModel, 'export' => false, 'columns' => [ [ //['class' => 'yii\grid\SerialColumn'], 'class' => 'kartik\grid\ExpandRowColumn', 'value' => function($model, $key, $index, $column){ return GridView::ROW_COLLAPSED; }, 'detail' => function($model, $key, $index, $column){ $searchModel = new ExpartiesSearch(); $searchModel-> district = $model->district; $dataProvider = $searchModel->search(Yii::$app->request->queryParams); return Yii::$app->controller->renderPartial('_exparties', [ 'searchModel' => $searchModel, 'dataProvider' => $dataProvider, ]); }, ], //['class' => 'yii\grid\SerialColumn'], 'district', // [ // 'attribute' => 'date', // 'value' => 'tsell.date', // 'filter' => \yii\jui\DatePicker::widget(['language' => 'ru', 'dateFormat' => 'dd-MM-yyyy']), // 'format' => 'html', // ], 'sell', 'collection', //['class' => 'yii\grid\ActionColumn'], ], ]); ?> </div>
In this photo we see that although sorting is for sale, it does not actually sort the data. 
In Figure 2, we see that although assam data is passed to the next level of the kartik extension, it is not filtered.